I am using this SQL query to order a list of records by date in a php page.
SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM table WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s');
This works fine.
In a different php page, I want to be able to delete this record, and show the next one in the list. The query I am using to do that is:
SELECT ARTICLE_NO FROM auctions1 WHERE str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) > (SELECT str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) FROM table WHERE ARTICLE_NO =".$pk.") ORDER BY str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) LIMIT 1";
The problem I seem to be having, is because there are many records with the same date, any record from the group of records with the same date will be chosen, not the same one in the list.
How can I select the next record returned from the same result set as the first query? The first query always returns the same order, so I am not sure why the second query seems to have a different order.
edit:
I have been trying to use Quassnoi advice. The first query I am now using is:
SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME,
date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate
FROM AUCTIONS1
WHERE upper(ARTICLE_NAME) LIKE % x %
ORDER BY
str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no limit 0, 10
And the second query, as suggested by Quassnoi is:
SELECT ARTICLE_NO
FROM auctions1
WHERE (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) >
(
SELECT str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
FROM auctions1
WHERE ARTICLE_NO = xxx
)
ORDER BY
str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
LIMIT 1
I copied this query by echoing it out through my php page, and simply placed xxx in place of the article_no that was present. This matches the first code example perfectly, however the results are the same as the code I was using in my original question.
edit2:
This is the query used to obtain the original result set:
SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM auctions1 WHERE upper(ARTICLE_NAME) LIKE '%o%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no limit 0, 10;
Which results in this data, which is fine:
ARTICLE_NO USERNAME ACCESSSTARTS ARTICLE_NAME shortDate
160288212077 5864_australen 30/09/2008 05:22:30 DON ED HARDY TIGER JACKE WEISS XL 30 09 2008
220288566257 fashionticker1 01/10/2008 16:39:12 Ed Hardy Tank Top Lila Neu & OVP Gr. L 01 10 2008
280273115680 mulle15 01/10/2008 16:42:38 Ed Hardy, T-Shirt,Destroy, schwarz, Gr.L 01 10 2008
280273115991 mulle15 01/10/2008 16:43:54 Ed Hardy, T-Shirt,Destroy, schwarz, Gr.XL 01 10 2008
280273116224 mulle15 01/10/2008 16:44:59 Ed Hardy, T-Shirt,Destroy, schwarz, Gr.XXL 01 10 2008
280273118653 mulle15 01/10/2008 16:54:50 Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.M 01 10 2008
120312402767 lieschenjuli 01/10/2008 16:56:12 Badehose Shorts Ed Hardy L 01 10 2008
280273119206 mulle15 01/10/2008 16:56:47 Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.XL 01 10 2008
280273119489 mulle15 01/10/2008 16:57:49 Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.XXL 01 10 2008
160288777155 bonifatzius1 01/10/2008 16:58:33 Ed Hardy Bomberjacke Gr. L Jacke für Damen oder H... 01 10 2008
The problem is, if I perform this query:
SELECT ARTICLE_NO
FROM auctions1
WHERE (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) >
(
SELECT str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
FROM auctions1
WHERE ARTICLE_NO =160288212077
)
ORDER BY
str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
LIMIT 1;
The this returns 280273112610, when 220288566257 is what should be returned
-
You could try also ordering your results by ARTICLE_NO. Just do:
... ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), ARTICLE_NO ...
This way, both results sets should have exactly the same order. In both cases, the next record will be the record with the lowest ARTICLE_NO with a posterior date.
Greetings
Sacher
Joshxtothe4 : the ARTICLE_NO fields are completely arbitrary, but I will try this. -
This is perfectly logic. You have a condition on ARTICLE_NAME in the first query that is missing from the second query - the second query returns MORE rows than the first one. Remove the LIMIT and you'll see. The second ordering condition is also a good idea, even if the number is arbitrary.
-
SELECT ARTICLE_NO FROM auctions1 WHERE upper(ARTICLE_NAME) LIKE '% x %' AND (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) > ( SELECT str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no FROM auctions1 WHERE ARTICLE_NO = @pk ) ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no LIMIT 1
Note that your original resultset:
SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM auctions1 WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s');
does not guarantee stable row order within one
ACCESSSTARTS
. You need to addPRIMARY KEY
to theORDER BY
clause, like this:SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM auctions1 WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no.
(I'm assuming
ARTICLE_NO
is thePRIMARY KEY
of your table)Your original rowset, depending on what access method it's using, returns rows either in table order in in index order.
You really, really, really need to change your original rowset to use the stable order.
But if you cannot do it for some reason, you can do the following:
SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, FROM ( SELECT @c := NULL ) vars, auctions1 WHERE upper(ARTICLE_NAME) LIKE % x % AND CASE WHEN ARTICLE_NO = $PK THEN @с := 0 ELSE 0 END IS NOT NULL AND (@c := @c + 1) = 2 ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'); LIMIT 1
This query is less efficient and heavily relies on fact that this query will use exactly same access method as your original query.
You don't normally rely on this fact, as access method can change anytime.
If you want clear and sane code, just add the
ARTICLE_NO
intoORDER BY
and enjoy the query I posted first.Joshxtothe4 : This results in the same behavior as in my post. The next record from my list of records is not selcted, rather a record with the same date in a different order is selected.Joshxtothe4 : HI Quassnoi, thanks for the clarification, however it still fails. I have posted the updated queries I am using into my question.Joshxtothe4 : edited with new infoQuassnoi : Updated the first query
0 comments:
Post a Comment