[SOLVED] Stuck: Form with multiple, unique mysql updates...

[MishieMoo]

Okay so for the forums that I'm coding I made a an order feature that lets you order how the categories are displayed on the page.  I'm trying to add a form in the admin area where this can change.  I'm stumped now.  I'm out of solutions so I'm asking for help.

 

Basics:  Each category has an order number (1, 2, 3...).  I want to have a table, with two columns.  One displays category name and id, and the other has a dropdown for choosing the order number.  I'm using switches for this so action=update display's the form, and action=orderupdate has the actual update query.  I have the html setup all fine, it's the processing/naming that's confusing me.

 

Problems:

1. The drop down only displays the number '1'.  Why and how do I change it?

2. How do I get the query to run one time for each entry submitted? 

 

Here's what I have, for this part.  The main page is huge and has 6 or 7 cases so I'm not posting it all.  It's not much, but this is the problem area.  And I know it's not right =(

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">';
    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");
    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
        ?>
        <tr><td><?php echo $name; ?></td><td><select name="order">
        <option><?php echo $catorder; ?></option>
        <?php $oquery=mysql_query("SELECT catorder FROM category ORDER BY catorder ASC");
        while($oresult=mysql_fetch_array($oquery)){
        $number=$oresult['catorder'];
            
            echo'<option value="'.$number.'">'.$number.'</option></td>';
    } echo'</select></td></tr>';
    }
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';
    echo'</tr></table></center>';
break;

case "orderchange":
    $neworder=$_POST['order'];
    $query=mysql_query("SELECT id FROM category ORDER BY catorder ASC");
    while($result=mysql_fetch_array($query)) {
        $catid=$result['id'];
        $update=mysql_query("UPDATE category SET catorder='$neworder' WHERE id='$catid'");
        
        }
break;
?>

 

Please let me know if there's any other information needed.

 

Thanks in advance!

 

Edit: (clarification requested)

I need help making one form declare multiple values for catorder(essentially), and then save each value into the database.  Each catorder value has to be assigned to a category id (just id in the code).  I don't know how to do this.  And there are multiple categories already in existence.

[teng84]

can you explain it clearer!

 

and maybe you might need isset or empty to determine which process to perform?

 

1. The drop down only displays the number '1'.  Why and how do I change it?

 

this query $oquery=mysql_query("SELECT catorder FROM category ORDER BY catorder ASC");

are you sure it returns multiple records?

[MishieMoo]

How much clearer can it be explained?  I want a select menu as part of a form to change the value  called catorder.  I want to be able to declare multiple values for this while within the constraints of one form, so I don't have to have a separate submit button for each.

 

And yes, I'm sure there's more than one value for catorder.  In fact there are 3.  But only 1 displays.

 

And I'm using a switch for this page, as it has a bunch of other stuff on it.  The mysql_query that updates the database will not run unless the form has been submitted.

[neilfurry]

<?php case "order":

    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';

    echo'<table><form method="post" action="?action=orderchange">';

    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {

        $name=$result['catname'];

        $catorder=$result['catorder'];

        $catid=$result['id'];

        ?>

        <tr><td><?php echo $name; ?></td><td><select name="order">

        <option><?php echo $catorder; ?></option>

        <?php $oquery=mysql_query("SELECT catorder FROM category ORDER BY catorder ASC");

        while($oresult=mysql_fetch_array($oquery)){

        $number=$oresult['catorder'];

           

            echo'<option value="'.$number.'">'.$number.'</option></td>';

    } echo'</select></td></tr>';

    }

    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';

    echo'</tr></table></center>';

break;

 

case "orderchange":

    $neworder=$_POST['order'];

    $query=mysql_query("SELECT id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {

        $catid=$result['id'];

        $update=mysql_query("UPDATE category SET catorder='$neworder' WHERE id='$catid'");

       

        }

break;

?>

 

Can you please try use this code i have just made couple of modifications:

 

<?php case "order":

    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';

    echo'<table><form method="post" action="?action=orderchange">';

    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {

        $name=$result['catname'];

        $catorder=$result['catorder'];

        $catid=$result['id'];

        ?>

        <tr><td><?php echo $name; ?></td><td><select name="order">

        <option><?php echo $catorder; ?></option>

        <?php $oquery=mysql_query("SELECT catorder FROM category ORDER BY catorder ASC");

        while($oresult=mysql_fetch_array($oquery)){

        $number=$oresult['catorder'];

           

            echo'<option value="'.$number.'">'.$number.'</option></td>';

    echo'</select></td></tr>'; }

    }

    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';

    echo'</tr></table></center>';

break;

 

case "orderchange":

    $neworder=$_POST['order'];

    $query=mysql_query("SELECT id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {

        $catid=$result['id'];

        $update=mysql_query("UPDATE category SET catorder='$neworder' WHERE id='$catid'");

       

        }

