[MySQL] Update Multiple Rows At Once

[evenmonkeys]

Can someone tell me the process in updating multiple rows at once? I've been updating one row at a time with:

$result = mysql_query("UPDATE tablename SET field='$field'") 
or die(mysql_error());

I'm curious as to how I could have multiple rows at once, and then edit them all all at one time. Let's use a user database as an example. Right now, I just print out every single user with a link as their name which takes me to their own page. I'd like to be able to edit the page in which they all print out.

Username: [ yarub ] Password: [ **** ] Email: [ yarub@yarub.com ]
Username: [ fred ] Password: [ **** ] Email: [ fred@yarub.com ]
Username: [ john ] Password: [ **** ] Email: [ john@yarub.com ]
[ update ]

The brackets represent input fields obviously. Can someone run me through the process of making this work? I have absolutely no idea and can't find any tutorials on it.

Thanks.

The screenshot below is what I'm trying to use it for. At the bottom of the list is a single update button.
http://lsms.reshasha.com/image/screens/pic4.jpg

[Adrian Schneider]

PHP Code:

$result = mysql_query("UPDATE tablename SET field='$field'") 

or die(mysql_error()); 


Wouldn't that update all the rows? There is no WHERE clause.

Do you want a javascript method where it updates the form elements? or a PHP method which updates all of the records in the database?

[evenmonkeys]

Well, I'm not looking to update rows based on a WHERE clause. I just want to be able to update all of the rows that are printed out at once like how vBulletin does it like the display order for forums. It shows all of the forums and lets you change the fields.

I'm looking for a PHP method that would update all of the records. =D

[Adrian Schneider]

Ok have the element names like this...

name="field[$userid]"

PHP Code:

foreach ($_POST['positions'] as $userid => $position)
{
    $userid = intval($userid);
    $position = mysql_real_escape_string($position);
    
    $hours = intval($_POST['hours'][$userid]);
    $lot = mysql_real_escape_string($_POST['lots'][$userid]);
    
    $result = mysql_query("
        UPDATE tablename
        SET
            position = '$position',
            hours = $hours,
            lot = '$lot'
        WHERE userid = $userid
    ");
    
    if (!$result) die(mysql_error());
} 


[evenmonkeys]

I'm not getting it to work. I made a test database with some information that's easy to work with. Can you modify this set of code so that it should work? I honestly don't understand much of this right now because of what I know, none of this even looks like it'd even work to me. Haha. I'd really appreciate it.

<?php

$conn = mysql_connect(" ", " ", " ")
or die(mysql_error());
mysql_select_db(" ",$conn) or die(mysql_error());

if($_POST[submit]){

  foreach($_POST['name'] as $id => $name){
    $id = intval($id);
    $name = mysql_real_escape_string($name);
    $lastname = mysql_real_escape_string($_POST['lastname']);
    $email = mysql_real_escape_string($_POST['email'][$id]);

    $result = mysql_query("
      UPDATE test_mysql
      SET
        name = '$name',
        lastname = '$lastname',
        email = '$email'
      WHERE id = '$id'
    ");

    if (!$result) die(mysql_error());
  }
}
echo("

<form name='form1' method='post' action=''>
      <table width='500' border='0' cellspacing='1' cellpadding='0'>

");
$query = mysql_query("select * FROM test_mysql");
while($row = mysql_fetch_array($query)){
echo("

        <tr>
          <td align='center'>
<input name='field[$id]' type='hidden' id='id' value='$row[id]'>$row[id]
          </td>
          <td align='center'>
<input name='field[$name]' type='text' id='name' value='$row[name]'>
          </td>
          <td align='center'>
<input name='field[$lastname]' type='text' id='lastname' value='$row[lastname]'>
          </td>
          <td align='center'>
<input name='field[$email]' type='text' id='email' value='$row[email]'>
          </td>
        </tr>

");
}
echo("

        <tr>
          <td colspan='4' align='center'>
<input type='submit' name='submit' value='Submit'>
          </td>
        </tr>
      </table>
</form>

");
?>

I'm either getting errors or nothing at all... or it's deleting rows. O_o Sometimes it says there's a syntax error on line 12. Sometimes it says there's no match for an entry. Then when I added [$id] after lastname, it started deleting the last row and rearranging the other fields in other rows. Lol. PHP hates me.

[Adrian Schneider]

Ok for each field you have, you'll need to make it an array. For firstname, set it as "firstnames[$row[id]]" and lastname, "lastnames[$row[id]]" so you end up submitting each piece of data tied to the user.

When you receive the data, you loop through one of the arrays (all should have the same "keys" [ids] so it doesn't matter which field you choose).

Inside the loop, you'll have to grab the other fields

PHP Code:

foreach ($_POST['firstnames'] as $userid => $firstname)
{
    $lastname = $_POST['lastnames'][$userid];
    $email = $_POST['emails'][$userid];

    // update query
} 


[evenmonkeys]

For the record, you just jumbled my brain far beyond all recognition and I am now lost more than ever. I'm a nuissance; I know. I honestly don't understand. Could I possibly bribe you into editing the code I attached above to make it work? If I have a working version of it, I can usually break it down afterwards to understand why it works the way it does. Getting bits and pieces is just making it harder for me to understand. If not, no problem. =\

[Adrian Schneider]

Try this...

PHP Code:

<?php

$conn = mysql_connect(" ", " ", " ")
or die(mysql_error());
mysql_select_db(" ", $conn) or die(mysql_error());

if ($_POST['submit'])
{
    foreach ($_POST['ids'] as $id)
    {
        $id = intval($id);

        $name = mysql_real_escape_string($_POST['names'][$id]);
        $lastname = mysql_real_escape_string($_POST['lastnames'][$id]);
        $email = mysql_real_escape_string($_POST['emails'][$id]);

        $result = mysql_query("
            UPDATE test_mysql
            SET
                name = '$name',
                lastname = '$lastname',
                email = '$email'
            WHERE id = '$id'
        ");

        if (!$result) die(mysql_error());
    }
}

echo("

<form name='form1' method='post' action=''>
      <table width='500' border='0' cellspacing='1' cellpadding='0'>

");

$query = mysql_query("
    SELECT *
    FROM test_mysql
");

while($row = mysql_fetch_array($query))
{
    $userid = $row['id'];
    echo("

            <tr>
              <td align='center'>
    <input name='ids[]' type='hidden' id='id' value='$row[id]'>$row[id]
              </td>
              <td align='center'>
    <input name='names[$userid]' type='text' id='name' value='$row[name]'>
              </td>
              <td align='center'>
    <input name='lastnames[$userid]' type='text' id='lastname' value='$row[lastname]'>
              </td>
              <td align='center'>
    <input name='emails[$userid]' type='text' id='email' value='$row[email]'>
              </td>
            </tr>

    ");
}

echo("

        <tr>
          <td colspan='4' align='center'>
<input type='submit' name='submit' value='Submit'>
          </td>
        </tr>
      </table>
</form>

");

?>

[evenmonkeys]

Wow. Works like a charm! I can't even being to thank you enough. I've been working on this for days now. Thank you very much, SirAdrian. I really really appreciate it.

[Adrian Schneider]

No problem...