Invalid query, syntrax error

[jeff5656]

I get "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 '' at line 23"

 

I cannot find the error for the life of me:

 

<?php
require('secure.php'); 

include "dateheader.php"; 
include "connectdb.php";


$newdate = date("Y-m-d", strtotime($_POST['rcf_date']));

$sql = "UPDATE icu SET
rm_loc = '" . $_POST['rm_loc'] . "',
patient = '" . $_POST['patient'] . "',
mrn = '" . $_POST['mrn'] . "',
age = '" . $_POST['age'] . "',
race = '" . $_POST['race'] . "',
gender = '" . $_POST['gender'] . "',
pod = '" . $_POST['pod'] . "',
rcf_date = '" . $_POST['rcf_date'] . "',
dx = '" . $_POST['dx'] . "',
meds = '" . $_POST['meds'] . "',
pmhx = '" . $_POST['pmhx'] . "',
problist = '" . $_POST['problist'] . "',
anticipate = '" . $_POST['anticipate'] . "',
comments = '" . $_POST['comments'] . "',
code = '" . $_POST['code'] . "',
allergy = '" . $_POST['allergy'] . "',
todo = '" . $_POST['todo'] . "',
signoff_status = '" . $_POST['signoff_status'] . "',
rcf_date2 = '$newdate'


WHERE id_incr = ".$_POST['id_incr']."";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());


?>
<p>
Done
<?php
}
header("Location: displayactive.php");

?>

[ILMV]

Missing ; on this line...

 

   rcf_date2 = '$newdate'

 

Edit:

 

In fact it seems that end section of the SQL is incorrect, should be....

 

$sql = "UPDATE icu SET
   rm_loc = '" . $_POST['rm_loc'] . "',
   patient = '" . $_POST['patient'] . "',
   mrn = '" . $_POST['mrn'] . "',
   age = '" . $_POST['age'] . "',
   race = '" . $_POST['race'] . "',
   gender = '" . $_POST['gender'] . "',
   pod = '" . $_POST['pod'] . "',
   rcf_date = '" . $_POST['rcf_date'] . "',
   dx = '" . $_POST['dx'] . "',
   meds = '" . $_POST['meds'] . "',
   pmhx = '" . $_POST['pmhx'] . "',
   problist = '" . $_POST['problist'] . "',
   anticipate = '" . $_POST['anticipate'] . "',
   comments = '" . $_POST['comments'] . "',
   code = '" . $_POST['code'] . "',
   allergy = '" . $_POST['allergy'] . "',
   todo = '" . $_POST['todo'] . "',
   signoff_status = '" . $_POST['signoff_status'] . "',
   rcf_date2 = '".$newdate."'  WHERE id_incr = ".$_POST['id_incr']."";

[jeff5656]

No I don't think you put a ; at the end of the last one before the WHERE statement.

and the second example you give just puts WHERE up on the same line so I don't think that would solve it.

[ILMV]

No I don't think you put a ; at the end of the last one before the WHERE statement.

and the second example you give just puts WHERE up on the same line so I don't think that would solve it.

 

1) You're right, no need to put a ; in there, but cleaning up your code might help

2) You're right again, but again it didn't look right, let me take another look.....

[ILMV]

Can you tell me what datatype your 'anticipate' field is in your DB..

[jeff5656]

anticipate text NOT NULL

[jeff5656]

Does anyone have any ideas?  I have the same code for another section of my site that works, all I did was change the variables.

[jeff5656]

Here's something else; if I delete most of the variables I still get an error, but the line number changes:

<?php
require('secure.php'); 


include "connectdb.php";


$newdate = date("Y-m-d", strtotime($_POST['rcf_date']));

$sql = "UPDATE icu SET
rm_loc = '" . $_POST['rm_loc'] . "',



WHERE id_incr = ".$_POST['id_incr']."";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());


?>
<p>
Done
<?php
}
header("Location: displayactive.php");

?>

 

then the error becomes: "Invalid query: 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 'WHERE id_incr =' at line 6"

 

instead of line 23. 

[mmarif4u]

I think you dont need this:

$sql = "UPDATE icu SET
   rm_loc = '" . $_POST['rm_loc'] . "',
   
   WHERE id_incr = ".$_POST['id_incr']."";

 

Should be like this:

 

$sql = "UPDATE icu SET rm_loc = '$_POST['rm_loc']' WHERE id_incr = '$_POST['id_incr']'";

try it and let us know.

[jeff5656]

No I gwt "Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\mc\commit.php on line 8"

 

when I do what you said:

<?php
require('secure.php'); 

include "connectdb.php";

$newdate = date("Y-m-d", strtotime($_POST['rcf_date']));

$sql = "UPDATE icu SET rm_loc = '$_POST['rm_loc']' WHERE id_incr = '$_POST['id_incr']'";

if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());


?>
<p>
Done
<?php
}
header("Location: displayactive.php");

?>

 

