[SOLVED] Enter NULL if field is empty

[cheeseus]

Hello,

 

I have a script where I add news to a database. I am adding a new field for an image for the respective article, but I need the script to check if there is a value for the image or not, and if there isn't, I want it to enter NULL in the DB.

I have this:

if (isset($_POST['art_image']))
     		{
			$art_image = $_POST['art_image'];
     		}
		else
    		{
			$art_image = NULL;
     		}

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue','$art_image','$art_text')");

 

but it always adds the contents of the field, which is value="features/" indicating the directory in which the image is, if any. I tried removing the folder name ("features/") but it still leaves the cell empty instead of writing NULL in it. Can you help please?

[per1os]

The table structure for that column, does it allow nulls?

[wildteen88]

Your code won't write the word NULL into the database table as the art_image variable contains a null value and not the string NULL. You will want to enclose the word NULL within quotes for the art_image variable to hold the world NULL and not the value NULL. If you set the variable as NULL without quotes PHP will store a blank value and so when your code runs it wont write null to the database.

[cheeseus]

I tried writing

$art_image = 'NULL';

but when I ran the code, it still inserted a blank into the cell, instead of NULL

[per1os]

I think this is what wildteen means

 

if (isset($_POST['art_image']))
     		{
			$art_image = "'" . $_POST['art_image'] . "'";
     		}
		else
    		{
			$art_image = NULL;
     		}

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue'," . $art_image . ",'$art_text')");

 

See if that works.

[akitchin]

you'll usually want to use the empty() function to check for missing form fields, as opposed to isset() - if it's a text input, the variable $_POST['inputname'] will always be set, but it will be empty.  furthermore, you need the NULL to be a string as wildteen said but you still need the single quotes as frost has shown:

 

if (!empty($_POST['art_image']))
     		{
			$art_image = "'" . $_POST['art_image'] . "'";
     		}
		else
    		{
			$art_image = 'NULL';
     		}

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue'," . $art_image . ",'$art_text')");

 

