0

I have some problem when want to update my value in database. There is no error shown. The page will ust redirect as indicated even when the value is not updated. This is the code for user to input..

    echo "<td bgcolor='#FFFFFF'><input id='id' name='pro_id[]' type='text'></td>";
    echo "<td bgcolor='#FFFFFF'><input id='name' name='pro_name[]' type='text'></td>";
    echo "<td bgcolor=‘#FFFFFF’><input id='quan' name='pro_quan[]' type='text'></td>";

Below is the code for my insert value..

$query = "INSERT INTO product (username, pro_id, pro_name, pro_quan) VALUES ";
for ($i = 0; $i < count($_POST['pro_id']); $i++) {
$query .= " ('{$username}', '{$id[$i]}', '{$name[$i]}', '{$quan[$i]}'),";
}
$query = substr($query, 0, -1);

$result = mysqli_query($con, $query) or die(mysqli_error($con));

The insert code work fine. The value is inserted into the database.Below is my update code..

$sql = "SELECT * FROM product where username = '$username'";
foreach($_SESSION['product'] as $item)
{
$id = $item['pro_id'];
$name = $item['pro_name'];
$quan = $item['pro_quan'];
$sold = $item['pro_sold'];
$sql="UPDATE product SET pro_id='".$id."', pro_name='".$name."', pro_quan='".$quan."', pro_sold='".$sold."' WHERE username = '".$username."'";
}

$results=mysqli_query($con, $sql);

The value couldn't be updated. I have no idea what have gone wrong.So, any help will be appreciated.Thanks

Improve this question
4
  • 1
    where is WHERE clause in update query. Commented Apr 18, 2017 at 4:43
  • 1
    sorry, i miss that Commented Apr 18, 2017 at 4:47
  • Why names in input tags have brackets? Commented Apr 18, 2017 at 15:38
  • that is to declare it as an array Commented Apr 19, 2017 at 2:09

4 Answers 4

Reset to default
1

$id and $quan in your update query are between single quotes. I don't know anything about your database structure, but something tells me those values are numbers and not strings. Here is the updated line:

$sql="UPDATE product SET pro_id=".$id.", pro_name='".$name."', pro_quan=".$quan.", pro_sold='".$sold."'";

You might have to remove the quotes around $sold as well.

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

5 Comments

@June, I don't really understand your code. You are assigning a value to $sql on each iteration of the loop, why? What exactly are you trying to achieve with your code, and what are the types of values you are trying to store? Is $_SESSION['product'] an array at all? These are some of the questions I have.
ok, now i have to insert several number of products into the database. So for the product, i need to update the value of the product such as product quantity, product sold and so on as the user might input wrongly.
You are assigning a value to $sql inside a loop, its value is going to be whatever it is on the last iteration of the loop when you run mysqli_query.Try echoing the queries you are trying to run before/instead of running them to see if there's anything out of the ordinary there as well.
how should i echo it?
Sorry about the delay. Like anything else, just do echo "UPDATE product SET pro_id=".$id.", pro_name='".$name."', pro_quan=".$quan.", pro_sold='".$sold."'"; or echo $sql;. This will show you what it is you are trying to insert.
1

Need to put sql execution in foreac, as below...

$sql = "SELECT * FROM product where username = '$username'";
$results=mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($results)){
 $id = $row['pro_id'];
 $name = $row['pro_name'];
 $quan = $row['pro_quan'];
 $sold = $row['pro_sold'];
 $sql="UPDATE product SET pro_id='".$id."', pro_name='".$name."', pro_quan='".$quan."', pro_sold='".$sold."' WHERE pro_id = '".$id."' ";
 mysqli_query($con, $sql);
}
Improve this answer

3 Comments

Notice: Undefined index: product in F:\xampp\htdocs\New folder\upProduct.php on line 15 Warning: Invalid argument supplied for foreach() in F:\xampp\htdocs\New folder\upProduct.php on line 15 Notice: Undefined variable: results in F:\xampp\htdocs\New folder\upProduct.php on line 27
Still not working, it directly update all the values into 0.
ok, previously my where clause is username, so after i change it to id, the values are still not uodated. So, any ideas why?
1

first of all check the pro_id that is primary key or not. if it is a primary key than write query in this way.

$sql="UPDATE product SET  pro_name='".$name."', pro_quan='".$quan."', pro_sold='".$sold."' WHERE pro_id = '".$id."' ";

because primary key generate error if the uniqueness of the column is disturb.

Improve this answer

Comments

0

Thanks guys for all your helping. I have change my update code by using for loop. Below is the solution..

for ($i = 0; $i < count($_POST['pro_id']); $i++) {
$sql="UPDATE product SET pro_name='".$name[$i]."', pro_quan=".$quan[$i].", pro_sold=".$sold[$i]." WHERE pro_id=".$id[$i]."";
$results=mysqli_query($con, $sql);
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.