[MySql] Order By Another Table...

[zackcez]

Well I've done this before I'm just not remembering how...Takes too much thinking for me right now

 

I have this:

$result = $db->query_read("SELECT * FROM server_status ORDER BY up DESC");

 

And this function:

function getVotes($id)
{
	$gVotes = mysql_query("SELECT * FROM server_votes WHERE vote='1' AND server_id='" . $id . "'");
	$bVotes =mysql_query("SELECT * FROM server_votes WHERE vote='-1' AND server_id='" . $id . "'");
	$goodVotes = mysql_num_rows($gVotes);
	$badVotes = mysql_num_rows($bVotes);
	return ($goodVotes - $badVotes);
}

 

I know that function is ugly but at the moment that's not what I care about.

 

I'd like to know how I can have it order by "up" (which is either a 1 or a 0) and then order by "getVotes"...

[Mchl]

ANd what's the relation between these tables?

[zackcez]

One holds the servers data and the other one holds the servers ranking or "votes"...

[Mchl]

SELECT
  s.*, 
  v.score
FROM
  server_status AS s
CROSS JOIN
  (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v
USING (server_id)
ORDER BY s.up, v.score

[zackcez]

Would you explain that for me if you could please?

[Mchl]

There's a subquery:

SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id

you can run it on its own to see it returns sum of votes for each server and server_id. This result is then joined with server_status table using server_id column.

[zackcez]

Alright, thanks; But how exactly does the cross join syntax work...I've never used that before ;s.

[Mchl]

It creates a Cartesian product of both sets of data limited to elements that meet the condition (in this case both elements need to have equal values in server_id column)

[zackcez]

I'm not sure if this would matter, but within server_status the "server_id" column is actually called "owner_id".

[Mchl]

It would. We need to change join condition

 

SELECT
  s.*,
  v.score
FROM
  server_status AS s
CROSS JOIN
  (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v
ON s.owner_id = v.server_id
ORDER BY s.up, v.score

[zackcez]

so it would look like this :s?

$result = $db->query_read("SELECT s.*, v.score FROM server_status AS s CROSS JOIN (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v ON s.owner_id = v.server_id ORDER BY s.up, v.score DESC");

 

What would I need to change in order to make it work with my fetch_array then?

[Mchl]

I don't know. I don't know what $db is.

[zackcez]

$db is the database class of vbulletin...it's just a normal mysql_query pretty much.

[Mchl]

Then just use it as normal mysql_query.

[zackcez]

Sorry but...I thought I had it but it doesn't output anything :s:

$result = mysql_query("SELECT * FROM server_status AS s CROSS JOIN (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v ON s.owner_id = v.server_id ORDER BY s.up, v.score DESC");
while($row = mysql_fetch_array($result)) {
echo $row['server_name']; // That's a column in the server_status table...
}

[Mchl]

Let's add some debugging code then and see if I made a mistake somewhere:

 

$result = mysql_query("SELECT * FROM server_status AS s CROSS JOIN (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v ON s.owner_id = v.server_id ORDER BY s.up, v.score DESC") or die(mysql_error());
while($row = mysql_fetch_array($result)) {
echo $row['server_name']; // That's a column in the server_status table...
}

[zackcez]

<?php
$result = mysql_query("SELECT * FROM server_status AS s CROSS JOIN (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v ON s.owner_id = v.server_id ORDER BY s.up, v.score DESC") or die(mysql_error());
while($row = mysql_fetch_array($result)) {
	echo $row['server_name']; // That's a column in the server_status table...
	echo("hmm");
}
?>

Nothing at all :s.

[Mchl]

Can you run this query in PhpMyAdmin or similar tool just to check if it runs fine?

 

Also, please familiarise yourself with: http://www.phpfreaks.com/tutorial/debugging-a-beginners-guide/page1

[zackcez]

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0020 sec )

[Mchl]

That's something. Apprently there are no rows meeting this set of conditions. HOw about this?

 

SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id

[zackcez]

I just found out why it wasn't work...If there's no votes for that server in the table, then it doesn't do anything...is there anyway I can just have it return zero...Sorry I didn't even think of that I thought there was still a few rows in it :s.

[Mchl]

Sure there is. That's what LEFT JOINS are for

 

SELECT
  s.*,
  IFNULL(v.score,0) AS score
FROM
  server_status AS s
LEFT JOIN
  (SELECT server_id, SUM(IF(vote=1,1,0))-SUM(IF(vote=-1,1,0)) AS score FROM server_votes GROUP BY server_id) AS v
ON s.owner_id = v.server_id
ORDER BY s.up, v.score DESC

[zackcez]

Thank you very much, it's all working good now .