PHP, MySQL and null?

Okay, how do I make PHP 4.3.2. and MySQL 4.0.13 to agree on what null means?

I'm trying to get a form to add a new record into my database where one of
the
Fields might be null and for said field to actually be a null record once
into
The database as well...

This is the script in question (a simple "Let the user add links to the
site" thingy).
It may not be the most elegant way of solving things...($name,$url and
$comment are
Variables found in $_POST if it's not obvious btw).

<?php

extract ($_POST, EXTR_PREFIX_SAME,"ww");


$fail=0;
$fail2=0;

if ($name==""):
echo "I need a name for the new site!";
return;
endif;

if ($url==""):
echo "The site needs an URL!";
return;
endif;

if ($comment==""):
$comment=null;
endif;

$link = mysql_connect("localhost") or die("Could not connect : " .
mysql_error());
mysql_select_db("intranet") or die("Could not select database");


$query = "SELECT id from site where name=\"$name\"";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_row($result)) {
$fail=$row[0];
}

if ($fail>0):
echo "Sorry, that name already exist!";
return;
endif;

mysql_free_result($result);

$query = "SELECT id from site where url=\"$url\"";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_row($result)) {
$fail2=$row[0];
}

if ($fail2>0):
echo "Sorry, that URL already exist!";
return;
endif;

mysql_free_result($result);



$name2=mysql_escape_string($name);
$url2=mysql_escape_string($url);
$comment2=mysql_escape_string($comment);

$query = "INSERT into site(id,name,url,comment) VALUES
(null,'$name2','$url2','$comment2')";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

mysql_close($link);

echo "Sitename=$name";
echo "<br>Site's URL=$url";

if (is_null($comment)):
echo "<br>No comment";
else:
echo "<br>Comment=$comment";
endif;

echo "<p>Added!";

?>

If I have null direcly in the query string (like this part...VALUES
(null,'test'...) it works
Like excepted (the id field is an auto_increment one so the id is
automatically changed to
The next available id-number).

However, what fails is when I set a variable to be null and adds that to the
query. The
Record is inserted into the database alright but the comment-field (in this
case) doesn't
Get filled in with MySQL's the null-value. It gets filled with a zero-length
string instead
Which screws things up, since I no longer can use constructs like "select
id,name from site where comment is null;"

Not a problem on this specific table but on another table where I'm about to
write the insert-script it is a
Huge problem!

Many thanks if someone has a good solution for this!

---
"... and pray that there's intelligent life somewhere out in space, because
there's bugger all down here on Earth!" - Eric Idle - the Meaning of Life
---
/Jonas

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 2003-07-14

Have you tried inserting it without including the id field in the insert
statement? I have several tables with auto increments and I never even
bother putting that field in the insert statement.

David


-----Original Message-----
From: Jonas Thorell [mailto:jonasth@bahnhof.se]
Sent: Sunday, July 27, 2003 9:44 AM
To: php-general@lists.php.net
Subject: [php] PHP, MySQL and null?


Okay, how do I make PHP 4.3.2. and MySQL 4.0.13 to agree on what null means?

I'm trying to get a form to add a new record into my database where one of
the
Fields might be null and for said field to actually be a null record once
into
The database as well...

This is the script in question (a simple "Let the user add links to the
site" thingy).
It may not be the most elegant way of solving things...($name,$url and
$comment are
Variables found in $_POST if it's not obvious btw).

<?php

extract ($_POST, EXTR_PREFIX_SAME,"ww");


$fail=0;
$fail2=0;

if ($name==""):
echo "I need a name for the new site!";
return;
endif;

if ($url==""):
echo "The site needs an URL!";
return;
endif;

if ($comment==""):
$comment=null;
endif;

$link = mysql_connect("localhost") or die("Could not connect : " .
mysql_error());
mysql_select_db("intranet") or die("Could not select database");


$query = "SELECT id from site where name=\"$name\"";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_row($result)) {
$fail=$row[0];
}

if ($fail>0):
echo "Sorry, that name already exist!";
return;
endif;

mysql_free_result($result);

$query = "SELECT id from site where url=\"$url\"";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_row($result)) {
$fail2=$row[0];
}

if ($fail2>0):
echo "Sorry, that URL already exist!";
return;
endif;

mysql_free_result($result);



$name2=mysql_escape_string($name);
$url2=mysql_escape_string($url);
$comment2=mysql_escape_string($comment);

$query = "INSERT into site(id,name,url,comment) VALUES
(null,'$name2','$url2','$comment2')";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

mysql_close($link);

echo "Sitename=$name";
echo "<br>Site's URL=$url";

if (is_null($comment)):
echo "<br>No comment";
else:
echo "<br>Comment=$comment";
endif;

echo "<p>Added!";

?>

If I have null direcly in the query string (like this part...VALUES
(null,'test'...) it works
Like excepted (the id field is an auto_increment one so the id is
automatically changed to
The next available id-number).

However, what fails is when I set a variable to be null and adds that to the
query. The
Record is inserted into the database alright but the comment-field (in this
case) doesn't
Get filled in with MySQL's the null-value. It gets filled with a zero-length
string instead
Which screws things up, since I no longer can use constructs like "select
id,name from site where comment is null;"

Not a problem on this specific table but on another table where I'm about to
write the insert-script it is a
Huge problem!

Many thanks if someone has a good solution for this!

---
"... and pray that there's intelligent life somewhere out in space, because
there's bugger all down here on Earth!" - Eric Idle - the Meaning of Life
---
/Jonas

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 2003-07-14



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

>Have you tried inserting it without including the id field in the insert
statement? I have several tables with auto increments and I never even
bother
>putting that field in the insert statement.

Well, no. But that's not where the problem is. If I was unclear - the
id-field work as it should. It's when I try to use
A variable in the query-string the problem pops up.

IOW - INSERT INTO SITE (id,name,url,comment) VALUES
(null,'testname','testurl','testcomment');

Works

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'$name','$url','$comment');

Does not when I've set $comment=null;

If every field has a proper value (when everything had been filled in on the
form),
Everything works as expected.

/Jonas

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 2003-07-14

Sorry, wrote wrong here so one more try.

Well, no. But that's not where the problem is. If I was unclear - the
id-field work as it should. It's when I try to use A variable in the
query-string the problem pops up.

IOW - INSERT INTO SITE (id,name,url,comment) VALUES
(null,'testname','testurl',null)

Works

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'$name','$url','$comment')

