Page 1 of 1

Php and MSQL simple mixup problem.... help!

Posted: Thu Jul 06, 2006 2:42 pm
by Citizen
Ok, so I'm trying to run a query that finds the scores from a particular game for a particular user.

This code returns a single value when searching only by the particular game:

Code: Select all

$sql4="SELECT * FROM `arcade_highscores` WHERE `gamename` = '$gameid' ORDER BY `score` DESC LIMIT 1";
	$result4=mysql_query($sql4);
	$row4 = mysql_fetch_array($result4);
	$scorenum = mysql_num_rows($result4);
        echo "$scorenum";
	if($scorenum == 1){
		$yourscore = $row4["score"];
	}
	else {
		$yourscore = "None";
	}
This code returns a single value when searching by the particular user:


Code: Select all

$sql4="SELECT * FROM `arcade_highscores` WHERE `user` = '$user' ORDER BY `score` DESC LIMIT 1";
	$result4=mysql_query($sql4);
	$row4 = mysql_fetch_array($result4);
	$scorenum = mysql_num_rows($result4);
        echo "$scorenum";
	if($scorenum == 1){
		$yourscore = $row4["score"];
	}
	else {
		$yourscore = "None";
	}
But, this code returns no values when I run a query for both at the same time:

Code: Select all

$sql4="SELECT * FROM `arcade_highscores` WHERE `gamename` = '$gameid', `user` = '$user' ORDER BY `score` DESC LIMIT 1";
	$result4=mysql_query($sql4);
	$row4 = mysql_fetch_array($result4);
	$scorenum = mysql_num_rows($result4);
        echo "$scorenum";
	if($scorenum == 1){
		$yourscore = $row4["score"];
	}
	else {
		$yourscore = "None";
	}
Whats going on?

Posted: Thu Jul 06, 2006 4:48 pm
by RobertGonzalez
Do the first two queries return the exact same data? The third of those queries is checking against two WHERE clauses (with no AND mind you) which makes the search parameters a tad more strict.

Posted: Fri Jul 07, 2006 9:54 am
by Citizen
The first two queries come up with two different results but the third comes up with nothing.

Posted: Fri Jul 07, 2006 10:28 am
by JayBird
Shoudln't your third query look like this?

Code: Select all

$sql4="SELECT * FROM `arcade_highscores` WHERE `gamename` = '$gameid' AND `user` = '$user' ORDER BY `score` DESC LIMIT 1";

Posted: Fri Jul 07, 2006 11:04 am
by RobertGonzalez
Citizen wrote:The first two queries come up with two different results but the third comes up with nothing.
The reason that the third query is coming up with nothing (aside from the syntax issue with the AND) is that the first two queries are grabbing different data. Since there is nothing common between the first two query results, when you combine the two for common results, you get none. Does that make sense?