Update more than one record

[lawler_09]

Hi guys,

 

Doing a small project and i'm pretty new to php/mysql so hoping someone can help. Also i wasn't sure if this should be in php section or mysql, so apologises if it's in the wrong section.

 

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 text box to put a number in.

 

Sooo like

 

(ID)          (Model)          (current stock level)          (new stock level)

1              AAA                3                                      [ ]

2              BBB                1                                      [ ]

3              CCC                4                                      [ ]

 

A user will then put a new number in the 'new stock level' text box and when it gets submitted the new number replaces the number in 'current stock level'

 

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?

 

 

appreciate any help!

 

thanks!

[scootstah]

If you are using the same data for each row you can use WHERE ID IN(1,2,3) to update rows with the ID 1, 2, and 3. If you want different data for each update though, you will have to use multiple queries.

[lawler_09]

If you are using the same data for each row you can use WHERE ID IN(1,2,3) to update rows with the ID 1, 2, and 3. If you want different data for each update though, you will have to use multiple queries.

 

how do you mean same data?

 

the numbers going in to the 'new stock level' would be different every time as well as which row is being update. so the first 3 rows might be updated and every other row ignored.

 

also the id numbers are slightly different to just 1,2,3, and rows would change all the time as models come and go.

[scootstah]

If you wanted to say, change the new stock level of rows 1 and 3 to "17" then you could do that in one query. If you wanted to change the new stock level of row 1 to "17" and the new stock level of row 3 to "9", you would need two queries.

[lawler_09]

If you wanted to say, change the new stock level of rows 1 and 3 to "17" then you could do that in one query. If you wanted to change the new stock level of row 1 to "17" and the new stock level of row 3 to "9", you would need two queries.

 

it could be different every time. for example, tomorrow there might be 10 records displayed and you want to change to stock level of row 1,3,5,8 and 9 with the number in the new stock level, and ignore rows 2,4,6,7 and 10 as they don't have anything in the new stock level text box.

 

but next week there may be 20 records/rows returned and you want to change rows 1 and 2 and ignore the rest of the rows.

 

thanks for your help so far as well!

[scootstah]

You can update multiple rows with no problem, you can use WHERE ... IN(...).

 

For example, let's say you want to update rows 1, 2, and 3 to have a new stock level of 7. You could do...

UPDATE stocks SET level = 7 WHERE ID IN (1,2,3)

 

The problem is, if you want each updated row to have a different stock level, then you need multiple queries. If you wanted row 1 to have a level of 7, row 2 to have a level of 9, and row 3 to have a level of 13, you would have to do this:

UPDATE stocks SET level = 7 WHERE ID = 1;
UPDATE stocks SET level = 9 WHERE ID = 2;
UPDATE stocks SET level = 13 WHERE ID = 3;

[lawler_09]

I understand that i'll need mutliple queries and your train of thought. The issue i'm having is that the id and new stock value will be different every single time.

 

I'll attempt to show you what i have so far (excuse the amateur coding, like i said im new aha)

 

