Multiple tables with multiple order by

[selenin]

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?

[requinix]

Sure.

 

And the PHP thing is for PHP function names. Not SQL. Use simple code tags for that.

[selenin]

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");

[requinix]

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.

[fenway]

You can order by as many expressions as you'd like.

[selenin]

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

[fenway]

Then you'll need a join -- and if it's many-to-many, you'll need a subquery.

[selenin]

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 

[fenway]

Yes, JOINs really work.

[selenin]

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

[awjudd]

What are the error messages that you are getting?

 

~awjudd

[selenin]

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

[awjudd]

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

[fenway]

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().