how to UPDATE multiple inputs

[cinq]

I wish to display order of certain categories for eg.

Cat 1 , order : 1
Cat 2 , order : 2

( Runs thru the catorder table to display category names in ASC order of catorders. )

The orders are displayed as input boxes, which can be changed in value , say to reverse the order, i will put 2 in Cat 1's order input box and vice versa.

At the end of the form, i will click on a Submit button and the categories will have their orders updated accordingly ( i have a table column for Category order )

How should i go about doing this ?

Thanks for any help rendered.

[Velocd]

PHP Code:

$fetch_cats = $DB_site->query("SELECT id,title,order FROM `categories` ORDER BY order DESC");

while ($category = $DB_site->fetch_array($fetch_cats))
{
  echo $category['title'] . '   ';
  echo '<input type="text" name="cat_order['.$category['id'].']" value="'.$category['order'].'" />';
} 


Then the POST process portion:

PHP Code:

$fetch_cats = $DB_site->query("SELECT id,title,order FROM `categories`");

while ($category = $DB_site->fetch_array($fetch_cats))
{
    $DB_site->query("UPDATE `categories` SET order=$_POST[cat_order][$category[id]] WHERE id=$category[id]");
} 


This should work. You might get a parse error or two since I compiled this very quickly, but it shouldn't be hard to work around.

[filburt1]

Originally Posted by Velocd

PHP Code:

$fetch_cats = $DB_site->query("SELECT id,title,order FROM `categories` ORDER BY order DESC");

while ($category = $DB_site->fetch_array($fetch_cats))
{
  echo $category['title'] . '   ';
  echo '<input type="text" name="cat_order['.$category['id'].']" value="'.$category['order'].'" />';
} 


Then the POST process portion:

PHP Code:

$fetch_cats = $DB_site->query("SELECT id,title,order FROM `categories`");

while ($category = $DB_site->fetch_array($fetch_cats))
{
    $DB_site->query("UPDATE `categories` SET order=$_POST[cat_order][$category[id]] WHERE id=$category[id]");
} 


This should work. You might get a parse error or two since I compiled this very quickly, but it shouldn't be hard to work around.

Very bad idea to query in a loop...

[Dean C]

Then how would we get round this one?

[Velocd]

There aren't many fast ways around it.

The method I provided is the exact vBulletin uses to update the order of forums in the Admin Cp:

PHP Code:

// ###################### Start do order #######################
if ($_POST['do'] == 'doorder')
{
    globalize($_POST, array('order'));

    if (is_array($order))
    {
        $forums = $DB_site->query("SELECT forumid,displayorder FROM " . TABLE_PREFIX . "forum");
        while ($forum = $DB_site->fetch_array($forums))
        {
            $displayorder = intval($order["$forum[forumid]"]);
            if ($forum['displayorder'] != $displayorder)
            {
                $DB_site->query("
                    UPDATE " . TABLE_PREFIX . "forum
                    SET displayorder = $displayorder
                    WHERE forumid = $forum[forumid]
                ");
            }
        }
    }

    build_forum_permissions();

    define('CP_REDIRECT', 'forum.php?do=modify');
    print_stop_message('saved_display_order_successfully');
} 


The only main difference is this:

PHP Code:

            $displayorder = intval($order["$forum[forumid]"]);
            if ($forum['displayorder'] != $displayorder) 


Which is a check to ensure that it's not quering and updating a row when it has yet to be modified.

You could probably apply the same to the code above.

[Dean C]

I feel ashamed to be bumping a reasonably old thread but i'm ecountering a similar issue at the moment. I want to update multiple rows in a database with one query. Any solution?

[Admin]

Supposedly this might work:

UPDATE table SET order = IF(id = 1, 1, IF(id = 2, 2, 3)) WHERE id IN (1, 2, 3);

But I doubt it.

[TECK]

Originally Posted by Chen

Supposedly this might work:

UPDATE table SET order = IF(id = 1, 1, IF(id = 2, 2, 3)) WHERE id IN (1, 2, 3);

But I doubt it.

Whoot? You are still alive?

[cinq]

Originally Posted by TECK

Whoot? You are still alive?

Will try some of the code provided here, thanks guys

[Dean C]

Originally Posted by TECK

Whoot? You are still alive?

He's around all the time in the vBulletin.org chat TECK

Btw cinq - the best way I found of doing this was via vb3's method which VelocD highlighted