if no result problem

[wayneglossop]

Hi.
I'm having trouble with this code:

$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.

[Mark Baker]

If F_Name and L_Name are varchars, try wrapping the values in quotes in your query.

$result = mysql_query("SELECT COUNT(*) FROM demo_players WHERE F_Name='$homeplayer1_fname' AND L_Name='$homeplayer1_lname'"); 

And remember to escape your $homeplayer1_fname and $homeplayer1_lname values

[SJH]

A COUNT(*) query will always return exactly one row - containing the number of records in the table that match the condition.

Try this:

$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) { 

[r937]

This is always returning a result, even if the player "F_Name' and L_Name" are in the database.

i don't do php, but without the quotes around the name strings, your result was a syntax error and you did not know it, which is a coding error

so perhaps we should say that a COUNT(*) query will always return exactly one row if it executes

[wayneglossop]

THanks guys.
But I tried both examples with no joy.
Will ir work without a COUNT?
This is my latest (unsuccessful) attempt:

$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.

[AnthonySterling]

You should be able to adapt the following to suit.

<?php
$rResult = mysql_query(
    sprintf(
        "SELECT COUNT(firstName) AS numberOfRecords FROM table WHERE firstName = '%s' AND lastName = '%s'",
        mysql_real_escape_string($sFirstName),
        mysql_real_escape_string($sLastName)
    )
);
$aQueryDataset = mysql_fetch_assoc($rResult);
if($aQueryDataset['numberOfRecords'] === 0)
{
    printf(
        'Sorry, %s %s does not exist in our database, go ring someone.',
        $sFirstName,
        $sLastName
    );
}
else
{
    printf(
        'Yay! Good ol %s %s exists in our database.',
        $sFirstName,
        $sLastName
    );
}
?>

[wayneglossop]

Thanks SilverBullett, but still no joy. I can't believe how many combinations don't work!

Here is the problem when I use your script: It says " Yay! Good ol x y exists in our database" whether they exist or not.

Is the problem in the combination of first name and second name?
I want to select the rows where the first name and second name are in the same row.
I don't know. But hopefully someone out there does!
Cheers,
Wayne.

[r937]

Is the problem in the combination of first name and second name?

easy way to find out is to test your query outside of php

[wayneglossop]

Sorry guys. This has really got me stumped. It just seems so obvious, yet nothing works.
I've attached the file (normally .php) in the hope someone can spot a glaring error.
Cheers,
Wayne.

[wayneglossop]

YAY! I've cracked it!!

I used the code from SJH.
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:
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; }

Thanks for all contributions,
Wayne.