PHP fgetcsv: empty strings vs NULL

LotsOfZeros · Mar 29, 2011

Using fgetcsv to pull data into MySQL. All columns in the table default to NULL but when I run the script, it inserts what I am assuming are empty strings into the columns where the CSV had nothing but , "", "", "", "etc"

How to convert these empty strings so they will insert into the column in the database as NULL?

Help me and I'll show boobs

lschmidt · Mar 29, 2011

fgetcsv returns an array. for any of those array elements that are empty(), use NULL inside your mysql query for the corresponding mysql field.

what's your code look like? it would be something like:

Code:

$data = fgetcsv($handle, 1000, ',');
$mysql_values = array();

foreach($data as $element){
    if(empty($element)){
        $mysql_values[] = 'NULL';
    }else{
        $mysql_values[] = '\''.mysql_real_escape_string($element).'\'';
    }
}

and then just use $mysql_values instead of $data. At least that's how I would do it.

LotsOfZeros · Mar 29, 2011

^^^
Thanks for the info. I like how you have addressed the empty row but my question was more specifically addressing the issue of some of the columns came in with empty strings.

I need to convert empty cells into NULL

Here's my code:

Code:

$inbound = "inbound";
$completed = "completed";
foreach (glob($inbound . "/*.csv") as $file) {
  $row = 1;
  if (($handle = fopen($file, "r")) !== FALSE) {
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
          if ($row == 1) { // skipping header row
          } else {
            $data = array_map('mysql_real_escape_string', $data);
            $insertquery = "INSERT INTO i_orders (io_company, io_name, io_email, io_addr1, io_addr2, io_city, io_country, io_orderdate, io_orderno, io_ordstatus, io_postalcode, io_shipname, io_state, io_phone, io_desc, io_qty, io_sku, io_weight, io_tcost, io_cost, io_orderid) VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]')";
            mysql_query($insertquery) or die(mysql_error());
          }
          $row++;
      }
      fclose($handle);
      rename($file, $completed . "/" .date('YmdHis', strtotime("now")) . "_" . substr($file, 8));
  } else {
    echo "Failure: file " . $file . " can not be opened!";
  }
}
exit;

lschmidt · Mar 29, 2011

Try this, I'm pretty certain it should work. Note that this would also turn 0 (both 0 as an integer as well as "0" as a string) and whitespace (i.e. " ") into NULL as well as an empty string (""). If you only want an empty string to turn into NULL you could use strlen() instead of empty().

Code:

$inbound = "inbound";
$completed = "completed";
foreach (glob($inbound . "/*.csv") as $file) {
  $row = 1;
  if (($handle = fopen($file, "r")) !== FALSE) {
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
          if ($row > 1) {
            $mysql_values = array();

            foreach($data as $element){
              if(empty(trim($element))){
                $mysql_values[] = 'NULL';
              }else{
                $mysql_values[] = '\''.mysql_real_escape_string($element).'\'';
              }
            }

            $insertquery = 'INSERT INTO i_orders (io_company, io_name, io_email, io_addr1, io_addr2, io_city, io_country, io_orderdate, io_orderno, io_ordstatus, io_postalcode, io_shipname, io_state, io_phone, io_desc, io_qty, io_sku, io_weight, io_tcost, io_cost, io_orderid) VALUES ('.implode(', ', $mysql_values).')';
            mysql_query($insertquery) or die(mysql_error());
          }
          $row++;
      }
      fclose($handle);
      rename($file, $completed . "/" .date('YmdHis', strtotime("now")) . "_" . substr($file, 8));
  } else {
    echo "Failure: file " . $file . " can not be opened!";
  }
}
exit;

LotsOfZeros · Mar 30, 2011

Fatal error: Can't use function return value in write context in ...

Hmmm - I think we're on the right track here though.

Rage9 · Mar 30, 2011

LotsOfZeros said:
Using fgetcsv to pull data into MySQL. All columns in the table default to NULL but when I run the script, it inserts what I am assuming are empty strings into the columns where the CSV had nothing but , "", "", "", "etc"

How to convert these empty strings so they will insert into the column in the database as NULL?

Help me and I'll show boobs

The question is why do you need to convert them? NULL == '' (empty string) == 0 (integer) == FALSE (boolean)

When you pull the row all you have to do is test for a value, example:

