SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    if no result problem

    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.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If F_Name and L_Name are varchars, try wrapping the values in quotes in your query.
    PHP Code:
    $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
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A COUNT(*) query will always return exactly one row - containing the number of records in the table that match the condition.

    Try this:

    PHP Code:
    $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) { 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wayneglossop View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You should be able to adapt the following to suit.
    PHP Code:
    <?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
        
    );
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wayneglossop View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Attached Files Attached Files

  10. #10
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •