Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
3

I'm struggling with some PHP/MySQL code. I am reading from 1 table, changing some fields then writing to another table, nothing happens if inserting and one of the array values is null when I would like it to insert null in the database (null values are allowed for the field). It looks a bit like this:

$results = mysql_query("select * from mytable");
while ($row = mysql_fetch_assoc($results) {
    mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', {$row['null_field']});
}

Not every row has a null value and in my query there are more fields and 2 columns which may or may not be null

share|improve this question

2 Answers

up vote 10 down vote accepted

This is one example where using prepared statements really saves you some trouble.

In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', NULL);

However, if you want to insert a value in that field, you must now branch your code to add the single quotes:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', 'String Value');

Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.

share|improve this answer
1  
Brilliant, thanks i'm now a mysqli convert! – MattP Mar 17 '11 at 10:40
2  
@MattP: Note that PDO has the same behavior. Compare the API for each before you settle on one (PDO is easier to use and largely DB agnostic, mysqli provides more direct access to the MySQL API). – outis Jan 17 '12 at 20:44
up vote 3 down vote

I think you need quotes around your {$row['null_field']}, so '{$row['null_field']}'

If you don't have the quotes, you'll occasionally end up with an insert statement that looks like this: insert into table2 (f1, f2) values ('val1',) which is a syntax error.

If that is a numeric field, you will have to do some testing above it, and if there is no value in null_field, explicitly set it to null..

share|improve this answer
Thanks, i'll have to add some additional testing and explicitly set the nulls, it is much more logical in the application that the fields are null rather than empty strings. – MattP Mar 16 '11 at 18:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.