$row = mysql_fetch_array($result);
if($row['something']){ //it has a value } else { //it doesn't have a value }

or inversely

if(!$row['something']){ //it doesn't have a value e } else { //it has a value }

lschmidt · Mar 30, 2011

Rage9 said:
The question is why do you need to convert them? NULL == '' (empty string) == 0 (integer) == FALSE (boolean)

When you pull the row all you have to do is test for a value, example:

$row = mysql_fetch_array($result);
if($row['something']){ //it has a value } else { //it doesn't have a value }

or inversely

if(!$row['something']){ //it doesn't have a value e } else { //it has a value }

Perhaps he does certain queries which rely on fields being set to NULL. For example, NULL values are sorted before empty strings, NULL values have a NULL length instead of 0 for an empty string, COUNT() will count empty strings but not NULL values, etc. Also logically, a value of NULL could indicate something different than an empty string, depending on what you're doing.

LotsOfZeros, I made a mistake by putting trim() inside empty().

Here you go:

Code:

$inbound = "inbound";
$completed = "completed";
foreach (glob($inbound . "/*.csv") as $file) {
  $row = 1;
  if (($handle = fopen($file, "r")) !== FALSE) {
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
          if ($row > 1) {
            $mysql_values = array();

            foreach($data as $element){
              $element = trim($element);

              if(empty($element)){
                $mysql_values[] = 'NULL';
              }else{
                $mysql_values[] = '\''.mysql_real_escape_string($element).'\'';
              }
            }

            $insertquery = 'INSERT INTO i_orders (io_company, io_name, io_email, io_addr1, io_addr2, io_city, io_country, io_orderdate, io_orderno, io_ordstatus, io_postalcode, io_shipname, io_state, io_phone, io_desc, io_qty, io_sku, io_weight, io_tcost, io_cost, io_orderid) VALUES ('.implode(', ', $mysql_values).')';
            mysql_query($insertquery) or die(mysql_error());
          }
          $row++;
      }
      fclose($handle);
      rename($file, $completed . "/" .date('YmdHis', strtotime("now")) . "_" . substr($file, 8));
  } else {
    echo "Failure: file " . $file . " can not be opened!";
  }
}
exit;

LotsOfZeros · Mar 30, 2011

Rage9 said:
The question is why do you need to convert them? NULL == '' (empty string) == 0 (integer) == FALSE (boolean)

When you pull the row all you have to do is test for a value, example:

$row = mysql_fetch_array($result);
if($row['something']){ //it has a value } else { //it doesn't have a value }

or inversely

if(!$row['something']){ //it doesn't have a value e } else { //it has a value }

What would I put in place of 'something'? An actual value like '0' or FALSE?
I wasn't sure how to validate those fields other then the string length.

lschmidt said:
LotsOfZeros, I made a mistake by putting trim() inside empty().

Here you go:

Thanks lschmidt, I will try that.

Thanks to all the participation on this thread, here's the boobs:

Rage9 · Mar 30, 2011

LotsOfZeros said:
What would I put in place of 'something'? An actual value like '0' or FALSE?
I wasn't sure how to validate those fields other then the string length.

Example:
I have a table 'users' with the columns/fields:
id, name, address

If you use mysql_fetch_array() it's the name of the column/field, it creates an associative array, the names of the indexes are the same as the name of the columns/fields. Example:

Code:

$result = mysql_query('SELECT * FROM users'); //grab all rows and all fields from the users table
while($row = mysql_fetch_array($result)){
echo $row['id'];
echo $row['name'];
echo $row['address'];
}

If you use mysql_fetch_row() it's the index of the columns/fields. The index of the columns/fields is either left to right (like when viewing a table in phpMyAdmin) or top to bottom (like in the Structure view of phpMyAdmin):

Code:

$result = mysql_query('SELECT * FROM users'); //grab all rows and all fields from the users table
while($row = mysql_fetch_row($result)){
echo $row[0]; //id
echo $row[1]; //name
echo $row[2]; //address
}

Or in your query you could pick and choose which columns/fields you want and what order they should be in:

Code:

$result = mysql_query('SELECT address, id FROM users'); //grab all the rows and return the columns address and id only and in that order
while($row = mysql_fetch_row($result)){
echo $row[0]; //address
echo $row[1]; //id
}

Hope that helps.

LotsOfZeros · Mar 30, 2011

Ah - I was over complicating it. Thank you for clarifying.

argh01 · Apr 7, 2011

LotsOfZeros said:
Thanks to all the participation on this thread, here's the boobs:

confirmed. Those are, in fact, boobs.

ikonic · Apr 9, 2011

With MySql you can perform checks during the insert without all the PHP checking BS (unless its something complex)

Code:

insert into test (a, b) values(1, IF(LENGTH('$variable')=0,NULL,'$variable'))

In this example, the query checks if the length of the string $variable = 0 and if so it inserts a null into the field instead.

Essentially the clause acts as a ternary operator during the insert.

Search

Search

PHP fgetcsv: empty strings vs NULL

LotsOfZeros

^^^ Bi-Winning ^^^

lschmidt

New member

LotsOfZeros

^^^ Bi-Winning ^^^

lschmidt

New member

LotsOfZeros

^^^ Bi-Winning ^^^

Rage9

Banned

lschmidt

New member

LotsOfZeros

^^^ Bi-Winning ^^^

Rage9

Banned

LotsOfZeros

^^^ Bi-Winning ^^^

argh01

Little Member

ikonic

New member