Problem with count.

breeze351
breeze351 used Ask the Experts™
on
I'm having a bad couple of days.  This code will return the correct count on some addresses and not on others.  The count should be either one or zero.  I've pasted the query into MySql and it does return the correct record.

$SqlString1 = "SELECT * FROM bld1 WHERE SEQ = '".$SurveySeq.$work."'";
      $Bld1 = mysql_query($SqlString1);
      $ROW1=mysql_fetch_array($Bld1, MYSQL_BOTH);

      // ****************************************************
      // * Find out number of rows.                         *
      // ****************************************************
      $Bld1_Count = mysql_num_rows($Bld1);
      echo "String1 = ".$SqlString1."<br>";
      echo "Count = ".$Bld1_Count."<br>";

If I use "Select count(seq) in the query, how do it get the number of rows?
Glenn
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Ray Paseur
Most Valuable Expert 2011
Top Expert 2016

Commented:
You've got to get off MySQL.  Just do it now before you write any more code that is going to have to be rewritten in the future.  This is not abstract - it's something that will happen.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

To the instant question, when you fetch the results from this query into $row:

SELECT COUNT(seq) AS count_seq FROM...

You can look in the results set with var_dump($row) to find an element named count_seq with the COUNT() information.
Ray Paseur
Most Valuable Expert 2011
Top Expert 2016

Commented:
Here's a script showing two ways of getting the number of rows matching a WHERE clause.  The first uses the num_rows property of the query result object.  The second uses SELECT COUNT and shows where to find the number of matching rows in the results set.
http://iconoun.com/demo/temp_glenn.php

<?php // demo/temp_glenn.php

/**
 * http://www.experts-exchange.com/questions/28704131/Problem-with-count.html
 */
error_reporting(E_ALL);
echo '<pre>';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, OWCN1 VARCHAR( 4) NOT NULL DEFAULT ''
, OWCN2 VARCHAR( 4) NOT NULL DEFAULT ''
, MGCDE VARCHAR( 8) NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING TEST DATA INTO THE TABLE
$sql = "INSERT INTO my_table ( OWCN1, OWCN2 ) VALUES ( '0000', '0000' )";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

$mysqli->query( "INSERT INTO my_table ( OWCN1, OWCN2 ) VALUES ( '0001', '0001' )" );
$mysqli->query( "INSERT INTO my_table ( OWCN1, OWCN2 ) VALUES ( '0002', '0001' )" );
$mysqli->query( "INSERT INTO my_table ( OWCN1, OWCN2 ) VALUES ( '1001', '0001' )" );
$mysqli->query( "INSERT INTO my_table ( OWCN1, OWCN2 ) VALUES ( '    ', '    ' )" );



// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT * FROM my_table WHERE OWCN2='0001' ";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND NO DATA ";
}
else
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND $num_fmt ROWS OF DATA ";
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;



// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT COUNT(id) AS count_id FROM my_table WHERE OWCN2='0001'";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND NO DATA ";
}
else
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND $num_fmt ROWS OF DATA ";
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}

Open in new window

HTH, ~Ray
breeze351

Author

Commented:
Ray
I tried you code:

$sql = "SELECT * FROM bld1 WHERE SEQ='NY1091000644' ";
$res = $mysqli->query($sql);

I get the following error:
Notice: Undefined variable: mysqli in /home/mrbreeze/public_html/Building_Insert.php on line 76
Fatal error: Call to a member function query() on a non-object in /home/mrbreeze/public_html/Building_Insert.php on line 76
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Enroll Today!
breeze351

Author

Commented:
Back to the orginal question.  The sql query does not return any records  even though there is a record in the table.  I've echoed the query and it works in phpmyadmin but not in the php page.
Expert of the Year 2008
Top Expert 2008
Commented:
>> If I use "Select count(seq) in the query, how do it get the number of rows?
Assuming you don't have any syntax erros, a "Count()" query  always returns only one record.  So you need to retrieve the record first and get if from the extracted "row" value -ex:
$SqlString1 = "SELECT COUNT(*) as `total` FROM bld1 WHERE SEQ = '".$SurveySeq.$work."'";
      $Bld1 = mysql_query($SqlString1);
      $ROW1=mysql_fetch_assoc($Bld1);

echo $ROW1['total'];//should give you the total number of rows

Open in new window

breeze351

Author

Commented:
Thanks for the help with the count.  I'm opening another question because we got side tracked on the original queston.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial