Joining Querys

Krakken

Is it possible to join two querys together? Such as:

$phpOne = mysql_query("SELECT * FROM dataone", $dtaHost);
$phpTwo = mysql_query("SELECT * FROM datatwo", $dtaHost);

Then joining "$phpOne" and "$phpTwo" together into "$phpJoin" so that when you do:

mysql_fetch_array($phpJoin);

It will return all of the values from "$phpOne" then "$phpTwo".

Krakken

Lars_Berg

$phpOne = mysql_query("SELECT dataone.*,1 as o FROM dataone union all 
SELECT datatwo.*,2 FROM datatwo order by o", $dtaHost);

A union requires that the number of values in select lists are that same and that ecah corresponding column pair is comparable.

Krakken

Can't do that because I do ORDER BY for both in my case.

Krakken

A better example maybe:


$phpOne = mysql_query("SELECT * FROM data WHERE score > 100 ORDER BY score ASC", $dtaHost);
$phpTwo = mysql_query("SELECT * FROM data WHERE score < 100 ORDER BY score DESC", $dtaHost);

Then join those together.

Lars_Berg

Put the order by after the union

$phpOne = mysql_query("SELECT data.*,1 as o FROM data WHERE score > 100 union " . 
"SELECT data.*,2 FROM data WHERE score < 100 " . 
"ORDER BY o, " . 
" case when o = 1 then score else 100 end asc, " . 
"case when o = 2 then score else 0 end DESC", $dtaHost);

Krakken

Thanks. Maybe you made an error though as it doesn't return anything. Here are the snippets of my code that are relevant.

    

$phpFight = mysql_query(

 "SELECT mbUser.*,1 as idUse FROM mbUser WHERE cntScore > $usrScore UNION " .
 "SELECT mbUser.*,2 FROM mbUser WHERE cntScore < $usrScore " .
 "ORDER BY idUse, CASE WHEN idUse = 1 THEN cntScore ELSE $usrScore END ASC, CASE WHEN idUse = 2 THEN cntScore ELSE 0 END DESC"

, $phpHost);


for ($inUse = 0; $inUse < 15; $inUse++) {
  $fitData = fncQuery($phpFight);
  echo '<option value="'. $fitData['actUser'] .'">'. $fitData['actUser'] .' ('. $fitData['cntScore'] .')</option>';
}

Lars_Berg

You should add some error handling

$phpFight = mysql_query(
 "SELECT mbUser.*,1 as idUse FROM mbUser WHERE cntScore > $usrScore UNION " .
 "SELECT mbUser.*,2 FROM mbUser WHERE cntScore < $usrScore " .
 "ORDER BY idUse, CASE WHEN idUse = 1 THEN cntScore ELSE 100 END ASC, CASE WHEN idUse = 2 THEN cntScore ELSE 0 END DESC"
, $phpHost) or die (mysql_error());

Maybe you are using a version of mysql that doesn't support union.

Krakken

Damn yep that's the problem :/
"MySQL 3.23.35 running on localhost"

Man this is BAD. I don't have any hosting with MySQL 4.0
Is there any other way?

Krakken

sigh there MUST be another way :/

Lars_Berg

Well, as it seems that ordering is the problem it can be rewritten as

$phpOne = mysql_query("SELECT *FROM data ORDER BY " . 
" case when score cntScore > $usrScore then cntScore else 10000000 end asc, " . 
"case when cntScore < $usrScore then cntScore else 0 end DESC", $dtaHost) or die(mysql_error());

the 10000000 could be any value that is larger than the highest cntScore.

Your earlier queries did not include records where cntScore is equal to $usrScore. Is that intentional? If so you can add the criteria

where cntScore != $usrScore

Krakken

Yeah that was intentional as I wanted it to list everyone but yourself. Thanks for the help! You have saved me a lot of stress with this 🙂