asked on

Problem with count.

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

Ray Paseur

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.
https://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

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

ASKER

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

breeze351

ASKER

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.

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

breeze351

ASKER

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