Does not when I've set $comment=null;

If every field has a proper value (when everything had been filled in on the
form), Everything works as expected.

/Jonas

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 2003-07-14

* Thus wrote Jonas Thorell (jonasth@bahnhof.se):
> Sorry, wrote wrong here so one more try.
>
> Well, no. But that's not where the problem is. If I was unclear - the
> id-field work as it should. It's when I try to use A variable in the
> query-string the problem pops up.
>
> IOW - INSERT INTO SITE (id,name,url,comment) VALUES
> (null,'testname','testurl',null)
>
> Works
>
> INSERT INTO SITE (id,name,url,comment) VALUES
> (null,'$name','$url','$comment')
>
> Does not when I've set $comment=null;
>
> If every field has a proper value (when everything had been filled in on the
> form), Everything works as expected.

The problem is when the string gets translated (variables expanded)
and sent to mysql is that the string looks like this:

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'Name of url','http://blah/','')

So when the database sees the query it thinks you want to put and
empty sting there, not null. To achieve what you want to do you
have to make sure that the word, NULL, without quotes around it,
gets sent to the databaase.

$comment2 = trim($comment); // also clear out space just in case
if (strlen($comment2)) {
// escape the string
$comment2 = mysql_escape_string($comment2);

// wrap the string in single quotes for the db
$comment2 = "'$comment2'";
} else {
// send the keyword NULL to the database
$comment2 = "null";
}

Now in your SQL statement you would put the $comment2 variable in
the proper place without the single quotes:

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'$name','$url',$comment2)

Now your sql statment will be (if comment was left blank:)

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'Name of url','http://blah/',null)

or if it had a value:

INSERT INTO SITE (id,name,url,comment) VALUES
(null,'Name of url','http://google.com/','This should be first')


HTH,

Curt
--
"I used to think I was indecisive, but now I'm not so sure."