CODEWITHSUNDEEP
phpmysql
Erik
Erik

Reputation: 5791

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

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>";
?>

Upvotes: 0

Views: 354

Answers (2)

bdl
bdl

Reputation: 1502

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.

Upvotes: 0

Damien Pirsy
Damien Pirsy

Reputation: 25435

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.

Upvotes: 2

Related Questions