[RESOLVED] Updating more than one row problem!

[lawler_09]

Hi guys,

Doing a small project and i'm pretty new to php/mysql so hoping someone can help.

Basically...

I have a form with a table in it with the fields (id, model, current stock level, new stock level)

The table is populated using a mysql query to get the data from 1 table in a database.

In the new stock column there's a simple blank text box to put a number in.

so for example the page might look:

(ID) (Model) (current stock level) (new stock level)
1 AAA 3 [ ]
2 BBB 1 [4]
3 CCC 4 [ ]
4 DDD 4 [2]
5 EEE 5 [ ]

[SUBMIT BUTTON]

so when i click the submit button it updates the rows with id 2 and 4 with replace the current stock levels with the value in the new stock level.

but the week after the same page might look like this:

(ID) (Model) (current stock level) (new stock level)
4 DDD 3 [1]
6 FFF 1 [ ]
7 GGG 4 [ ]
8 HHH 4 [ ]
9 III 5 [2]
11 GHF 4 [ ]
13 DFE 4 [3]

[SUBMIT BUTTON]

so when i click the submit button this time it updates the rows with id 4, 9 and 13 with the values, each time ignoring the rows with dont have anything entered in the new stock level column

Basically i know how to do it if it was just the one record, but how do i get it to update all the ones with numbers in the new stock level column at the same time, and ignore it if it doesn't have anything entered in the text box?

the code for my form is:

    <?php

$query = "SELECT * FROM items";

$result = doQuery($query);