<?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['item_code']."</td>"; 
          echo "<td style=\"text-align: center;\">".$row['item_colour']."</td>";

                                    
                                            if ($row['item_stock']=="2")
                                                    echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['item_stock']."</b>"."</font></td>";
                                            
                                            elseif ($row['item_stock']=="1")
                                                    echo "<td style=\"text-align: center;\"><font color =\"#d50031\">"."<b>".$row['item_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['item_stock']."</b>"."</font></td>";
                                            
                                            ?><form class="cmxform" id="signupForm" method="post" action="../../php/query.php"> <?php
                                            
            echo "<td style=\"text-align: center;\"><input type=\"text\" name=\"item_new_stock_total\" style=\"width:20px\"></td>";

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

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

    </form>

 

so i'd pass the value in the text box for item_new_stock_total and the id to another php page to do the query.

 

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

 

 

Hope this was easy enough to understand!!

[scootstah]

What you can do is change the name of your input so that it is an array. So change it to something like this:

<input type=\"text\" name=\"item_new_stock_total[" . $row['item_id'] . "]\" style=\"width:20px\">

 

Now if you look at the $_POST data for "item_new_stock_total", it will be an array and the keys will be the ID of the item you want to update. So you can simply loop through the array and update the rows.

$items = $_POST['item_new_stock_total'];

foreach($items as $key => $val)
{
    mysql_query("UPDATE stocks SET level = '$val' WHERE ID = '$key'");
}

[lawler_09]

What you can do is change the name of your input so that it is an array. So change it to something like this:

<input type=\"text\" name=\"item_new_stock_total[" . $row['item_id'] . "]\" style=\"width:20px\">

 

Now if you look at the $_POST data for "item_new_stock_total", it will be an array and the keys will be the ID of the item you want to update. So you can simply loop through the array and update the rows.

$items = $_POST['item_new_stock_total'];

foreach($items as $key => $val)
{
    mysql_query("UPDATE stocks SET level = '$val' WHERE ID = '$key'");
}

 

thanks for all your help so far.

 

ive just tried that now and it just ignores it.

 

should i be changing $val + $key to something else?

 

 

[silkfire]

1. Put an input hidden in your table that holds the id value so you can use it in your query later.

2. Escape your input data (even id) with mysql_real_escape.

3. Use "echo mysql_error()" function to show the error in your query.

[lawler_09]

1. Put an input hidden in your table that holds the id value so you can use it in your query later.

2. Escape your input data (even id) with mysql_real_escape.

3. Use "echo mysql_error()" function to show the error in your query.

 

thanks for your help as well.

 

in my form i have

 

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

 

then in the page it posts to i have:

 

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

$items = $_POST['item_new_stock_total'];foreach($items as $key => $val){    mysql_query("UPDATE items SET item_stock = '$val' WHERE item_id = '$key'");}

echo mysql_error();

 

i dont know how to do the escaping you mentioned in step 2.

 

apologises as im really new to php.

[silkfire]

Your form should be in HTML, no need to echo it out, even if you loop it. Plus you can hide your hidden field in the same cell as the text field. Multiple form fields are to have empty brackets [] at the end.

 

<td style="text-align: center;">
   <input type="hidden" name="item_id[]">
   <input type="text" name="item_new_stock_total[]" style="width:20px" value="<?echo [$THE VALUE READ FROM DATABASE]?>">
</td>

 

And in your processing page:

 

require_once 'functions.php';

foreach ($_POST['item_new_stock_total'] as $key => $val) {
   $val = mysql_real_escape_string($val);
   $id  = mysql_real_escape_string($_POST['item_id'][$key]);

   mysql_query("UPDATE items SET item_stock = '$val' WHERE item_id = '$id'");
}

[lawler_09]

Your form should be in HTML, no need to echo it out, even if you loop it. Plus you can hide your hidden field in the same cell as the text field. Multiple form fields are to have empty brackets [] at the end.

 

<td style="text-align: center;">
   <input type="hidden" name="item_id[]">
   <input type="text" name="item_new_stock_total[]" style="width:20px" value="<?echo [$THE VALUE READ FROM DATABASE]?>">
</td>

 

And in your processing page:

 

require_once 'functions.php';

foreach ($_POST['item_new_stock_total'] as $key => $val) {
   $val = mysql_real_escape_string($val);
   $id  = mysql_real_escape_string($_POST['item_id'][$key]);

   mysql_query("UPDATE items SET item_stock = '$val' WHERE item_id = '$id'");
}

 

thanks again for your help.

 

few things.

 

in the text box on the form i'm wanting it to be blank and the user only puts a value in if they want to change the stock level. if no value is put in the text box then it is ignored. as far as i understand your code your wanting me to echo in the stock value, which is already displayed in the previous column.

 

but i tried what you put anyway, and it just cycled through the query, but didnt work. i put in echo mysql_error(); after the query and it returned 'database not selected', and cant figure out why

[silkfire]

Ah that has nothing to so with your query, mate.

 

Before performing operations you need to select the database that contains the tables. Like this:

 

mysql_select_db('NAME OF DATABASE')

 

I hope you know what the name is. Put this statemenet directly after the mysql_connect() statement.

[lawler_09]

Ah that has nothing to so with your query, mate.

 

Before performing operations you need to select the database that contains the tables. Like this:

 

mysql_select_db('NAME OF DATABASE')

 

I hope you know what the name is. Put this statemenet directly after the mysql_connect() statement.

 

it just seems odd cause i use the include functions.php to connect to the database, which works perfectly on all the other pages

[silkfire]

How do the queries look there?

[lawler_09]

How do the queries look there?

 

<?php

function doQuery($query) {
	$result = "";
	$conn = @mysql_connect('xxx', 'xxx', 'xxx');
	if($conn==false) {
		$result = false;
	} else {
		$db = mysql_select_db("xxx");
		if($db==false) {
			$result = false;
		} else {
			$rows = mysql_query($query);
			if($rows==false) {
				$result = false;
			} else {
				$result = $rows;
			}			
		}
	}
	return $result;
}

?>

 

works fine on every other page when connectiong to db