break;

?>

[MishieMoo]

That didn't do anything =(

[MishieMoo]

I managed to fix the first problem but the database doesn't update...

 

Now I get an error that there is a duplicate key.  The first two in the list don't get modified...just the third one and it goes straight to one.  I changed the code to see if something else could work but it still doesn't.

[teng84]

echo your query and post that here!

[MishieMoo]

Output: 111

 

BUT that doesn't actually do anything.  I never made it output 111 but it does that no matter what values I select.  Only the last row is being updated.  I have values 1, 2, 3 to work with now.  The first two rows don't update, but the third one does change to whatever value I select in the menu.

 

I modified the code to get the menu to display, so I'm reposting it.

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">';
    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");
    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
        ?>
        <tr><td><?php echo $name; ?><input type="hidden" value="<?php echo $catid; ?>"  name="id"/></td><td><select name="order">
        <option><?php echo $catorder; ?></option>
        <?php $oquery=mysql_query("SELECT MAX(catorder) as max_order, MIN(catorder) as min_catorder FROM category ORDER BY catorder ASC");
        $oresult=mysql_fetch_array($oquery);
        for($i=$oresult['min_catorder']; $i<=$oresult['max_order']; $i++) {
            
            echo'<option value="'.$i.'">'.$i.'</option>';
    } echo'</select></td></tr>';
    }
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';
    echo'</tr></table></center>';
break;

case "orderchange":
     $query=mysql_query("SELECT id FROM category ORDER BY catorder ASC");
     while($result=mysql_fetch_array($query)) {
    $neworder=$_POST['order'];
    $catid=$_POST['id'];
    $update=mysql_query("UPDATE category SET catorder='$neworder' WHERE id='$catid'")or die(mysql_error());
    echo $update;
        }
break;
?>

[teng84]

your logic seems to go nowhere do you have a link of this ??

 

or maybe you can put a comment on each line telling what it does

[MishieMoo]

I can't link because the page is not only member protected, but only admin accessible.

 

And if you want me to comment it to the nines then I will.  I don't understand what's so confusing...

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">'; //start the form and table
    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC"); //get some values to use in the form
    while($result=mysql_fetch_array($query)) {
        $name=$result['catname']; //define the variable for category name
        $catorder=$result['catorder']; //define variable for the current order
        $catid=$result['id']; //define variable for category id number
        //start the actual form
        ?>
        <tr><td><?php echo $name; ?><input type="hidden" value="<?php echo $catid; ?>"  name="id"/></td><td><select name="order">
        <option><?php echo $catorder; ?></option>
        <?php $oquery=mysql_query("SELECT MAX(catorder) as max_order, MIN(catorder) as min_catorder FROM category ORDER BY catorder ASC"); //find the minimum and maximum current order numbers
        $oresult=mysql_fetch_array($oquery); //fetch it
        /*in order to get the list to display I had to do something weird.
         The logic in this is simple.  $i starts out as the lowest number in the cloumn 'catorder'
         Then we loop through, adding one to $i until it equals max_order
         For each $i we then have one option in the select menu with the value of $i.
         This isn't the problem.
         */
        for($i=$oresult['min_catorder']; $i<=$oresult['max_order']; $i++) {
            
            echo'<option value="'.$i.'">'.$i.'</option>';
    } echo'</select></td></tr>'; //close select and table
    }
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>'; //add submit button to submit form
    echo'</tr></form></table></center>';  //close everything up
break;
?>

[teng84]

this line confused me

    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
        ?>
        <tr><td><?php echo $name; ?><input type="hidden" value="<?php echo $catid; ?>"  name="id"/></td><td><select name="order">
        <option><?php echo $catorder; ?></option>
        <?php $oquery=mysql_query("SELECT MAX(catorder) as max_order, MIN(catorder) as min_catorder FROM category ORDER BY catorder ASC");
        $oresult=mysql_fetch_array($oquery);
        for($i=$oresult['min_catorder']; $i<=$oresult['max_order']; $i++) {
            
            echo'<option value="'.$i.'">'.$i.'</option>';
    } echo'</select></td></tr>';
    }

 

does it mean you have morethan one combo box and your hidden fields is having the same name so upon submit you will have the last value of your hidden fields (the last value of your fetching will be the value of hidden field when you submit the form)

[MishieMoo]

Then how do I differentiate between fields? And if I differentiate how do I incorporate that into an update query, without having to manually add in one for every different value?

 

I have no clue how to do this so there's a reason why it's confusing.  I'm stumbling around in the dark here.

 

Which line?  The while loop?  That's so that everything's displayed.

[teng84]

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">';

echo '<tr><td></td><td><select name="order">';
    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");
