Zend Framework convert NULL string to actual NULL value for MySql

Question

Somehow when doing an import of data into mysql using a multi-row insert with execute() function, there were many rows added with the string NULL for some columns. How can I convert those NULL strings into MySQL NULL values so that I can check for empty values using is_null() in php when displaying data.

How can I avoid this problem? I was using the quote() function for each piece of data. Was that the problem using quote on empty fields? I was importing a csv file.

thank you

Answer 1

To insert an SQL NULL or any other expression, you can use a Zend_Db_Expr object. Even if you use quote(), ZF passes these objects through unquoted.

$null = new Zend_Db_Expr("NULL");

$table = new MyTable();
$table->insert(123, "abc", $null);

Note that because use of Zend_Db_Expr bypasses both quoting and parameterization, you're responsible for protecting against SQL injection.

---

This is tangential to your question, but consider LOAD DATA INFILE if you're bulk-loading a CSV file. It can be 20x faster than even using multi-row INSERT.