[RESOLVED] updating records in db error

[dr_aybyd]

Hi, i am new in php

Whats wrong with my codes?
My server is using PHP Version 5.2.11

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);
mysql_query("UPDATE my_table SET 
machinetime = '$_POST[strDate]',
server = '$_POST[strServer]',
name = '$_POST[struser]',
imei = '$_POST[strImei]',
model = '$_POST[strModel]',
price = '$_POST[strPrice]',
refnum = '$_POST[strRef]'
WHERE PID = '$_POST[processID]'");
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record Updated";
mysql_close($con)
?>

When i click submit in my Form

It always throw me an error "Error: Query was empty"

[kows]

well, you never define $sql. you outright query it. you can either get rid of the call to mysql_query() around your SQL, and define it as the variable $sql, or you can change your IF statement to check the previous call to mysql_query() rather than the one you are now. or, you can define the first call to mysql_query() as a variable ($query, for example), and then use your IF statement to check if $query returns true or false, rather than another call to mysql_query. you need to get rid of at least one of the mysql_query() calls, though. whichever one you do get rid of is your choice. however, your code does update the database every time you submit your form.

either way, you might want to run those $_POST variables through mysql_real_escape_string() before putting them into your database. this will help you prevent SQL injection.

hope that makes sense; ask questions if you need to.

[dr_aybyd]

Hi I modofied my codes

<?php
$con = mysql_connect("localhost","username","password");

mysql_select_db("my)db", $con);
$sql = "UPDATE $sales_list SET 
machinetime = '$_POST[strDate]',
server = '$_POST[strServer]',
name = '$_POST[struser]',
imei = '$_POST[strImei]',
model = '$_POST[strModel]',
price = '$_POST[strPrice]',
refnum = '$_POST[strRef]'
WHERE PID = '$_POST[processID]'";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record Updated";
mysql_close($con)
?>

But throwing me another error. "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 'SET machinetime = '12/7/2009 12:31:42 PM', server = 'http://gsmfather.com/', na' at line 1"

[SambaNeko]

There's a problem with your SQL string, but I don't see it in the snippet that the error message has provided. Please try this:

<?php
$con = mysql_connect("localhost","username","password");

mysql_select_db("my)db", $con);
$sql = "UPDATE $sales_list SET 
machinetime = '$_POST[strDate]',
server = '$_POST[strServer]',
name = '$_POST[struser]',
imei = '$_POST[strImei]',
model = '$_POST[strModel]',
price = '$_POST[strPrice]',
refnum = '$_POST[strRef]'
WHERE PID = '$_POST[processID]'";

echo "SQL: ".$sql;

?>

...and post the resulting full SQL string.

[kows]

assuming that this is your entire script (and not just a tiny snippet), you are not defining the $sales_list variable (your table name). however, if you defined this previously, then I guess that wouldn't be a problem.

other than that, the SQL looks fine. so, if you're still having problems you would need to use mysql_real_escape_string() on your $_POST variables to escape any quotes that may be messing your query up.

[dr_aybyd]

@knows

that was my entire codes for updating...

The other is codes for FORM for submitting values for editing,
Can you help me modify my codes to the one you are saying? the mysql_real_escape_string().
I am really new in php.

[kows]

like I stated in my post, if that was your only code then your error is simply because you're referring to $sales_list as your table name but you never define $sales_list, so MySQL doesn't know what table you're trying to update. this would be the root of your error.

if you'd still like to use mysql_real_escape_string() anyway, then the simplest way would be to store the escaped value in a separate variable and then use that separate variable in your SQL. you could do something like:

<?php
  $machinetime = mysql_real_escape_string($_POST['strDate']);
  $server = mysql_real_escape_string($_POST['strServer']);
  //and the rest for your other variables

  $sql = "UPDATE sales_list SET
          machinetime='$machinetime',
          server='$server',
          ....

[Nightwalker83]

You might also want to make sure data is being sent from your html form before sending information to the database this is to make sure there is actually information to be sent to the database.

if(isset($_POST['processID'])){
machinetime = '$_POST[strDate]';
....
} 

[kows]

if you're going to do anything of the sort, you should do it the proper way by checking the value of $_SERVER['REQUEST_METHOD'].

if($_SERVER['REQUEST_METHOD'] == "POST"){
  /* stuff */
} 

[dr_aybyd]

Thanks for all your replies..it helps me correcting my codes.

here is my codes that works:

<?php
$con = mysql_connect("localhost","xxxxxxxx","xxxxxxxx");

mysql_select_db("xxxxxxxx", $con);

  $machinetime = mysql_real_escape_string($_POST['strDate']);
  $server = mysql_real_escape_string($_POST['strServer']);
  $name = mysql_real_escape_string($_POST['struser']);
  $imei = mysql_real_escape_string($_POST['strImei']);
  $model = mysql_real_escape_string($_POST['strModel']);
  $price = mysql_real_escape_string($_POST['strPrice']);
  $refnum = mysql_real_escape_string($_POST['strRef']);
  $PID =mysql_real_escape_string($_POST['processID']);

  $sql = "UPDATE sales_list SET
          pctime='$machinetime',
          server='$server',
      name='$name',
      imei='$imei',
      model='$model',
      price='$price',
      refnum='$refnum'
WHERE PID = '$PID'";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record Updated";

mysql_close($con)
?>