$x=0;
    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
	if($x == 0){
		echo '<option> '.$catorder.'</option>';
	}
	$x++;
	echo'<option value="'.$catid.'|'.$catorder.'">'.$catid.'</option>';
    }
echo'</select></td></tr>';
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';
    echo'</tr></table></center>';
break;

 

try that that might work  now when you submit that the value of your combo box will be formated as

 

catid|catorder  the you will explode that so you will get the value eg..

 

$x =explode('|', $_POST['order']);

$catid = $x[0];

$catorder = $x[1];

 

hope that helps

[MishieMoo]

I see what you're doing but that's not what I need.  It helps, but I've never used an explode like this so it's a bit confusing.  I need one drop down for each category, so as of now a total of three, and I need that to auto increase with the amount of categories listed.  What you gave me just displays numbers and nothing else...

 

Still really confused.

 

EDIT: Okay I incorporated that into the code...but it doesn't do anything different.  This is what I have now

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">';

    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
       echo '<tr><td>'.$name.'</td><td><select name="order"><option>'.$catorder.'</option>';
$oquery=mysql_query("SELECT MAX(catorder) as max_order, MIN(catorder) as min_catorder FROM category ORDER BY catorder ASC"); //find the minimum and maximum current order numbers
        while($oresult=mysql_fetch_array($oquery)){
                for($i=$oresult['min_catorder']; $i<=$oresult['max_order']; $i++) {
            
	echo'<option value="'.$catid.'|'.$i.'">'.$i.'</option>';
    }
    }echo'</select>';
    }
echo'</td></tr>';
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';
    echo'</tr></table></center>';
break;

case "orderchange":
     $query=mysql_query("SELECT * FROM category ORDER BY catorder ASC"); //get all the rows from the category table
     while($result=mysql_fetch_array($query)) { //throw them into a while loop so this executes once for each row, this is the problem part
    $x =explode('|', $_POST['order']);
    $catid = $x[0];
    $catorder = $x[1];
    $update=mysql_query("UPDATE category SET catorder='$catorder' WHERE id='$catid'")or die(mysql_error()); //update!
        }
break;
?>

 

Even though I have the explode it still only updates the last one.

 

I'm thinking that you still don't understand.  So I'll explain.

 

I have three areas in my forum, General id #1, Admin id #2 and Bugs id #3.  As of right now their orders are this: General => 2, Admin => 1, Bugs =>3.  I'm trying to make the script so that it displays:

Topic           |             Order

Admin                    (dropdown)

General                  (dropdown)

Bugs                      (dropdown)

             (submit)

 

Where each dropdown has the value of each order, i.e. with three areas the numbers 1-3, four areas numbers 1-4 etc.  Order is a numerical number, where 1 is the first displayed, 2 is the second etc.

 

The html now displays perfectly, with everything being unique.  I'm thinking it has to do more with the name of the select menu...

[MishieMoo]

Got it.  I used the explode concept (how I didn't think of that I don't know) to create unique names for each select menu.  Now it updates perfectly =).  Simple xD

 

For anyone who needs a reference, I'm posting the code

<?php case "order":
    echo'<center>Order forum categories.  One is at the top, and the numbers ascend.';
    echo'<table><form method="post" action="?action=orderchange">';

    $query=mysql_query("SELECT catname, catorder, id FROM category ORDER BY catorder ASC");

    while($result=mysql_fetch_array($query)) {
        $name=$result['catname'];
        $catorder=$result['catorder'];
        $catid=$result['id'];
       echo '<tr><td>'.$name.'</td><td><select name="order'.$catid.'"><option>'.$catorder.'</option>';
$oquery=mysql_query("SELECT MAX(catorder) as max_order, MIN(catorder) as min_catorder FROM category ORDER BY catorder ASC"); //find the minimum and maximum current order numbers
        while($oresult=mysql_fetch_array($oquery)){
                for($i=$oresult['min_catorder']; $i<=$oresult['max_order']; $i++) {
            
	echo'<option value="'.$catid.'|'.$i.'">'.$i.'</option>';
    }
    }echo'</select>';
    }
echo'</td></tr>';
    echo'<td colspan=2 align="center"><input type=submit name=submit value=Submit /></td>';
    echo'</tr></table></center>';
break;

case "orderchange":
     $query=mysql_query("SELECT * FROM category ORDER BY catorder ASC"); //get all the rows from the category table
    while($result=mysql_fetch_array($query)) {//throw them into a while loop so this executes once for each row, this is the problem part
    $ex=explode('|', $_POST['order'.$catid]);
    $catid = $ex[0];
    $catorder = $ex[1];
    $update=mysql_query("UPDATE category SET catorder='$catorder' WHERE id='$catid'")or die(mysql_error()); //update!
        }
        if($update){echo'Sucess!  Order has been changed. <a href="forumadmin.php">Back</a>';
    }
break;
?>

 

Thanks!