Page 1 of 1

if no result problem

Posted: Sun May 24, 2009 5:53 am
by WayneG
Hi.
I'm having trouble with this code:

Code: Select all

 
$result = mysql_query("SELECT COUNT(*) FROM demo_players WHERE F_Name=$homeplayer1_fname AND L_Name=$homeplayer1_lname");
 
if (mysql_num_rows($result)==0) {
echo "This player was not found in the Player Averages: $homeplayer1_fname $homeplayer1_lname.<P>
If this player should be included, please advise your Divisional Secretary."; die; }
 
This is always returning a result, even if the player "F_Name' and L_Name" are in the database.

Help much appreciated.

Re: if no result problem

Posted: Sun May 24, 2009 8:19 am
by watson516
I could be wrong but I believe it has something to do with the COUNT(*) in your mysql statement. If you want to use count, don't use mysql_num_rows (not having used count in ages, I believe you reference the count number with $var[0] but I could be wrong). If you want to use mysql_num_rows, just lose the count() in your mysql statement.

Re: if no result problem

Posted: Sun May 24, 2009 8:49 am
by straightman
It looks to me that this COUNT * is going to check for the whole table and is not specific to that name you want to find, therefore, as long as the table is not empty, it would never skip the IF.

in other words, u dont need the COUNT, just do the select and if there is no match, it will go through the echo.

Re: if no result problem

Posted: Sun May 24, 2009 10:17 am
by WayneG
Thanks.
But this doesn't work either:

Code: Select all

 
$result = mysql_query("SELECT * FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'");
if (mysql_num_rows($result)==0) {
echo "This player was not found in the Player Averages: $homeplayer1_fname $homeplayer1_lname.<P>
If this player should be included, please advise your Divisional Secretary."; die; }
 
The player name is still printed even though it's in the database.

Re: if no result problem

Posted: Tue May 26, 2009 3:25 am
by WayneG
Hi Guys. This is doing my nut!
I've tried every combination going without success. This is the simplest version, but it STILL echos the statement, even though the player is in the database:

Code: Select all

$result = mysql_query("SELECT id FROM demo_players WHERE F_Name=$homeplayer1_fname AND L_Name=$homeplayer1_lname");
if (!$result) {
echo "This player was not found in the Player Averages: $homeplayer1_fname $homeplayer1_lname."; die; }
 
Anything to do with "AND" in sql?
Help please!
Wayne.

Re: if no result problem

Posted: Tue May 26, 2009 4:18 am
by JeffG
First off, when you used count(*), the query (if it worked) would always return exactly 1 row, containing the value of count(*) which would be the number of records it found.

However, the query is not working for some reason which is why the mysql_query is returning false.
Try changing your code to the following, so you can see what the error is and also the query you generated:

Code: Select all

 
$query = "SELECT id FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'";
$result = mysql_query($query);
if (!$result)
{
    echo mysql_error()."<br>$query";
}
... etc. ...
 
(Don't forget the single quotes round the two text strings - I'm pretty sure it will fail without those)

Re: if no result problem

Posted: Tue May 26, 2009 4:36 am
by WayneG
Thanks for your quick response.
Here is the output from your code:
"Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
SELECT ID FROM demo_players WHERE F_Name='Pooh' AND L_Name='Bear'"

Yet I know Pooh Bear exists in the database.

Re: if no result problem

Posted: Tue May 26, 2009 5:35 am
by onion2k
What's in the database isn't going to make any difference. It's not connecting to the database. Check the database connection details.

Re: if no result problem

Posted: Tue May 26, 2009 7:57 am
by WayneG
OK. I put in some connection code which worked (ie no sql error):

Code: Select all

mysql_pconnect("x","y","z");
mysql_select_db("w");
$query = "SELECT ID FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'";
$result = mysql_query($query);
if (!$result)
{
    echo mysql_error()."<br>$query"; die; }
 }
BUT, when I called a name which I know isn't in the database, it still brought a result.

Re: if no result problem

Posted: Tue May 26, 2009 8:51 am
by JeffG
WayneG wrote: OK. I put in some connection code which worked (ie no sql error):
Proof? I don't see any tests on the return from mysql_connect and mysql_select_db. And any reason for using mysql_pconnect instead of mysql_connect? I've not used mysql_pconnect myself, so don't know what additional benefits it brings (apart from what I read in the doc).
WayneG wrote: BUT, when I called a name which I know isn't in the database, it still brought a result.
So what was the result you got? If you only tell us half the story, it's really difficult to help. :)

What you appear to be saying is that it returned a row with an id (if you used the same query). So what happened when you tried the query "select * from demo_players where id = '$id'"? That would be then next thing to try to see exactly what your initial query found. Bug-fixing often requires a bit of detective work. ;)

As an aside, I often open a command window when I have a problem and run mysql in there to check out queries etc. directly. I've found that very helpful in investigating just what's going on in a database or with my code.

Re: if no result problem

Posted: Tue May 26, 2009 9:33 am
by WayneG
OK I admit I'm not an expert!
I'm a code tweaker rather than a code writer.

I used "mysql_pconnect" just because I've used it before and it worked!

The "proof" was when I didn't get an error message, didn't die, and the rest of the page loaded. Don't know what proof I need to post here?
So what was the result you got? If you only tell us half the story, it's really difficult to help. :)
The result was I didn't get an error message, didn't die, and the rest of the page loaded. I really don't know what else to say.

On the previous page is a form where I enter a first name in one dropdown, and a last name in another dropdown.
If the name (together) isn't in the database, I want to say

Code: Select all

echo "This player was not found in the Database: $homeplayer1_fname $homeplayer1_lname"; die; }
This is the latest I've tried, with some code after the problem code to hopefully show what I'm trying to achieve.

Code: Select all

mysql_connect("x","y","z");
mysql_select_db("w");
 
$query = "SELECT * FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'";
$result = mysql_query($query);
if (!$result)
{
    echo mysql_error()."<br>$query"; die; }
 
?>
</font>
<?
 echo "$homeplayer1_fname $homeplayer1_lname v $awayplayer1_fname $awayplayer1_lname : "?>
<select name="set1">
<? if ( $homeplayer1_lname == "NoPlayer" ) {
  echo ?>
  <option value="W-0">W-0</option>
  <option value="0-W">0-W</option>
  <option value="0-0">0-0</option>
</select><? ;
}
This prints the (incorrect) player name.

Not sure what a "command window" is, but it sounds useful.

Re: if no result problem

Posted: Tue May 26, 2009 10:22 am
by WayneG
YAY! I've cracked it!!

The original code actually worked!
The reason it didn't work before was I needed to connect to the DB again!

I assumed I didn't need to as it echoed other stuff in the page fine.

So this is it:

Code: Select all

mysql_connect("x","y","z");
mysql_select_db("w");
 
$result = mysql_query("SELECT COUNT(*) AS number FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'");
$row = mysql_fetch_assoc($result);
 
if ($row['number']==0) {
echo "This player was not found in the Player Averages: $homeplayer1_fname $homeplayer1_lname.<P>
If this player should be included, please advise your Divisional Secretary."; die; }
Simple. Just like I thought!
Thanks for all contributions,
Wayne.

Re: if no result problem

Posted: Tue May 26, 2009 1:45 pm
by JeffG
Well, I wouldn't claim to be an expert either! However, when things go wrong there are a few basic things to try to find out what the problem is. I hope I've shared some of them here. Glad it's all working for you now :)