On Fri, 2003-08-15 at 17:45, Michael Ryan Byrd wrote:
> So I want to insert a NULL value into a column of a MySQL table from my PHP
> script:
>
> Do I
>
> $myVariable=NULL;
>
> or
>
> $myVariable='';
>
> (or either?)
>
> $query = "INSERT INTO MYTABLE (MYVARIABLE) VALUES('$myVariable')";
> $result=mysql_query($query) or die ("oops!");
Using variable expansion within the string, you'd need to do:
$myVariable = 'NULL';
$query = "INSERT INTO MYTABLE (MYVARIABLE) VALUES ($myVariable)";
...
note:
* there are quotes around NULL in the assignment ($myVariable contains
the string constant 'NULL', not NULL)
* there are no single quotes around $myVariable in $query
The end result is that $query becomes:
INSERT INTO MYTABLE (MYVARIABLE) VALUES (NULL)
as Adrian mentioned.
This approach however is quite inflexible. What if you want to put a
non-NULL value in instead later (but still using $myVariable of course)?
You can't just change the value of $myVariable, unless you explicitly
wrap it in quotes in $myVariable. eg:
$myVariable = "'data'"
Not cool. Then further suppose that the data had single quotes within
it:
$myVariable = "'This isn't right'"
You have to escape them manually:
$myVariable = "'This won\'t break'"
There, so that will work without needing to change $query. But that's
all rather tedious, especially if $myVariable is actually some incoming
input (which is likely).
The better approach: placeholders. I don't know the MySQL/PHP API, but I
assume the following lesson is applicable and I'll demonstrate using the
Ruby DBI.
query = "INSERT INTO MYTABLE (MYVARIABLE) VALUES (?)"
myVariable = nil
dbh.do(query, myVariable)
myVariable = "This won't break"
dbh.do(query, myVariable)
Both those inserts will work. The Mysql DBD underlying the DBI will
translate nil into "NULL" and "This won't break" into "'This won\'t
break'" for you (well, it doesn't do exactly that, but it makes it work
as if it had). It also does anything else that needs to be done that you
might have overlooked trying to do it yourself. Placeholders are a Good
Thing (TM).
I rant about this because I've been bitten by the previous code breaking
when user's input apostrophes, etc. in too many cases here at work with
Perl/MySQL before I learned about placeholders. They're very nice and
should be used whenever possible.
Jacob Fugal
____________________
BYU Unix Users Group
http://uug.byu.edu/
___________________________________________________________________
List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
