C3rd

PHP: about NULL vs 0
Posted: 18 Sep 2017, 20:43pm - Monday

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!

Back to list
Search
Pages
Categories
Related Links