Also, this code exactly the same as another php file I have that works for another table in another database.  The only difference is the posted variables.

[mmarif4u]

Sorry my mistake.

Try this:

<?php
require('secure.php'); 

include "connectdb.php";

$newdate = date("Y-m-d", strtotime($_POST['rcf_date']));
$rm_loc = mysql_real_escape_string($_POST['rm_loc']);
$id_incr = mysql_real_escape_string($_POST['id_incr']);

$sql = "UPDATE icu SET rm_loc = '$rm_loc' WHERE id_incr = '$id_incr'";

if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());


?>
<p>
Done
<?php
}
header("Location: displayactive.php");

?>

[Alt_F4]

try echoing the query to the browser:

 

$sql = "UPDATE icu SET rm_loc = '$_POST['rm_loc']' WHERE id_incr = '$_POST['id_incr']'";
echo $sql;

 

and see what it outputs

[mmarif4u]

No, this query is wrong, cannot use two single quotes at the same time:

 '$_POST['rm_loc']' 

 

look at the updated code above in my post.

[jeff5656]

Wait I don't understand.  Here is the code from another file (for a different database) that works perfectly:

 

 

<?php
require('secure.php'); 

include "dateheader.php"; 
include "connectdb.php";


$newdate = date("Y-m-d", strtotime($_POST['rcf_date']));

$sql = "UPDATE icu SET
rm_loc = '" . $_POST['rm_loc'] . "',
patient = '" . $_POST['patient'] . "',
mrn = '" . $_POST['mrn'] . "',
age = '" . $_POST['age'] . "',
race = '" . $_POST['race'] . "',
gender = '" . $_POST['gender'] . "',
attg = '" . $_POST['attg'] . "',
rcf_date = '" . $_POST['rcf_date'] . "',
dx = '" . $_POST['dx'] . "',
pmhx = '" . $_POST['pmhx'] . "',
diet = '" . $_POST['diet'] . "',
meds = '" . $_POST['meds'] . "',
sed = '" . $_POST['sed'] . "',
ivf = '" . $_POST['ivf'] . "',
abx = '" . $_POST['abx'] . "',
cx = '" . $_POST['cx'] . "',
prophylaxis = '" . $_POST['prophylaxis'] . "',
vent = '" . $_POST['vent'] . "',
lin = '" . $_POST['lin'] . "',
code = '" . $_POST['code'] . "',
allergy = '" . $_POST['allergy'] . "',
todo = '" . $_POST['todo'] . "',
signoff_status = '" . $_POST['signoff_status'] . "',
rcf_date2 = '$newdate'


WHERE id_incr = ".$_POST['id_incr']."";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());


?>
<p>
Done
<?php
}
header("Location: displayactive.php");

?>

 

So before I re-do the whole code format, why would the above work? The quotes are set up the same way as in the code that gives me the syntax error.

 

[mmarif4u]

Both code will work fine, yours and mine. the difference is that i make it simple to use the variables globally in that page any where, no need to use post again and again.

Also i use mysql_real_escape_string function to clean the inputs from user. and you have no need to use the quotes each and every time, and would not be confused.

The best thing is to understand the single and double quotes working in PHP.

 

Hope i am clear.

 

[Alt_F4]

before you redo your code echo the query to the browser and check that it is contains the data that you expect it to and that the quotes and commas are in the right places, you may save yourself a lot of trouble

[kenrbnsn]

Since the field names in the DB match the input POST fields, I would let PHP build the query for me:

<?php
$qtmp = array();
foreach($_POST as $fld => $val) {
$val = trim(stripslashes($val));
switch($fld) {
	case 'rcf_date':
		$qtmp[] = "rcf_date2 = '" . date("Y-m-d", strtotime($_POST['rcf_date'])) . "'";
		$qtmp[] = $fld . " = '" . mysql_real_escape_string($val) . "'";
		break;
	case 'rm_loc':
	case 'patient':
	case 'mrn':
	case 'age':
	case 'race':
	case 'gender':
	case 'pod':
	case 'dx':
	case 'meds':
	case 'pmhx':
	case 'problist':
	case 'anticipate':
	case 'comments':
	case 'code':
	case 'allergy':
	case 'todo':
	case 'signoff_status':
		$qtmp[] = $fld . " = '" . mysql_real_escape_string($val) . "'";
		break;
}
}
$sql = 'UPDATE icu SET ' . implode(', ',$qtmp) . "WHERE id_incr = '" . mysql_real_escape_string($_POST['id_incr']) . "'";
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: $sql<br>" . mysql_error());
?>

 

Ken

[jeff5656]

Thanks it worked when I did it your way.  But I still don't see where in my code it was not working AND my other one was working.  But anyway, your way is much simpler and less confusing with all those damn quotation marks!

[jeff5656]

Thanks for the code regarding have php build it for me.  It looks a lot "cleaner" but I already got the code to work, but next time I do this (likely soon) I may try it your way. Thanx