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
breeze351Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
0
Ray PaseurCommented:
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
0
breeze351Author 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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Enroll Today
breeze351Author 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.
0
hieloCommented:
>> 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
breeze351Author Commented:
Thanks for the help with the count.  I'm opening another question because we got side tracked on the original queston.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.