this means it will insert either 'input_value' or NULL for that field (you don't want single quotes around NULL in the query itself, as MySQL will interpret THAT NULL as a string), which is as it should be.  give that a whirl.

[cheeseus]

Thanks but I'm afraid it still doesn't work.

For what frost110 suggested, it added the single quote (') in the cell where art_image should be.

Then changed to "!empty" and when I ran the script it entered the word NULL in the DB, the only difference being that it wasn't in italics NULL as my PhpMyAdmin displays it. When I opened the page where I read the article, I got the "missing image" X instead of nothing, that is, it understands this NULL as an image name, not as the NULL I need...

[akitchin]

try the following, and report back:

 

change your mysql_query() line to simply assigning that query to a $query variable (ie. $query = "INSERT ...", and dumping all your variables:

 

$query = "INSERT ..";

echo 'POST art image: ';
var_dump($_POST['art_image']);
echo '<br />art image: ';
var_dump($art_image);
echo '<br />query: ';
var_dump($query);
exit;

 

let's see what you've actually got, and what you're actually running as a query.

[cheeseus]

hm, this is what I get:

 

POST art_image: string(0) ""

art image: NULL

query: NULL

 

I hope it means something to you as it means nothing to me 

[akitchin]

i have a feeling you've misplaced the code.  essentially it's telling you that $art_image is uninitialized (as well as $query), and that $_POST['art_image'] came through empty (ie. no input in the field).  try:

 

if (!empty($_POST['art_image']))
{
$art_image = "'" . $_POST['art_image'] . "'";
}
else
{
$art_image = 'NULL';
}

//run the query which adds the data gathered from the form into the database
$query = "INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue'," . $art_image . ",'$art_text')";

echo 'POST art image: ';
var_dump($_POST['art_image']);
echo '<br />art image: ';
var_dump($art_image);
echo '<br />query: ';
var_dump($query);
exit;

[nadeemshafi9]

why dont you set a default value in the feild in the databse table with value the word null

[cheeseus]

akitchin: this is what I got this time:

POST art image: string(0) ""

art image: string(4) "NULL"

query: string(181) "INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text) VALUES ('blah blah','Front Page', 'NULL', 'July 2007',NULL,'gagagaggagaga')"

 

nadeemshafi9: I am pretty new to PHP and I don't know how to do this (why dont you set a default value in the feild in the databse table with value the word null)

[akitchin]

that should insert the row with a NULL value, as needed.  note that category won't be NULL properly - it will be the string 'NULL', as opposed to having no actual value.

 

try running $query and seeing what you get.  it should register art_image as a NULL value.

[cheeseus]

Can't seem to make this work...

Here is the full code:

if($submit)
  {
      $art_title = $_POST['art_title'];
  $section = $_POST['section'];
  $category = $_POST['category'];
  $issue = $_POST['issue'];
  $art_image = $_POST['art_image'];
      $art_text = $_POST['art_text'];
  
      if(!$art_title)
  {
        echo "<br>Error: News title is a required field. Please fill it.";
        exit();
      }
  if(!$issue)
  {
        echo "<br>Error: Please fill ISSUE!";
        exit();
      }
  if(!$art_text)
  {
        echo "<br>Error: Well, you need a text for the news, don't you? Please fill it.";
        exit();
      }
  if (!empty($_POST['art_image']))
      {
	$art_image = "'" . $_POST['art_image'] . "'";
      }
  else
      {
    $art_image = NULL;
      }

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue','$art_image','$art_text')");
          //print success message.
          echo "<b>Thank you! News added Successfully!<br>You'll be redirected to ONLINE_ADD in a second.";
          echo "<meta http-equiv=Refresh content=1;url=add_online.php>";
  }//end of if($submit).
  // If the form has not been submitted, display it!
else
  {//begin of else

 

When I put NULL in quotes like ('NULL') it inserts the word NULL, which is no good. When it's without '' it inserts nothing, which is still no good

[per1os]

Thanks but I'm afraid it still doesn't work.

For what frost110 suggested, it added the single quote (') in the cell where art_image should be.

Then changed to "!empty" and when I ran the script it entered the word NULL in the DB, the only difference being that it wasn't in italics NULL as my PhpMyAdmin displays it. When I opened the page where I read the article, I got the "missing image" X instead of nothing, that is, it understands this NULL as an image name, not as the NULL I need...

 

Does your DB Column type allow nulls?

[cheeseus]

I think it does because I have other rows where I have the NULL value -- I used to enter the image url manually through the phpMyAdmin before.

[per1os]

if (!empty($_POST['art_image']))
     		{
			$art_image = "'" . $_POST['art_image'] . "'";
     		}
		else
    		{
			$art_image = 'NULL';
     		}

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue'," . $art_image . ",'$art_text')");

 

You have tried the above code exactly how it is. If so give this a shot

 

if (!empty($_POST['art_image']))
     		{
			$art_image = "'" . $_POST['art_image'] . "'";
     		}
		else
    		{
			$art_image = 'NULL';
     		}

         //run the query which adds the data gathered from the form into the database
        $result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue',NULL,'$art_text')");

 

Just for a testing purpose and see what comes of it.

[akitchin]

you're not looking closely enough at our examples.  use the EXACT version i last posted minus the var dumping, and you'll find that it works.  there are some subtle quote differences which you haven't updated into your current code.

 

your current code uses PHP to set $art_image to NULL.  when you try to put that into the MySQL query as a string, PHP just interpolates it with a blank value, not NULL.  in order to get MySQL to detect it as a NULL-value, you have to put it in as NULL without single quotes.  that is why we're setting $_POST['art_image'] with single quotes, and omitting them from the query - that ensures that if it IS a string, it will go in as ,'String', but if it is supposed to be a NULL, it's inserted into the query as ,NULL,

[cheeseus]

Thank you! IT WORKS!

I had really missed the

$result = mysql_query("INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text)
                       VALUES ('$art_title','$section', '$category', '$issue'," . $art_image . ",'$art_text')");

 

part.

I must try and adapt this to the CATEGORY field now, which is a <select>.

[akitchin]

the category is a relatively easy fix.  set its default (pre-selected) value to "NULL", and add single quotes around it if it is a real category, as opposed to the default:

 

if ($_POST['category'] != 'NULL')
{
  $category = "'".$_POST['category']."'";
}

$query = "INSERT blah blah... VALUES ('$art_title','$section', $category, '$issue', $art_image,'$art_text')"

 

essentially you're just taking care of putting single quotes where they should or shouldn't be dynamically, rather than assuming it'll always need them.  this is also a technique you'd have to use if using a type-anal database server, where integers CANNOT have single quotes around them when being inserted (as otherwise they are interpreted as a string and toss out a type mismatch error).

[cheeseus]

Thank you, I will try later as it is almost midnight here

[nadeemshafi9]

akitchin: this is what I got this time:

POST art image: string(0) ""

art image: string(4) "NULL"

query: string(181) "INSERT INTO az_articles (art_title, section, category, issue, art_image, art_text) VALUES ('blah blah','Front Page', 'NULL', 'July 2007',NULL,'gagagaggagaga')"

 

nadeemshafi9: I am pretty new to PHP and I don't know how to do this (why dont you set a default value in the feild in the databse table with value the word null)

 

open phpmyadmin and go to table design