Member Avatar for nicholaslee21

I cannot get this mysql update query to work. Please help. I am new at this.:)

<?PHP
session_start();
?>

<?php

//it's getting the data from the post fine. I know this part is working. 

$ud_id=$_POST['ud_id'];
$ud_fname=$_POST['ud_fname'];
$ud_lname=$_POST['ud_lname'];
$ud_type=$_POST['ud_type'];
$ud_used=$_POST['ud_used'];
$ud_sdate=$_POST['ud_sdate'];
$ud_edate=$_POST['ud_edate'];

if ($ud_id == "") echo " No record ID supplied! <br>";
else
echo "Amending record $ud_id";

if ($ud_id == "") echo " ";
else
echo "<br> Employee Name: $ud_fname $ud_lname";

//clean up any carriage returns etc

$ud_fname = preg_replace("/[\n\r]*/","",$ud_fname);
$ud_lname = preg_replace("/[\n\r]*/","",$ud_lname);



//Select the database we want to use
$db = mysql_connect("localhost", "root", "");
mysql_select_db("test1",$db) or die ('Unable to connect to database');

//NOT WORKING
mysql_query("UPDATE persons SET FirstName ='$ud_fname', LastName ='$ud_lname', Type ='$ud_type', DaysUsed ='$ud_used', StartDate ='$ud_sdate', EndDate ='$ud_edate', WHERE ID = '$ud_id'");

if ($ud_id == "") echo " ";
else
echo "<BR>Record Updated<BR><BR>";
?>

<?php

I then display the new data, but data is not updated.

Edited by mike_2000_17 because: Fixed formatting
  • 3 Contributors
  • 3 Replies
  • 5K Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by orcaraheel

Dani AI

Generated

Two quick pointers that will save you time here. First, an UPDATE with a stray comma before WHERE is a syntax error. Always surface SQL errors while developing so you see problems immediately. With the old mysql_ API you can do: mysql_query($sql) or die('SQL error: '.mysql_error());. Also check whether anything changed using mysql_affected_rows(). If your date columns are DATE, make sure your inputs are in YYYY-MM-DD format; MySQL will not coerce arbitrary strings (UPDATE syntax, date/time types). Note that mysql_ is deprecated and removed in PHP 7; prefer mysqli or PDO with prepared statements (mysqli prepared statements, PDO prepared statements).

For the later question about "only the last row updates": that usually means your form posts a single quantity and id value. When updating multiple rows from one form, name your inputs as arrays (e.g., quantity[123]) so each quantity is keyed by its row id, then loop server-side:

$db = new mysqli('localhost','user','pass','dbname');
$stmt = $db->prepare('UPDATE productorder SET Product_quantity = ? WHERE product_id = ?');
foreach ($_POST['quantity'] as $id => $qty) {
    $qty = (int)$qty;
    $id  = (int)$id;
    $stmt->bind_param('ii', $qty, $id);
    $stmt->execute();
}

This updates every targeted row safely and predictably.

Recommended Answers

All 3 Replies

Member Avatar for ApocDen

replace this with your line

mysql_query("UPDATE persons SET FirstName ='$ud_fname', LastName ='$ud_lname', Type ='$ud_type', DaysUsed ='$ud_used', StartDate ='$ud_sdate', EndDate ='$ud_edate' WHERE ID = '$ud_id'");

you had a "," between the EndDate ='$ud_edate' and WHERE ID = '$ud_id'"

Member Avatar for nicholaslee21

Thank you so much ApocDen!!!! I've been trying to figure this out for days!

Member Avatar for orcaraheel 
$con=mysql_connect("$hostname_boss", "$username_boss", "$password_boss")or die("cannot connect");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
                                 mysql_select_db("$database_boss")or die("cannot select DB");    
$update = $_POST['update'];
$quantity = $_POST['quantity'];
$id = $_POST['id'];
if($update){
$insert2="UPDATE productorder SET Product_quantity = '$quantity' WHERE product_id = '$id'";  
$resu= mysql_query($insert2);
echo "your quantity: $quantity";
echo "your id: $id";
echo "<meta http-equiv=\"refresh\" content=\"0;URL=shoping_cart.php\">";
mysql_close();
}

they update the lasr row(record) in database table how i update the other row(records)

Edited by Reverend Jim because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.