[SOLVED] SQL syntax error with PHP variables

[ShootingBlanks]

I'm getting the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'div, writer, sme, progress, status, due) VALUES (Test Project,test description,t' at line 1

 

Based on this code:

$insertSQL = sprintf("INSERT INTO projects (proj_name, proj_desc, sponsor, priority, div, writer, sme, progress, status, due)
VALUES (".$_POST['proj_name'].",".$_POST['desc'].",".$_POST['sponsor'].",".$_POST['priority'].",".$_POST['div'].","
.$_POST['writer'].",".$_POST['sme'].",".$progress.",'Open',".$mysqlFormat.'"');

 

Regarding that code, this is the values of the variable noted in there:

 

$progress = "Project opened."

 

$mysqlFormat = 2007-12-27

 

The $mysqlFormat is being put into the "due" column that is listed as a "DATE" type in my table.  If you need other info to help troubleshoot, please let me know.  Thanks!...

 

[revraz]

Priority is probably a reserved MySql arguement/operator.

[revraz]

Opps, I was wrong, DIV is

 

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[kellz]

it starts with a (" and ends as '"');  ???

 

shouldnt it end like "); ?

[ShootingBlanks]

Opps, I was wrong, DIV is

 

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

 

Okay - I kept all the code the same, except I renamed "div" to "division" (in both the code and my database table), and now I am getting THIS error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'project,test description,test sponsor,Medium,ALL,,,Project opened.,'Open',2007-1' at line 1

 

The "writer" and "sme" fields were left blank in my form (so the $_POST variables), which is why there are just blank commas there after "ALL" in the error above, but in my database table setup, I have them set to "NULL", so if they're empty, then doesn't that mean that it is okay to have nothing in them?  Or am I wrong about that?

 

Now what?  Thanks for that tip on "div", by the way!!!

 

 

[sasa]

you must add sigle qoute around string type data

Try

$insertSQL = sprintf("INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
VALUES ('".$_POST['proj_name']."', '".$_POST['desc']."', '".$_POST['sponsor']."', '".$_POST['priority']."', '".$_POST['div']."', '"
.$_POST['writer']."', '".$_POST['sme']."', '".$progress."', 'Open', '".$mysqlFormat."'");

[rajivgonsalves]

why not just try 

 

$insertSQL = "INSERT INTO projects (proj_name, proj_desc, sponsor, priority, div, writer, sme, progress, status, due) VALUES ('{$_POST['proj_name']}','{$_POST['desc']}','{$_POST['sponsor']}','{$_POST['priority']}','{$_POST['div']}','{$_POST['writer']}','{$_POST['sme']}','$progress','Open','$mysqlFormat')";

[kenrbnsn]

String values in a MySQL statement need to be quoted with single quotes. You should also process any user input through, at least, the mysql_real_escape_string() function:

<?php
$tmpq = array();
foreach ($_POST as $k => $v)
    switch ($k) {
        case 'proj_name':
        case 'desc':
        case 'sponsor':
        case 'priority':
        case 'div':
        case 'writer':
        case 'sme':
           $tmpq[] = "'" . mysql_real_escape_string($v) . "'";
    }
$insertSQL = "INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
VALUES (" . impode(', '. $tmpq) .  ",'" . $progress . "', 'Open', '".$mysqlFormat . "')";
?>

 

Ken

[ShootingBlanks]

you must add sigle qoute around string type data

Try

$insertSQL = sprintf("INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
VALUES ('".$_POST['proj_name']."', '".$_POST['desc']."', '".$_POST['sponsor']."', '".$_POST['priority']."', '".$_POST['div']."', '"
.$_POST['writer']."', '".$_POST['sme']."', '".$progress."', 'Open', '".$mysqlFormat."'");

 

Getting closer!...

 

...after adding the single quotes around $progress, I get the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'project,test description - empty,test sponsor,Medium,W&C,,,'Project opened.','Op' at line 1

 

If I fill in those "NULL" fields ("writer" and "sme"), then I get this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'project,test description - filled in,test sponsor,Medium,W&C,sample writer,sampl' at line 1

 

Any more ideas???

???

[ShootingBlanks]

String values in a MySQL statement need to be quoted with single quotes. You should also process any user input through, at least, the mysql_real_escape_string() function:

<?php
$tmpq = array();
foreach ($_POST as $k => $v)
    switch ($k) {
        case 'proj_name':
        case 'desc':
        case 'sponsor':
        case 'priority':
        case 'div':
        case 'writer':
        case 'sme':
           $tmpq[] = "'" . mysql_real_escape_string($v) . "'";
    }
$insertSQL = "INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
VALUES (" . impode(', '. $tmpq) .  ",'" . $progress . "', 'Open', '".$mysqlFormat . "')";
?>

 

Ken

 

I copied that code exactly above (except I changed "impode" to "implode", assuming a spelling error), and now I get the following errors:

 

Warning: implode() [function.implode]: Argument to implode must be an array. in C:\htdocs\_PHP-SITES\ProjectBoard\admin\addProject.php on line 161

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Project opened.', 'Open', '2007-12-27')' at line 2

 

Is this a step forward or backwards?

 

 

[kenrbnsn]

On the line where you do the Mysql statement add an "or die" clause and echo your query:

<?php
$rs = mysql_query($insertSQL) or die("Error with query: $insertSQL<br>" . mysql_error();
?>

 

This should give you a better understanding of what's going on.

 

Ken

[KevinM1]

Quick question: why are you using sprintf() with your query?

[kenrbnsn]

Sorry

 

This

<?php
impode(', '. $tmpq)
?>

 

should be

<?php
implode(', ', $tmpq)
?>

 

(I can't type today )

 

Ken

[ShootingBlanks]

On the line where you do the Mysql statement add an "or die" clause and echo your query:

<?php
$rs = mysql_query($insertSQL) or die("Error with query: $insertSQL<br>" . mysql_error();
?>

 

This should give you a better understanding of what's going on.

 

Ken

 

Pardon my newbie ignorance, but what do I echo to get the output of the query?  is it just:

echo '<p>'.$insertSQL.</p>;

in the body of the HTML document?  if so, wouldn't I still get the error page because the query will try to execute, so then I wouldn't ever see the echo?

 

[kenrbnsn]

The "or die" clause does the echoing when there is an error.

 

Ken

[ShootingBlanks]

I dunno, then, cuz I had an "or die" clause there the whole time.  Here's what my original statement said:

$Result1 = mysql_query($insertSQL, $ProjectBoard) or die(mysql_error());

 

 

[kenrbnsn]

Change it to:

<?php
$Result1 = mysql_query($insertSQL, $ProjectBoard) or die("Error with query: $insertSQL<br>" . mysql_error());
?>

 

And your formatted query will display before the error message.

 

Ken

[ShootingBlanks]

Change it to:

<?php
$Result1 = mysql_query($insertSQL, $ProjectBoard) or die("Error with query: $insertSQL<br>" . mysql_error());
?>

 

And your formatted query will display before the error message.

Okay, cool.  Here's the current error (and I did correct that "implode" typo):

 

Warning: implode() [function.implode]: Argument to implode must be an array. in C:\htdocs\_PHP-SITES\ProjectBoard\admin\addProject.php on line 161

Error with query: INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due) VALUES (,'Project opened.', 'Open', '2007-12-27')

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Project opened.', 'Open', '2007-12-27')' at line 2

[kenrbnsn]

Please repost the code you're using. It still looks like the implode is having a problem.

 

Ken

[ShootingBlanks]

Here you go.  Thanks so much again for all the help - it is MUCH appreciated!!! 

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $tmpq = array();
foreach ($_POST as $k => $v)
    switch ($k) {
        case 'proj_name':
        case 'desc':
        case 'sponsor':
        case 'priority':
        case 'div':
        case 'writer':
        case 'sme':
           $tmpq[] = "'" . mysql_real_escape_string($v) . "'";
    }
$insertSQL = "INSERT INTO projects (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
VALUES (" . implode(', '. $tmpq) .  ",'" . $progress . "', 'Open', '".$mysqlFormat . "')";

[kenrbnsn]

You didn't replace

<?php
implode(', '. $tmpq)
?>

with

<?php
implode(', ', $tmpq)
?>

 

Ken

[ShootingBlanks]

I think that worked!

 

I'm getting another error, but on a different part of the code.  Let me check into that.  Thanks!

 

[ShootingBlanks]

Would you be able to explain to me WHY that worked, as that may help to solve my next error that I'm getting?  I'm pretty new at all this, so I didn't really understand what was going on with all that code involving the $tmpq array, the foreach/switch statement, and the implode?  Thanks!