PHP: about NULL vs 0

Info.Tech, PHP, Silex Framework, SQL: Structured Query Language, Web Development Add comments

A client reported about the data wasn’t populating… So I debug and took me hours to find out…

Background: We have this enterprise system with legacy code and we created a QueryDriver class to make the legacy, Silex DBAL & CodeIgniter works and reusable. Behind the QueryDriver, it uses vsprintf() function.

$parcelData = [];
$jobVarObj = new JobsVarsTable();
$jobVarObj->setDrivers($this->getQueryDriver());
$prime_parcel = $jobVarObj->getVarValue($this->job_id, 'PRIMARY_PARCEL');

...

$q = "SELECT * FROM jobs_parcels WHERE JID = '%d' AND ID != '%d' AND Deleted IS NULL";
$sql = $this->getQueryDriver()->fetchAll($q, [$this->job_id, $prime_parcel]);
$parcel_data_list = $sql->getData();

In that codes above, the $parcel_data_list is always empty and I know there are 1 row results.

2 hours later…

Note: $prime_parcel = null; // if does not exists in the records, will return literal null

I just realised that printing vsprintf($q, [ $this->job, $prime_parcel ]) will gave me what I expected because when you sprintf() or vsprintf() the NULL to %d, this will convert to ZERO. But actually when I pass the query to our QueryDriver, %d and %s are actually converted to ? which the NULL become literal NULL in the query. Instead of “SELECT * FROM jobs_parcels JID = ‘123’ AND ID != ‘0’ AND DELETED IS NULL” then becomes “SELECT * FROM jobs_parcels JID = ‘123’ AND ID != NULL AND DELETED IS NULL”. So there will be no result.

So lesson learn…

Solution:

$parcelData = [];
$jobVarObj = new JobsVarsTable();
$jobVarObj->setDrivers($this->getQueryDriver());
$prime_parcel = (int)$jobVarObj->getVarValue($this->job_id, 'PRIMARY_PARCEL', 'n');

...

$q = "SELECT * FROM jobs_parcels WHERE JID = '%d' AND ID != '%d' AND Deleted IS NULL";
$sql = $this->getQueryDriver()->fetchAll($q, [$this->job_id, $prime_parcel]);
$parcel_data_list = $sql->getData();

Then it solves my 2hrs problem!!!

Ok that’s my entry for half of 2017… hehehe..

Cheers!




Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in