PHP - MySQL error

[JohnnyLee]

i'm trying to move/migrate old web application for a client of mine to latest versions of MySQL and PHP.

i'm having problems fixing this error:

There was a problem adding the initial timeframe to the database. Please inform the System Administrator. Insert failed

There was a problem logging this transaction. Please print out this page and inform the System Administrator.
Insert failed : INSERT INTO log VALUES( 0, "2012-02-23 16:22:41", "job", , "root", "1005,380,\'open\',\'2012-02-23 16:22:41\',\'root\',\'2012-02-23 16:22:41\',\'root\',\'\',\'lab\',\'\'", "added")

the code behind this is this:

PHP Code:

$insert_t0 = mysql_db_query("databaseName", "INSERT INTO timeframe VALUES (0,$new_int_job_no,\"T0\",1)")
    or print ("<p><font face=\"Verdana, Arial, Helvetica, sans-serif\">There was a problem adding the initial timeframe to the database.  Please inform the System Administrator.  Insert failed\n</font>\n");

  $added_data = "$cust_to_edit,$new_job_no,\'open\',\'$datetimenow\',\'$login\',\'$datetimenow\',\'$login\',\'$jobinit_ponum\',\'lab\',\'\'";

  $query = "INSERT INTO log VALUES(
    0,
    \"$datetimenow\",
    \"job\",
    $new_int_job_no,
    \"$login\",
    \"".$added_data."\",
    \"added\")"; 


i'm guessing this is something to do with the SQL syntax.

any help would be much appreciated
Johnny

[tangoforce]

Why are you using double quotes? - You're getting very confused about their usage and the difference between PHP and mysql's use of quote marks.

This is how you should do it:

PHP Code:

$query = "INSERT INTO log VALUES(
    0,
    '$datetimenow',
    'job')";  //Etc 


Your query is already inside double quotes. Therefore the $Variables will still be replaced with their values inside the single quotes.

For mysql, you always wrap values in single quotes and optionally wrap column names in backticks ` (which you've not specified in your query btw - insert into <table> (`columns`) values ('$Values');

In php anything inside single quotes (unless inside a double quoted string) will be exactly as you see it:

PHP Code:

$Name = 'Joe Bloggs';

//Single quotes:
$Str = 'Name is $Name'; // Name is $Name

$Str = "Name is $Name"; // Name is Joe Bloggs

$Query = "insert into <table> (`column`) values ('$Name')"; //insert into <table> (`column`) values ('Joe Bloggs') 


For more information, see the Quotes tip in my signature.

[Fou-Lu]

What's with all of these escapes? The $query itself needs to be escaped or swapped to use single quotes (on either the fields or the variable itself), but the $added_data is escaping when it should not be. Change the outside " to '. Since these are inserted fields, the location where $added_data is used should not be quoted.

Given the text output you have above, you need to do some verification of your data as well. , in the middle without any data should be using NULL, assuming that it allows null values of course.