SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count number of specific rows

    I am trying to count the number of rows in my database that have a specific date.

    The "reservation_date" is the column for the date I want to check. The variable "$date" is the date I am checking for. I want to know how many dates in "reservation_date" match the variable "$date".

    I have the "reservation_date" and "$date" in the same format.

    I have generated the following code, but I get a message indicating "mysql_numrows(): supplied argument is not a valid".

    What am I doing wrong?

    Here is the code:
    Code:
     <?php
            include("config.php");
            mysql_connect($hostname,$username, $password)
            OR DIE ('Unable to connect to database! Please try again later.');
    
            mysql_select_db($dbname);
            $table=$_POST['year'];
            $query="SELECT * FROM $table WHERE reservation_date = '$date' ";
            $result=mysql_query($query);
            $num=mysql_numrows($result);
            echo "Number of reservations = $num";
                        
             mysql_close();
     ?>
    Lawrence

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you should not retrieve all the rows and count them in php -- that's terribly inefficient, unless you also need to list all the rows individually (in which case it's essential)

    instead, if all you want is a count, just ask the database to return the count --
    Code:
    SELECT COUNT(*) AS number_of_rows
      FROM $table 
     WHERE reservation_date = '$date'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you should not retrieve all the rows and count them in php -- that's terribly inefficient, unless you also need to list all the rows individually (in which case it's essential)

    instead, if all you want is a count, just ask the database to return the count --
    Code:
    SELECT COUNT(*) AS number_of_rows
      FROM $table 
     WHERE reservation_date = '$date'
    Thank you for the tip.

    Unfortunately, I get the same error with this code.

    Lawrence

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    moving thread to php forum

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

  5. #5
    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)
    Give this a try:-

    PHP Code:
    <?php
    $rDatabase 
    mysqli_connect($sHostname$sUsername$sPassword$sDatabase3306);
    $sSQL sprintf("SELECT COUNT(*) AS number_of_rows FROM %s WHERE reservation_date = '%s'",
        
    mysqli_real_escape_string($_POST['year']),
        
    mysqli_real_escape_string($sDate)
    );
    $rResult mysqli_query($rDatabase$sSQL);
    while (
    $aRow mysqli_fetch_assoc()) {
        
    $iNumberOfRows $aRow['number_of_rows'];
    }
    echo 
    '<h4>Executed Query: ' $sSQL '</h4>';
    echo 
    '<p>The number of rows returned was ' $iNumberOfRows '</p>';
    ?>
    Additionally, try hard-coding some values to see if a result set is returned, then check the variables you're using in the query to see if they contain the expected values.
    @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.

  6. #6
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if ( ! isset($_POST['date'] ) )
    {
        die;
    }

    $date mysql_real_escape_string($_POST['date']);

    $query mysql_query("
    SELECT COUNT(*) AS num 
      FROM your_table 
    WHERE  reservation_date = '
    $date'
    "
    ) or die(mysql_error());

    $row mysql_fetch_array($query);

    echo 
    $row['num']; 
    my mobile portal
    ghiris.ro

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ernie1 View Post
    PHP Code:
    if ( ! isset($_POST['date'] ) )
    {
        die;
    }

    $date mysql_real_escape_string($_POST['date']);

    $query mysql_query("
    SELECT COUNT(*) AS num 
      FROM your_table 
    WHERE  reservation_date = '
    $date'
    "
    ) or die(mysql_error());

    $row mysql_fetch_array($query);

    echo 
    $row['num']; 
    Thank you. This worked perfect.

    Lawrence

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to thank everyone who responded to this thread.

    I spend so much time working on code like this that I can't seem to see the simple, and obvious, errors I make.

    Lawrence


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
  •