translating php variable with NULL value to mySQL table

[rynato]

pardon me if this question has been answered elsewhere before (it
probably has been), but I cannot find an answer to this anywhere,
including using the google:

Consider the following, given a table where col1 is 'varchar 255',
col2 is 'int' and col3 is 'varchar 255'
============
$myvar1 = 'foo';
$myvar2 = null;
$myvar3 = null;

$query = "INSERT INTO mytable (col1,col2,col3) VALUES
('$myvar1','$myvar2','$myvar3')";
$result = $conn->executeQuery($query);
============

when the query is executed, my table will hold the following values
for the row I just created:

col1 = foo
col2 = 0
col3 = (this field will be blank; not NULL)

Is there any elegant, simple way to translate a php variable which
holds a NULL value, to a mySQL NULL value?

The only way I was able to figure it out is rather kludge-y. Again,
using the above example:
============
$query = "INSERT INTO mytable (col1) VALUES ('$myvar1');
$result = $conn->executeQuery($query);
if (is_null($myvar2)) $col2set = "col2=null"
else $col2set = "col2='$myvar2'";
$query = "UPDATE mytable SET " . $col2set . " WHERE
rowid='$thisrowid'";
$result = $conn->executeQuery($query);
if (is_null($myvar3)) $col3set = "col3=null"
else $col3set = "col3='$myvar3'";
$query = "UPDATE mytable SET " . $col3set . " WHERE
rowid='$thisrowid'";
$result = $conn->executeQuery($query);
============
now, that is cumbersome as hell if one has a LOT of fields which
potentially have a null value. Of course one could write PHP code
which would translate (blank) or 0 to null but that is also
cumbersome, possibly a pain in the ass to troubleshoot if the code is
not working as desired, and generally not good procedure.

thx in adv

[Iván Sánchez Ortega]

rynato wrote:

> Is there any elegant, simple way to translate a php variable which
> holds a NULL value, to a mySQL NULL value?

Variable bindings. Have a look at the PHP manual, the section about PDO and
binding variables into a prepared query.

Cheers,
--
----------------------------------
Iván Sánchez Ortega -ivan-algarroba-sanchezortega-punto-es-

MSN:i_eat_s_p_a_m...@hotmail.com
Jabber:ivans...@jabber.org ; ivans...@kdetalk.net

[Jerry Stuckle]

Answered in alt.php.

Please do not multipost. Crosspost instead

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[FutureShock]

Ok here is a real stupid question.

What is the difference between cross and multi posting and how do you
know when you are doing either?

Thanks
Scotty

[Iván Sánchez Ortega]

FutureShock wrote:

> Ok here is a real stupid question.
>
> What is the difference between cross and multi posting and how do you
> know when you are doing either?

STFW: http://en.wikipedia.org/wiki/Crosspost

[FutureShock]

Iván Sánchez Ortega wrote:
> FutureShock wrote:
>
>> Ok here is a real stupid question.
>>
>> What is the difference between cross and multi posting and how do you
>> know when you are doing either?
>
> STFW: http://en.wikipedia.org/wiki/Crosspost
>

Thanks Ivan

But really, no need to get nasty about it, it was a simple friendly spur
of the moment question to Jerry.

[Jerry Stuckle]

Scotty,

Think of it like email. Crossposting is like sending one email to a
list of people. Multiposting is like sending the same email
individually to multiple people.

The advantage of crossposting is that the responses are also crossposted
(by default, anyway), so all groups get all responses.

[FutureShock]

Ok that makes more sense.

Thanks
Scotty

[Alex Weber]

and just to actually answer the question pass just pass "null" to sql,
without the quotation marks:

============
$myvar1 = 'foo';
$myvar2 = null;
$myvar3 = null;

$query = "INSERT INTO mytable (col1,col2,col3) VALUES

('$myvar1',null,null)";
============