[SOLVED] error in your SQL syntax;

[jeff5656]

After updating a form, the action goes to the following php file.  But I get an error:

 

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 '= 'fred', = '77', = '6', = '', = '0000-00-00', = '0000-00-00', = ''' at line 2

 

FYI:  'fred' refers to the your_name field, etc.

 

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

include "connectdb.php";


$sql = "UPDATE cin SET
$your_name = '" . $_POST['your_name'] . "',
$card_no  = '" . $_POST['card_no'] . "',
$name = '" . $_POST['name'] . "',
$mrn = '" . $_POST['mrn'] . "',
$date_enroll = '" . $_POST['date_enroll'] . "',
$date_ct = '" . $_POST['date_ct'] . "',
$age = '" . $_POST['age'] . "',
$sex = '" . $_POST['sex'] . "',
$race = '" . $_POST['race'] . "',
$ct_type = '" . $_POST['ct_type'] . "',
$diabetes = '" . $_POST['diabetes'] . "',
$lvef = '" . $_POST['lvef'] . "',
$htn = '" . $_POST['htn'] . "',
$acei = '" . $_POST['acei'] . "',
$diuretic = '" . $_POST['diuretic'] . "',
$ca_channel = '" . $_POST['ca_channel'] . "',
$a2r = '" . $_POST['a2r'] . "',
$nsaid = '" . $_POST['nsaid'] . "',
$renal_abx = '" . $_POST['renal_abx'] . "',
$bp = '" . $_POST['bp'] . "',
$weight = '" . $_POST['weight'] . "',
$height = '" . $_POST['height'] . "',
$day0_bc = '" . $_POST['day0_bc'] . "',
$day0_bun = '" . $_POST['day0_bun'] . "',
$day0_k = '" . $_POST['day0_k'] . "',
$day0_cr = '" . $_POST['day0_cr'] . "',
$whatgroup = '" . $_POST['whatgroup'] . "',
$day1_cr = '" . $_POST['day1_cr'] . "',
$day2_cr = '" . $_POST['day2_cr'] . "',
$comments = '" . $_POST['comments'] . "'

WHERE card_no = ".$_POST['card_no']."";

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


?>

 

This is basically cut and paste from my other (working) script and all I did was change the variables.

[AndyB]

$your_name should be your_name. Same for all the other variables in your update query. your_name is the name of the field you want to update, $your_name is an undefined empty string (as shown by your error message)

[jeff5656]

Thanks! that worked.

[kenrbnsn]

The query is incorrect.  The set format is

update tablename set fieldname = 'value'

 

This is basically cut and paste from my other (working) script and all I did was change the variables.

Then you didn't change it correctly.

 

You also are inviting MySQL injections since you are not using the mysql_real_escape_string() on the values.

 

Here's how I would do this:

<?php
$qtmp = array();
foreach ($_POST as $k=>$v) {
    if ($k != 'submit') // put the name of your submit button here
           if (strlen(trim(stripslashes($v))) != 0) // is the field entered
                 $qtmp[] = $k . " = '" . mysql_real_escape_string(stripslashes($v)) . "'";
}
if (!empty($qtmp)) {
    $sql = "UPDATE cin SET " . implode(', ',$qtmp) . "WHERE card_no = ".$_POST['card_no'];
    $rs = mysql_query($sql) or die("Problem with the query: $sql<br>" . mysql_error());
}
?>

 

Ken

[jeff5656]

Ken, I used your code and it works.  That is amazing because it means I don't have to type all the field names anymore!  Also the security thing with stripping, as was your original intent 

[kenrbnsn]

My method works as long as the field names in your form correspond to the field names in your table.

 

Ken