Page 1 of 1

help with updating a mysql table using a php form

Posted: Tue Jun 28, 2005 11:25 am
by rek
inv.php has a form that posts to itself attempting to update table inv, where category 'no' is the auto-increment primary category. The problem I am having is getting the looped form to post any row past the first row. I am a little familiar with creating arrays, but I don't know how to make an array that accounts for multiple random rows from a mysql table. There are about 500 rows in this table, and I am able to view them all in this form, but I can't update them. Does anyone have any suggestions?

Here is the relevant code from inv.php:

Code: Select all

if(isset($_POST['update'])) 
        { 
            $itemno = $_POST['itemno']; 
            $lotshow = $_POST['lotshow']; 
            $qty = $_POST['qty']; 
            $size = $_POST['size']; 
            $conc = $_POST['conc']; 
            $vol = $_POST['vol']; 
            $location = $_POST['location']; 
            $notes = $_POST['notes']; 
             
            $no = $_POST['no']; 
                         
            require_once('mysqladmin.php'); 
            $query = "UPDATE inv SET itemno='$itemno', lotshow='$lotshow',
            qty='$qty', size='$size', conc='$conc', vol='$vol',
            location='$location', notes='$notes' WHERE no='$no'"; 
            $result = mysql_query($query); 
                     
         
        } 
        require_once('mysqladmin.php'); 
        $query = "SELECT * FROM inv WHERE location != ' ' ORDER BY location"; 
        $result = mysql_query ($query); 
     
        ?> 
         
        <form action=inv.php method=post> 
        <center><input type=submit name=update value=Update></center> 
        <table border=1 bordercolor=000000 cellpadding=0 cellspacing=0> 
        <tr bgcolor=00ff00><td>Item</td> 
            <td>Lot</td> 
            <td>Qty.</td> 
            <td>Size</td> 
            <td>Conc.</td> 
            <td>Vol.</td> 
            <td>Location</td> 
            <td>notes</td> 
        </tr> 
         
         
        <?php 

         
        while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) 
         
        { 
        ?><input type=hidden name=no value="<?php echo "$row[no]";?>"> 
        <tr><td><input type=text name=itemno value="<?php echo "$row[itemno]";?>"></td> 
            <td><input type=text name=lotshow value="<?php echo "$row[lotshow]";?>" size=7></td> 
            <td><input type=text name=qty value="<?php echo "$row[qty]";?>" size=3></td> 
            <td><input type=text name=size value="<?php echo "$row[size]";?>" size=5></td> 
            <td><input type=text name=conc value="<?php echo "$row[conc]";?>" size=6></td> 
            <td><input type=text name=vol value="<?php echo "$row[vol]";?>" size=5></td> 
            <td><input type=text name=location value="<?php echo "$row[location]";?>" size=7></td> 
            <td><input type=text name=notes value="<?php echo "$row[notes]";?>"size=25></td> 
             
        </tr> 
        </form> 
        <?php 
     
        }
[/i]

Posted: Tue Jun 28, 2005 11:38 am
by Burrito
you can only update one row at a time with sql queries.

if you want to update multiple rows, you're going to have to put the update in a loop (probably easiest to use a while on a new query on your action page).

note though, with the while loop you have on your form page, you're going to have mulitple fields of the same name. You really should append the row id to the field name so they are distinct for every row:

ex:

Code: Select all

<input type="text" name="itemno<?=$row['no'];?>" value="<?=$row['itemno'];?>">
then on your action page you just run the same query you used to generate those values on your form page and simply run your update within your result loop and add the value of 'no' to your dynamically created field names.

ex:

Code: Select all

$query = "update inv set itemno = '".$_POST['itemno'.$row['no']]."'";
//etc

Posted: Tue Jun 28, 2005 12:22 pm
by rek
hmm... that seems to be making my fields blank...
What's the reason for switching value="<?php echo "$row[itemno]";?>" to value="<?=$row['itemno'];?>"

Posted: Tue Jun 28, 2005 12:24 pm
by Burrito
that's just a shorthand way of writing the same thing you had. If you're not seeing anything, your server might not be configured to work that way...go back to the way you had it.