Trying to update mysql record via php form. Need help?

Question

I have form that I want to use to update my a mySQl row. I have my search retrieval script done to find the record. Now I need to update it.

When I submit the form, I get a confirmation the part_no was updated, but it doesn't show up in the database.

What am I doing wrong? Does anyone see any errors in my script?

Thank you.

Erik

Here is my script:

<?PHP
session_start();
?>

<?php


$orig_time=$_POST['orig_time'];
$type=$_POST['type'];
$part_no=$_POST['part_no'];
$description=$_POST['description'];
$count=$_POST['count'];
$size=$_POST['size'];
$min=$_POST['min'];
$max=$_POST['max'];
$qty=$_POST['qty'];

if ($part_no == "") echo "! No identifier retrieved";
else
echo "Amending record $part_no";

$host="localhost";
$username="XXXXXX";
$password="XXXXXX";
$db_name="naturan8_hero";
$tbl_name="cartons_current";

mysql_connect("$host", "$username", "$password") or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

mysql_query("UPDATE cartons_current SET orig_time='$orig_time', type='$type',   
description='$description', count='$count', size='$size', min='$min', max='$max',  
qty='$qty', WHERE reference='$part_no'");

echo "<BR>$part_no was updated.<BR><BR>";
?>

"$var" should be only $var as it does not make any sense to encapsulate variables with double quotes. Does your query process without any database error? You have got a , before WHERE. Are you sure that there is a reference with the given $part_no? — str
– str, Commented Oct 17, 2011 at 19:35
First, read about sql injection. Your database is in grave danger! Then, use mysql_error() to check if mysql_query gave any error. — GolezTrol
– GolezTrol, Commented Oct 17, 2011 at 19:38

Damien Pirsy · Accepted Answer · 2011-10-17 19:49:27Z

2

You have a syntax error in your query. Also, use an error checker to see what failed. And be sure you actually have a $part_no to identify the correct row to update:

And, again, beware of SQL injections!

$orig_time = mysql_real_escape_string($_POST['orig_time']);
// do the same for the other $_POST indexes before passing them to the query!
// if values are numeric, you can cast them to INT to make sure they are numbers
// e.g. $count = intval($_POST['count']);



mysql_query("UPDATE `cartons_current` 
          SET `orig_time` ='".$orig_time."', 
          `type` = '".$type."',
          `description` = '".$description."', 
          `count` = '".$count."', 
          `size` = '".$size."', 
          `min` ='".$min."', 
          `max` ='".$max."',
          `qty` ='".$qty."'   
          WHERE `reference` = '".$part_no."'") or trigger_error(mysql_error());

UPDATE:

Try calling mysql_affected_rows() to see the outcome of your operation. Also, consider that

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

edited Oct 17, 2011 at 19:49

answered Oct 17, 2011 at 19:37

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

bdl Over a year ago

Why do you escape the "double quoted" string? If you're already using double quotes, go ahead and let the values be interpreted in the string.

Erik Over a year ago

Thanks, but I'm still getting a confirmation but the update does not take effect. I don't see any update even though I get confirmation. I get no errors.

Damien Pirsy Over a year ago

Is $part_no the right value? Are there, actually, any changes in your data?

Erik Over a year ago

Yes.. min, max, description, and count can be changed. part_no is the key reference for searching.

Damien Pirsy Over a year ago

Yeah, I got that, but is it set? is it the right row you're going to update?

|

bdl · Accepted Answer · 2011-10-17 19:53:58Z

Your issue seems to stem from this error in the SQL statement:

, WHERE reference='$part_no'"

You should be a) running all SQL statements on a test database with test data prior to building your script and b) using some sort of logging where the SQL error can be made available to you (not displayed on the page where everyone can view it). In other words, get rid of the OR die() statements you have during the connect and database selection function calls.

I would recommend looking at using the PDO library for database connection. It's much more versatile and allows you to implement prepared statements, which are much better than either sending unescaped data or even using something like mysql_real_escape_string().

In addition, your script logic seems to hinge on whether or not the $_POST['part_no'] value is valid. Make sure you do this before you do anything else. Kill the script with an error or redirect. No sense connecting to the database if the correct data isn't there.

Here's a quick trick to reassigning all those incoming POST values, BTW:

foreach($_POST AS $k=>$v) {
  $$k = $v;
}

What this effectively does is create a variable in the namespace with the same name as the array key, e.g. $_POST['part_no'] becomes $part_no. This eliminates all the unneccessary lines of code that go into reassigning values. You can also make use of the loop to perform any data validation or cleaning / escaping and you don't have to do it all by hand.

Collectives™ on Stack Overflow

Trying to update mysql record via php form. Need help?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related