insert null value

[yebaws]

Hello,

I have a page for inserting data into an sql database with an html form. It looks a bit like this:

$SQL = " INSERT INTO listings ";
$SQL = $SQL . " (paid, business_category, businesscategory, business_name,....
$SQL = $SQL . " ('$paid','$business_category','$businesscategory','$business_name',...

Then I define the values in the form and send the values to the database when it is submitted.

Works fine except that some fields are set in the database to default to NULL and when I use this form to add data to the database they lose their NULL status, even if I don't enter any data in the input field of the form. I want them to stay NULL if I don't enter any data. I realise I can use UPDATE `business_category` SET `grading2` = NULL ... but there's no simple way to do this from a form only when the input field is blank. Is there another way to do it?

Thanks

 

[jpadie]

sure. just profile the variables like so

$variables = array('paid', 'business_category' ...

then use this code to construct your query

$sql = "Insert into $tablename ";
foreach ($variables as $var){
 ${$var} = empty($_POST[$var']) ? NULL : mysql_real_escape_string($_POST[$var]));
 $bits[] = "$var = '${$var}'";
}
$sql = $sql . implode (',', $bits);

or something like that anyway (not tested ...)

 

[yebaws]

forgive me but I'm on steep learning curve at the moment.

Could you explain exactly how you would re-write this bit of code please:

$SQL = " INSERT INTO listings ";
$SQL = $SQL . " (paid, business_category, businesscategory, business_name) VALUES";
$SQL = $SQL . " ('$paid','$business_category','$businesscategory','$business_name')";
$result = mysql_db_query($db,"$SQL",$cid);


It's probably more long winded than it needs to be I know, but as I said, I'm learning...
I tried integrating your code, but there seem to be too many )'s
Thanks again

 

[jpadie]

replace one with the other

 

[yebaws]

ok, I am trying....

$sql = "Insert into $tablename ";
foreach ($variables as $var){
${$var} = empty($_POST[$var']) ? NULL : mysql_real_escape_string($_POST[$var]));
$bits[] = "$var = '${$var}'";
}
$sql = $sql . implode (',', $bits);

this code isn't right 'though - has at least one syntax error, but I can't fix it...

 

[yebaws]

insert one with the other" doesn't work.

anybody else able to help it would be appreciated

 

[jpadie]

you should be able to undertake basic debugging before posting here.

In my post above I advise you to profile your variables. this is a prerequisite to using the looping structure that i provided.

<?php
$tablename = 'myTable';
$vars = array('apple', 'banana', 'carrot', 'dandelion', 'elderberry');


$sql = "Insert into $tablename SET ";
foreach ($vars as $var){
 ${$var} = empty($_POST[$var]) ? 'NULL' : "'".mysql_real_escape_string($_POST[$var])."'";
 $bits[] = "$var = ${$var}";
}
$sql = $sql . implode (',', $bits);

echo $sql;
?>

this outputs

Insert into myTable apple = NULL,banana = NULL,carrot = NULL,dandelion = NULL,elderberry = NULL

 

[jpadie]

this outputs

Insert into myTable [red]SET[/red] apple = NULL,banana = NULL,carrot = NULL,dandelion = NULL,elderberry = NULL

 

[yebaws]

Yes, I can do basic de-bugging, but when the code is new to me anyway it's more helpful to have code without any syntax errors.

Thank you for your help. This does the job perfectly. Not being pedantic, but just for the sake of anybody else reading it who is slightly challenged like me:

foreach ($vars as $var){

should read:

foreach ($variable as $var){