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? Quote Link to comment Share on other sites More sharing options...
gizmola Posted May 27, 2011 Share Posted May 27, 2011 I see no problem with your SQL. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 INT Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
lalnfl Posted May 27, 2011 Author Share Posted May 27, 2011 Yes it does. Quote Link to comment 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. Quote Link to comment 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 (... Quote Link to comment 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? Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.