selenin Posted November 23, 2011 Share Posted November 23, 2011 Hi I have a question, my script so far $site2 = mysql_query("SELECT * FROM `sites` WHERE (`banned` = '0' AND `user`!='{$data->login}') AND sites.user IN (select concat_ws(',', login) from users WHERE `coins`>=`cpc`) AND `id` NOT IN (SELECT `site_id` FROM `plused` WHERE `user_id`='{$data->id}') ORDER BY `cpc` DESC LIMIT 0, 10"); $ext = mysql_num_rows($site2); Now it's ordered by cpc desc, but I want to order as well by from the table users coins desc. Then the user with more coins is before the other. But sites.cpc is another table then users.coins, is this possible? Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/ Share on other sites More sharing options...
requinix Posted November 23, 2011 Share Posted November 23, 2011 Sure. And the PHP thing is for PHP function names. Not SQL. Use simple code tags for that. Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1290634 Share on other sites More sharing options...
selenin Posted November 23, 2011 Author Share Posted November 23, 2011 Don't know where to edit my post like that? $site2 = mysql_query("SELECT * FROM `sites` WHERE (`banned` = '0' AND `user`!='{$data->login}') AND sites.user IN (select concat_ws(',', login) from users WHERE `coins`>=`cpc`) AND `id` NOT IN (SELECT `site_id` FROM `plused` WHERE `user_id`='{$data->id}') ORDER BY `cpc` DESC LIMIT 0, 10"); Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1290639 Share on other sites More sharing options...
requinix Posted November 23, 2011 Share Posted November 23, 2011 You can only edit your own post for a couple minutes after posting. The link would be by the Quote button. That's quote . Try the code button. Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1290644 Share on other sites More sharing options...
fenway Posted November 23, 2011 Share Posted November 23, 2011 You can order by as many expressions as you'd like. Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1290813 Share on other sites More sharing options...
selenin Posted November 24, 2011 Author Share Posted November 24, 2011 You can order by as many expressions as you'd like. $site2 = mysql_query("SELECT * FROM `sites` WHERE (`banned` = '0' AND `user`!='{$data->login}') AND sites.user IN (select concat_ws(',', login) from users WHERE `coins`>=`cpc`) AND `id` NOT IN (SELECT `site_id` FROM `plused` WHERE `user_id`='{$data->id}') ORDER BY `cpc` DESC LIMIT 0, 10"); Yes I know but I want as well order by coins, but coins is from another table, the table users. First it should be ordered by cpc desc and sometimes the cpc are the same the it should be ordered by users.coins Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1290857 Share on other sites More sharing options...
fenway Posted November 24, 2011 Share Posted November 24, 2011 Then you'll need a join -- and if it's many-to-many, you'll need a subquery. Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1291011 Share on other sites More sharing options...
selenin Posted December 1, 2011 Author Share Posted December 1, 2011 Do you think that really works I'm getting all the time error messages, it's the first time I use JOIN, I tried as well UNION but the same Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1292849 Share on other sites More sharing options...
fenway Posted December 1, 2011 Share Posted December 1, 2011 Yes, JOINs really work. Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293092 Share on other sites More sharing options...
selenin Posted December 1, 2011 Author Share Posted December 1, 2011 SELECT * FROM `sites` JOIN users ON sites.user = users.login WHERE (`banned` = '0' AND `user`!='{$data->login}') AND sites.user IN (select concat_ws(',', login) from users WHERE `coins`>=`cpc`) AND `id` NOT IN (SELECT `site_id` FROM `plused` WHERE `user_id`='{$data->id}') ORDER BY `cpc` DESC LIMIT 0, 10 I tried it like that but I'm only getting error messages, I really don't understand this JOIN Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293231 Share on other sites More sharing options...
awjudd Posted December 1, 2011 Share Posted December 1, 2011 What are the error messages that you are getting? ~awjudd Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293260 Share on other sites More sharing options...
selenin Posted December 1, 2011 Author Share Posted December 1, 2011 Yes the error message is simple, because I'm not getting any results, normally I get 10 Error message Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/selenin/public_html/artmeo/google.php on line 5 Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293286 Share on other sites More sharing options...
awjudd Posted December 2, 2011 Share Posted December 2, 2011 That is a PHP error, not a SQL error. You are going to need to post the actual code if you want help on this. ~awjudd Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293314 Share on other sites More sharing options...
fenway Posted December 3, 2011 Share Posted December 3, 2011 Yes the error message is simple, because I'm not getting any results, normally I get 10 Error message Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/selenin/public_html/artmeo/google.php on line 5 Then show us the output of mysql_error(). Link to comment https://forums.phpfreaks.com/topic/251654-multiple-tables-with-multiple-order-by/#findComment-1293914 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.