Skylight_lady Posted January 16, 2011 Share Posted January 16, 2011 Hi, i have a mysql table which is a varchar string. I have tried to get the following sql statement to work on my localhost without any luck. SELECT * FROM users WHERE ID = '$ID' AND subscription <> 0 AND subscription <> 'admin' ORDER BY CONVERT(INT, subscription) DESC If i use the following statement: SELECT * FROM users WHERE ID = '$ID' AND subscription <> 0 AND subscription <> 'admin' ORDER BY subscription DESC It works fine but, they are in the wrong order as i have numbers stored in the varchar and one text as shown in the sql statement above. Any advice would be appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/224650-how-to-convert-varchar-to-number-in-sql-statement/ Share on other sites More sharing options...
Pikachu2000 Posted January 16, 2011 Share Posted January 16, 2011 This should do it; see what happens. SELECT * FROM users WHERE ID = '$ID' AND subscription NOT IN( 0, 'admin') ORDER BY CAST( `subscription` AS UNSIGNED INT) DESC Quote Link to comment https://forums.phpfreaks.com/topic/224650-how-to-convert-varchar-to-number-in-sql-statement/#findComment-1160430 Share on other sites More sharing options...
Skylight_lady Posted January 16, 2011 Author Share Posted January 16, 2011 Perfect. Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/224650-how-to-convert-varchar-to-number-in-sql-statement/#findComment-1160431 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.