vb.org Archive - [MySQL] Update Multiple Rows At Once

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - [MySQL] Update Multiple Rows At Once (https://vborg.vbsupport.ru/showthread.php?t=129800)

[MySQL] Update Multiple Rows At Once

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

Code:

$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

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?

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

Ok have the element names like this...

Code:

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());

}

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.

Code:

<?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.

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
}

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. =\

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>

");

?>

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.

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (4)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02258 seconds Memory Usage 1,775KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (4)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: