asked on

Why is num_rows 0?

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

Ray Paseur

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.

DrDamnit

ASKER

@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:

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.

Ray Paseur

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.

DrDamnit

ASKER

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.

DrDamnit

ASKER

Sample data is in the log where I dump the SQL statement.

Ray Paseur

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 ''

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

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

Start Free Trial

DrDamnit

ASKER

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.

Ray Paseur

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.

DrDamnit

ASKER

I'll try that, and report back.