Solved

Why is num_rows 0?

Posted on 2014-03-29
10
234 Views
Last Modified: 2014-04-19
For some reason, this query is returning 0 rows when it should be returning 33 rows for a given address that is duplicated 33 times. I must have a syntax error somewhere that I am not seeing. Does anyone see what I a missing here?

	function createIfNew()
	{
		$this->logger->log(print_r($this,true));
		$s = $this->db->prepare('SELECT * FROM mcdb2.addresses
								WHERE
								addresses_street1 = ?
								AND addresses_street2 = ?
								AND addresses_city = ?
								AND addresses_state = ?
								AND addresses_zip = ?');
		$s->bind_param('ssssi',$this->addresses_street1,$this->addresses_street2,$this->addresses_city,$this->addresses_state,$this->addresses_zip);
                //Show SQL statement for logs since we can't dump prepared statements.
		$sql = sprintf('SELECT * FROM mcdb2.addresses
								WHERE
								addresses_street1 = "%s"
								AND addresses_street2 = "%s"
								AND addresses_city = "%s"
								AND addresses_state = "%s"
								AND addresses_zip = "%s"',$this->addresses_street1,$this->addresses_street2,$this->addresses_city,$this->addresses_state,$this->addresses_zip);
		$this->logger->log($sql);
		if($s->execute())
		{
			$this->logger->log("Query successful");
			$result = $s->get_result();
			$this->logger->log(print_r($result,true));
			if($result->num_rows == 0)
			{
				$this->logger->log("This address does not exist. Inserting it now.");
				return $this->insert_me();
			} else {
				$this->logger->log("This address already exists in the database. Loading it instead.");
				$row = $result->fetch_assoc();
				$this->addresses_id = $row['addresses_id'];
				$this->load_me();
				return $this->addresses_id;
			}
		}
	}

Open in new window

0
Comment
Question by:DrDamnit
  • 5
  • 5
10 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39964296
Does this statement (line 23) cause a log entry?
$this->logger->log("Query successful");

Are you running with error_reporting(E_ALL) set?

Is there any chance that you have a ZIP+4 type of ZIP code?  It might not work with binding as integer.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39964299
@ray:

Here's the address table create statement.

CREATE TABLE `addresses` (
  `addresses_id` int(11) NOT NULL AUTO_INCREMENT,
  `addresses_label` varchar(45) DEFAULT NULL COMMENT 'Identifies the address.',
  `addresses_street1` varchar(45) DEFAULT NULL,
  `addresses_street2` varchar(45) DEFAULT NULL,
  `addresses_city` varchar(45) DEFAULT NULL,
  `addresses_state` varchar(2) DEFAULT NULL,
  `addresses_zip` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`addresses_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10190 DEFAULT CHARSET=latin1;

Open in new window


Line 23 does create a log statement. I use this (http://svn.highpoweredhelp.com/logger/).

Here's the log entry:
2014-03-29 17:29:51	user	Loading contact 
2014-03-29 17:29:51	user	Setting contacts_company = 12605
2014-03-29 17:29:51	user	Adding email for contact 
2014-03-29 17:29:51	user	Adding email to contact 
2014-03-29 17:29:51	email	Email added successfully.
2014-03-29 17:29:51	user	Contact  is the primary contact for A C Inc. setting them as primary contact.
2014-03-29 17:29:51	type	Address class staring up...
2014-03-29 17:29:51	phone	This type does exist. I'm loading it now.
2014-03-29 17:29:51	phone	Type is loaded. Moving on...
2014-03-29 17:29:51	phone	Type set to: 1
2014-03-29 17:29:51	phone	Checking: CPHONE1
2014-03-29 17:29:51	phone	Checking: CPHONE2
2014-03-29 17:29:51	phone	Checking: CPHONE3
2014-03-29 17:29:51	phone	Checking: CPHONE4
2014-03-29 17:29:51	phone	Checking: CPHONE5
2014-03-29 17:29:51	phone	Checking: CPHONE6
2014-03-29 17:29:51	phone	Checking: CPHONE7
2014-03-29 17:29:51	phone	Checking: CPHONE8
2014-03-29 17:29:51	phone	Checking: CPHONE9
2014-03-29 17:29:51	phone	Checking Fax
2014-03-29 17:29:51	import-mcdb	Query returned with results. There is at least one company with the M_ID in question
2014-03-29 17:29:51	import-mcdb	This company already exists in the database. Company loaded.
2014-03-29 17:29:51	address	Address class staring up...
2014-03-29 17:29:51	user	addr Object
(
    [__oldlabel] => import-mcdb
    [addresses_id] => 
    [addresses_label] => Primary
    [addresses_street1] => 1665 Lakes Parkway Suite 108
    [addresses_street2] => 
    [addresses_city] => Lawrenceville
    [addresses_state] => GA
    [addresses_zip] => 30043
    [db] => mysqli Object
        (
            [affected_rows] => 0
            [client_info] => mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $
            [client_version] => 50010
            [connect_errno] => 0
            [connect_error] => 
            [errno] => 0
            [error] => 
            [error_list] => Array
                (
                )

            [field_count] => 0
            [host_info] => Localhost via UNIX socket
            [info] => 
            [insert_id] => 0
            [server_info] => 5.5.34-0ubuntu0.13.04.1
            [server_version] => 50534
            [stat] => Uptime: 1572439  Threads: 12  Questions: 1007913  Slow queries: 1  Opens: 3492  Flush tables: 1  Open tables: 195  Queries per second avg: 0.640
            [sqlstate] => 00000
            [protocol_version] => 10
            [thread_id] => 206779
            [warning_count] => 0
        )

    [logger] => logger Object
        (
            [logfile:logger:private] => import-mcdb.log
            [handle] => Resource id #64
            [logpath:logger:private] => /home/mcdb/log/
            [mode:logger:private] => a+
            [label] => user
            [skipMessages] => 
            [verbosity] => 1
        )

)

2014-03-29 17:29:51	user	SELECT * FROM mcdb2.addresses
								WHERE
								addresses_street1 = "1665 Lakes Parkway Suite 108"
								AND addresses_street2 = ""
								AND addresses_city = "Lawrenceville"
								AND addresses_state = "GA"
								AND addresses_zip = "30043"
2014-03-29 17:29:51	user	Query successful
2014-03-29 17:29:51	user	mysqli_result Object
(
    [current_field] => 0
    [field_count] => 7
    [lengths] => 
    [num_rows] => 0
    [type] => 0
)

2014-03-29 17:29:51	user	This address does not exist. Inserting it now.
2014-03-29 17:29:51	user	Associating address id (10187) to company id: 12604
2014-03-29 17:29:51	user	Address 10187 associated with company 12604

Open in new window


Log lines are three column: timestamp - tab - class generating the message - tab - message.

You can see in the final lines of this excerpt, the num_rows = 0.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39964344
This is a bit of a guess, but it should be easy enough to test.  Try changing the types from ssssi to sssss.  I am guessing this based on the way the CREATE TABLE handles the addresses_zip column.  After that it's a data-dependent problem so we might need to see some test data that would let us load the table.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39964363
Ah...  Yes.  It started out as sssss, but theni changed it to ssssi because i was to lazy to check the table definitions. When neither worked,  I checked the definition to confirm. This code was copy pasted before I put it back to 'sssss'.

I wish that was the problem. It would be an easy fix.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39964364
Sample data is in the log where I dump the SQL statement.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39964401
I'll take a look at this again tomorrow.  Gotta watch some roundball now.

Maybe try omitting addresses_street2 from the query (just for the test case)?  These notes seems to imply there may be "interesting" issues with NULL.
http://php.net/manual/en/mysqli-stmt.bind-param.php#103144
http://php.net/manual/en/mysqli-stmt.bind-param.php#104935
http://php.net/manual/en/mysqli-stmt.bind-param.php#80118

Another possible approach would be to define the columns as NOT NULL DEFAULT ''
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39965013
Looks like a PHP empty string in a bound variable does not match NULL in a MySQL query.
http://iconoun.com/demo/temp_michael_munger.php

<?php // demo/temp_michael_munger.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
)
;
// 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);
}

// CREATING A FIRST TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE first_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) DEFAULT NULL
, lname VARCHAR(24) DEFAULT NULL
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

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

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO first_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM first_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

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

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

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

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

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

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();




// CREATING ANOTHER TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE second_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

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

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO second_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM second_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

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

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

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

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

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

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

Open in new window

0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39965171
That makes sense. How do we get around this problem? Street2 will be blank quite often, but, when it's not blank it's important. Change the table definition?

For example, when I import data, I want to check for duplicates, so I am looking for street 1 = "123 Happy Street", street 2 is blank, City = "Boston" State="MA" and zipcode = "123456".

Initially, this code was failing (and creating duplicates) because num_rows was coming back as 0 and that's what I was checking. The logic was: if num_rows==0, then the address doesn't exist yet.

But when this fails, num_rows==0 and it inserts a duplicate.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39965231
Michael, I would change the table definition.  Instead of DEFAULT NULL, try NOT NULL DEFAULT '' where the '' is simply two apostrophes to indicate an empty string.  There may be collateral changes needed elsewhere if you have code that depends on columns containing NULLs so tread carefully as you make the change.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39965311
I'll try that, and report back.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now