Foreach loop still execute after the last array is updated

Question

I get an error "Error: Query was empty" after completing the task instead of the echo result. I suspect that the FOREACH still attempt to execute the query even the last array is updated. Am I right?

If yes, how can I correct this?

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

<?php

  foreach ($_POST['id'] as $key=>$id)
  {
    $order = $_POST['order'][$key];
    mysql_query("UPDATE table SET `order`=$order WHERE `id` = $id");
  }
    if (!mysql_query($sql,$con))
     {
       die('Error: ' . mysql_error());
     }
       echo "<div class=\"result\">**1 record updated**</div>";         
?>

Many thanks

More information about the form posting these value : Create array from form and update mysql

At what point did you populate `$sql`? Not in the above code... That's the empty query which is causing errors. — Michael Berkowski, Jul 05 '13 at 02:17
At a minimum, you must protect `$id` against sql injection. If it is an integer expected, do `$id = intval($id)` — Michael Berkowski, Jul 05 '13 at 02:17
I have just updated the question with the content of the init file. And yes Dagon, I have several ids and orders from a form. — Michael Eugene Yuen, Jul 05 '13 at 02:25
Thanks, Michael. I will add intval($id) to and intval($order) to it. :) — Michael Eugene Yuen, Jul 05 '13 at 02:32

score 2 · Answer 1 · answered Jul 05 '13 at 02:31

2

The problem could be in:

 if (!mysql_query($sql,$con))

its' running mysql_query without $sql being defined.

Though i don't recommend putting a query inside a loop because it's a waste of memory, for your code sample - try putting mysql_query() inside a variable like so:

$sql = mysql_query("UPDATE table SET `order`=$order WHERE `id` = $id");

answered Jul 05 '13 at 02:31

jagc

209
3
13

Thanks! But wonder why it still updates the table even without the $sql= – Michael Eugene Yuen Jul 05 '13 at 03:10

score 1 · Accepted Answer · answered Jul 05 '13 at 02:20

You're not checking your errors properly, and instead, calling mysql_query twice. Do this:

<?php
foreach ($_POST['id'] as $key=>$id)
{
  $order = $_POST['order'][$key];
  $result = mysql_query("UPDATE table SET `order`=$order WHERE `id` = $id");
  if ($result === false)
  {
    die('Error: ' . mysql_error());
  }
  echo "<div class=\"result\">**1 record updated**</div>";         
}
?>

Obligatory notices:

Don't' use mysql - use mysqli or PDO
Do make sure you properly escape inputs to SQL queries. Use mysql_real_escape_string()

Thanks, I have used your method and added this => foreach ($_POST['id'] as $key=>$ids) { $id = mysql_real_escape_string(ids);..... } — Michael Eugene Yuen, Jul 05 '13 at 03:13

score 1 · Answer 3 · edited May 23 '17 at 11:56

You have excluded query execution from loop.
You haven't defined $sql anywhere, so it is threated as empty string.

Use this:

<?php
foreach ($_POST['id'] as $key => $id)
{
    $order = mysql_real_escape_string($_POST['order'][$key], $con);
    $id    = mysql_real_escape_string($id, $con);

    $sql   = "UPDATE table SET `order` = $order WHERE `id` = $id";

    if(!mysql_query($sql, $con))die(mysql_error());
}      
?>

NOTE: MySQL (mysql_* functions) extension is deprecated. I suggest to use MySQLi (mysqli_* functions) or PDO instead.

NOTE: Your code is vulnurable to SQL-Injection attacks. You may read solution suggestions in this question: How can I prevent SQL injection in PHP?.

score 1 · Answer 4 · answered Jul 05 '13 at 03:00

In other condition, have you check your connection before? If it's okay try this

<?php
  foreach ($_POST['id'] as $key=>$id)
  {
    $order = $_POST['order'][$key];
    $query = "UPDATE table SET `order`=$order WHERE `id` = $id";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());

  }
    if (!$result)
     {
       die('Error: ' . mysql_error());
     }
       echo "<div class=\"result\">**1 record updated**</div>";         
?>

Foreach loop still execute after the last array is updated

4 Answers4