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. :D

[Velocd]

$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:


$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]

$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'] . '&nbsp;&nbsp;&nbsp;';
echo '<input type="text" name="cat_order['.$category['id'].']" value="'.$category['order'].'" />';
}


Then the POST process portion:


$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:

// ###################### 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_successful ly');
}

The only main difference is this:
$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]

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? :D

[cinq]

Whoot? You are still alive? :D
:D

Will try some of the code provided here, thanks guys :)

[Dean C]

Whoot? You are still alive? :D
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 :)

[cinq]

Thanks Velo and Mist. :)

The code works wonderfully , after slight changes to the syntax :D:D