if($result==false) {
    echo mysql_error();

    } else {
        $rowcount = 1;
        while($row=mysql_fetch_assoc($result)) 	{

                echo "<tr>";
		echo "<td style=\"text-align: center;\">".$row['item_id']."</td>"; 

		echo "<td style=\"text-align: center;\">".$row['item_model']."</td>"; 
	   	echo "<td style=\"text-align: center;\">".$row['cart_colour']."</td>";


                                        if ($row['item_stock']=="2")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['cart_stock']."</b>"."</font></td>";

                                        elseif ($row['item_stock']=="1")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['cart_stock']."</b>"."</font></td>";
                                        elseif ($row['item_stock']=="0")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>"."Out Of Stock :("."</b>"."</font></td>";

                                        else
                                                echo "<td style=\"text-align: center;\"><font color =\"#009933\">"."<b>".$row['cart_stock']."</b>"."</font></td>";

                                        ?>

                                        <form class="cmxform" id="signupForm" method="post" action="../../php/test123.php"> 

        <td style="text-align: center;">   
            <input type="hidden" name="cart_id" value="<?php echo $row['item_id']?>">   
            <input type="text" name="item_new_stock_total" style="width:20px">
        </td>

        <?php
        echo "</tr>";
        $rowcount++;


}
}	
?>

appreciate any help!

thanks!

[bradgrafelman]

Here's how I would do it:

1. Get rid of the hidden 'cart_id' element (it's useless anyway).

2. Change your 'item_new_stock_total' text inputs to have a name that looks like 'item_new_stock_total[123]' (where 123 is the item_id from the D😎.

   That way, when you submit the form, $POST['item_new_stock_total'] will be an array where the keys are the IDs (item_id) and the values are the user-supplied values from the textboxes.
   

3. On the processing page, loop through $_POST['item_new_stock_total'] in a [man]foreach/man loop (using the '$id => $value' syntax so that you can access both the item_id and the new stock quantity value). If the supplied value is non-empty (e.g. the user actually entered something in that particular textbox), execute an UPDATE query to update the total value in the DB for that item with the newly supplied value from the form.
   
   (Don't forget to first sanitize the user-supplied input before placing it in the query!)

EDIT: Also, move the <form> element to some place outside of the loop so that you only have a single form for all rows.

[lawler_09]

many thanks for your reply! this sounds pretty much what i want.

i'm getting the current error - Incorrect integer value: '' for column 'cart_stock' at row 1

this is the code i've got:

    <div id="container1">

<form class="cmxform" id="signupForm" method="post" action="../../php/test123.php">

<table width="90%"  cellspacing="0" bordercolor="#000000" height="50%" align="center" cellpadding="3">

<tr style="background-color:#d50031; font-weight:bold; color:#FFF; text-align: center;"><td>Short Number</td><td>Long Number</td><td>Cart Colour</td><td>Current Stock Level</td><td>New Stock Level</td></tr>

<?php

$query = "SELECT * FROM cartridges";

$result = doQuery($query);

if($result==false) {
    echo mysql_error();

    } else {
        $rowcount = 1;
        while($row=mysql_fetch_assoc($result)) 	{

            $cart_id = $row['cart_id'];

                echo "<tr>";

        echo "<td style=\"text-align: center;\">".$row['cart_short']."</td>"; 
		echo "<td style=\"text-align: center;\">".$row['cart_long']."</td>"; 
	   	echo "<td style=\"text-align: center;\">".$row['cart_colour']."</td>";


                                        if ($row['cart_stock']=="2")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['cart_stock']."</b>"."</font></td>";

                                        elseif ($row['cart_stock']=="1")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['cart_stock']."</b>"."</font></td>";
                                        elseif ($row['cart_stock']=="0")
                                                echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>"."Out Of Stock :("."</b>"."</font></td>";

                                        else
                                                echo "<td style=\"text-align: center;\"><font color =\"#009933\">"."<b>".$row['cart_stock']."</b>"."</font></td>";

                                        ?> 

        <td style="text-align: center;">     
            <input type="text" name="item_new_stock_total[<?php echo $cart_id ?>]" style="width:20px">
        </td>

        <?php
        echo "</tr>";
        $rowcount++;


}
}	
?>
    </table>

<p align="right" style="padding-right: 49px;">
        <input class="submit" type="submit" value="Submit" id="button_style" />
    </p>

</form>
</div>

and then my processing page has the follwing on:

<?php

extract($_POST);
include("functions.php");

foreach ($_POST['item_new_stock_total'] as $key => $val) {


$query = "UPDATE cartridges SET cart_stock = '$val' WHERE cart_id = '$key'";}

$result = doQuery($query);

if($result==false) {
           echo mysql_error();
    }

{
                header("Location:../index.php");
    }


?>

again many thanks for your help so far!!!

[lawler_09]

i echoed the query as well and it gave this

Incorrect integer value: '' for column 'cart_stock' at row 1UPDATE cartridges SET cart_stock = '' WHERE cart_id = '0000005993955001'

[Weedpacket]

First of all, lose this:

lawler_09 wrote:

extract($_POST);

It doesn't do what you think it does.

Have a look at your query:

UPDATE cartridges SET cart_stock = '' WHERE cart_id = '0000005993955001'

That will happen if $_POST['item_new_stock_total']['0000005993955001'] was empty when the form was submitted (what did that form field contain?)

What you will need to do is look at $val and skip updating the cart_stock whenever it's an empty string. Something along the lines of

if(trim($val) == '') continue;
$val = (int)$val;

And you shouldn't be using the [man]MySQL[/man] extension. It's been started on the path towards deprecation and eventual removal, and the official word is that it should not be used for new development.

[lawler_09]

many thanks for your contribution.

after changing my code to match your suggestions

<?php

include("functions.php");

foreach ($_POST['item_new_stock_total'] as $key => $val) {

if(trim($val) == '') continue;
$val = (int)$val;


$query = "UPDATE cartridges SET cart_stock = '$val' WHERE cart_id = '$key'";}

$result = doQuery($query);

if($result==false) {
           echo mysql_error();
    }

{
                header("Location:../index.php"); 

}


?>  

after doing this it works to an extent.

if i put a number in say 4 boxes, it only updates the number in the last filled in box, and ignores the previous filled in ones.

[Derokorian]

The reason you shouldn't use extract on $POST is stated on the manual page:

Do not use extract() on untrusted data, like user input (i.e. $_GET, $_FILES, etc.). If you do, for example if you want to run old code that relies on register_globals temporarily, make sure you use one of the non-overwriting extract_type values such as EXTR_SKIP and be aware that you should extract in the same order that's defined in variables_order within the php.ini.

[lawler_09]

Derokorian;10996630 wrote:

The reason you shouldn't use extract on $_POST is stated on the manual page:

interesting. thanks for that.

just been going by what i was taught at college. But i will try and change my ways from now on!

Thanks for the heads up!

[Derokorian]

That's why I love the PHP manual. sometimes best practice changes, and the manual is pretty good about telling you when to stop using something (such as register globals, or magic quotes)

[lawler_09]

luckily college was only 2 years ago and i'm only starting to get back into it again now, so i will definitely start to look into the manual more!

thanks again! much appreciated!

[bradgrafelman]

You've got two major problems at the moment:

1. You need to properly indent your code...

   i   m p r  o      perl    y    sp a  c ed   c     ode is h  ar     d t o     rea       d!

2. If you had properly indented your code, you might've been able to spot the second problem much easier. Take a look at your foreach() loop and subsequent code once it's been properly indented:

   foreach ($_POST['item_new_stock_total'] as $key => $val)
   { 
       if(trim($val) == '')
           continue;
   
   $val = (int)$val; 
   
   $query = "UPDATE cartridges SET cart_stock = '$val' WHERE cart_id = '$key'";
   }
   
   $result = doQuery($query);

   Notice the problem now? You're defining $query inside the loop but never executing it as a SQL query until after the loop ends.

EDIT: Also note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks (see: [man]database.sql-injection[/man]) or just plain SQL errors. Instead, it must first be sanitized such as with a function like [man]mysql_real_escape_string/man (for string data) or by using prepared statements.

Also note that numerical values in SQL queries should not be surrounded with quotes (same as if it were PHP code).

[lawler_09]

1. Apologies for that. My bad habits, i tend to clean code up once i get it working, but that was one of my downfalls on this occasion!

2. I did see my problem, and my problem is now fixed, and it is fully working how i would like it to work!

I would like to more than thank you for all your help and guidence on this, and learning me a thing of two in the process! Greatly appreciated!

Also like to thank Derokorian for making me aware of the manual!

Absolutely brilliant thank you!

[Weedpacket]

lawler_09 wrote:

My bad habits, i tend to clean code up once i get it working,

It is indeed a bad habit: having clean code is mainly of benefit when it's not working. How clean the code is doesn't matter to the parser, only to the human who has to change what it's doing. For Exhibit A, see bradgrafelman's second point. It's exactly the same code as far as the parser is concerned.

[Derokorian]

More case in point, the following snippets are identical to the parser:

if(sizeof($var)>0){foreach($var as $k=>$v){print("$k: $v<br>");}}else{print('No results');}print('End');

if( sizeof($var) > 0 ) {
   foreach( $var as $k => $v ) {
      print("$k: $v<br>");
   }
} else {
   print('No results');
}
print('End');

Notice how much easier the second snippet is to understand? 😛 that's not even a deeply nested example!