insert vs. update problem

Anon

I have a bit of a problem with a survey application I'm setting up.

I am parsing an array of key:value pairs from an html survey form, where some of the values may be null.

Both the key and value for each pair in the array need to be written to the db table even if the value is empty. If it is empty I need to write it to the table as null.

At this point I'm using an INSERT and it works correctly.

The user has the option of returning to the form later to update their answers. When using UPDATE to update their entry in the DB table it writes the empty values as "0" instead of null.

I've included the code below. Can someone please point out where I'm going wrong.

Thanks!!!!

$link = mysql_connect ("localhost", "rhughes", "XXXXXX");
mysql_select_db("CompanyDataTables", $link);

$question_array = $question;

while (list($key, $value) = each($question_array))

{

$result = mysql_query ("SELECT question_id FROM $table WHERE question_id = $key", $link);
if (mysql_num_rows($result) < "1")
{
if ($value == ""){$value = "\N";}
mysql_query ("INSERT INTO $table (company_id,question_id,answer_group,answer_order) VALUES ($company_id,$key,$answer_group,$value)", $link);
print "in - $key - $value<br>";
}

elseif (mysql_num_rows($result) > "0")
{
if ($value == ""){$value = "\N";}
mysql_query ("UPDATE $table SET company_id = '$company_id', question_id = '$key', answer_group = '$answer_group', answer_order = '$value' WHERE question_id = '$key'", $link);
print "up - $key - $value<br>";
}

}

chaostik

Hi,

I'm not 100% sure about this one, but what springs to mind is the fact that you have quoted the update data, but not the inserts. I know in PostgreSQL it would cause a differing results, so it is probably so in MySQL. What are the datatypes for all the columns?? Make sure that any numerical columns are updated without using quotes and that only text/blob etc column types quote the variable.

For example, if company_id and question_id are ints and answer_group and answer_order are text, then do the following:

UPDATE $table SET company_id = $company_id, question_id = $key, answer_group = '$answer_group', answer_order = '$value' WHERE question_id = '$key'

I've a feeling I've been incomprehensible there. Basically, remove as many of the ' ' quotes as possible and then it will interpret the zero as NULL and not as a string zero.

All the best,

Louis

Anon

Louis,

YOU ARE THE MAN!!!!!!!

I can't thank you enough. That was exactly the problem.

It's amazing how easy it is to look over something so simple when you been coding for about 60 straight hours after buying a book and learning php and mySQL in the two days proceeding the coding run!!

Thanks again,

Robert