lalnfl Posted May 27, 2011 Share Posted May 27, 2011 So say I have a union sql statement like this: (SELECT id, card_id, ordered FROM Match_1 WHERE card_id='$card_id') UNION (SELECT id, card_id, ordered FROM Match_2 WHERE card_id='$card_id') UNION (SELECT id, card_id, ordered FROM Match_3 WHERE card_id='$card_id') ORDER BY ordered ASC ordered is an INT, but yet when I order the results its like this 1 11 2 11 should obviously be last but it isn't, what am I doing wrong? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/ Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 I see no problem with your SQL. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221388 Share on other sites More sharing options...
requinix Posted May 27, 2011 Share Posted May 27, 2011 What's wrong is you're storing numbers as strings. "11" sorts before "2" because 1 But the real question is why you have three Match_# tables. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221390 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 What's wrong is you're storing numbers as strings. "11" sorts before "2" because 1 Haha, yes that would explain it. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221391 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 What's wrong is you're storing numbers as strings. "11" sorts before "2" because 1<2. But the real question is why you have three Match_# tables. So I would have to make the php string a number then? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221395 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 Has nothing to do with PHP, hes saying your orderedby columns in the database need to be tinyints. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221397 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 Has nothing to do with PHP, hes saying your orderedby columns in the database need to be tinyints. How does that work then? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221402 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 What is the data type of the ordered columns in your tables? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221405 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 INT Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221408 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 When you run the query in phpMyAdmin SQL window or in the mysql command line client is the result ordered properly? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221410 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 Yes it does. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221417 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 So I go back to my original response. The implication is that there is something you are doing wrong in the code that displays the results. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221419 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 So I go back to my original response. The implication is that there is something you are doing wrong in the code that displays the results. Could it be because I am using parenthesis around each SELECT part (SELECT id, card_id, ordered FROM Match_1 WHERE card_id='$card_id') UNION (... Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221423 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 No, there is nothing wrong with the query, which you verified by running it as SQL. What is your php fetch/display code? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221429 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 After posting a re-looking at the code and then redoing it in the phpmyadmin sql query thing, it doesn't display correctly. And the reason is if I declare more than 2 UNION statements, it messes up the results. Do you know why it does that? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221430 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 Probably one of the ordered columns is not actually an int. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221432 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 Probably one of the ordered columns is not actually an int. Son of a bitch. 2 hours and that is the problem. Thanks for your help gizmola. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221434 Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 You could have saved a lot of time if you had actually checked what we suggested you check, rather than taking the short cut and looking at one table of the 3. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221435 Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 You could have saved a lot of time if you had actually checked what we suggested you check, rather than taking the short cut and looking at one table of the 3. What do you mean? Everyone was set at INT, except for one of them, that I accidentally skipped. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221439 Share on other sites More sharing options...
requinix Posted May 28, 2011 Share Posted May 28, 2011 Which brings us back to the fact that you have three identical tables. What's up with that? Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221455 Share on other sites More sharing options...
gizmola Posted May 28, 2011 Share Posted May 28, 2011 You could have saved a lot of time if you had actually checked what we suggested you check, rather than taking the short cut and looking at one table of the 3. What do you mean? Everyone was set at INT, except for one of them, that I accidentally skipped. Exactly right. requinix pointed out that the data types were a concern. We have mo way of knowing what the data types were, but we assumed that you checked all of them, not one of three. In the future don't cut corners. That is the lesson that hopefully you will learn. Consistency is an important aspect of successful development practices. It doesn't matter to requinix or myself. Neither of us would have made the mistake you made. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221477 Share on other sites More sharing options...
lalnfl Posted May 28, 2011 Author Share Posted May 28, 2011 Which brings us back to the fact that you have three identical tables. What's up with that? Each one have different fields. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221483 Share on other sites More sharing options...
lalnfl Posted May 28, 2011 Author Share Posted May 28, 2011 You could have saved a lot of time if you had actually checked what we suggested you check, rather than taking the short cut and looking at one table of the 3. What do you mean? Everyone was set at INT, except for one of them, that I accidentally skipped. Exactly right. requinix pointed out that the data types were a concern. We have know way of knowing what the data types were, but we assumed that you checked all of them, not one of three. In the future don't cut corners. That is the lesson that hopefully you will learn. Consistency is an important aspect of successful development practices. It doesn't matter to requinix or myself. Neither of us would have made the mistake you made. Well excuse me, didn't know you had to be perfect. lol Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221484 Share on other sites More sharing options...
gizmola Posted May 28, 2011 Share Posted May 28, 2011 Well excuse me, didn't know you had to be perfect. lol Stop being defensive. It's not a matter of being perfect, you were careless. Link to comment https://forums.phpfreaks.com/topic/237677-union-and-order-by/#findComment-1221508 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.