Databases
StackOverflow2013
Postsdbo
POPHP MySql Update multiple rows
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP MySql Update multiple rows
    text
    copied!<p>Can't get this code working. the statement works fine directly on the table, just not when executed from the PHP file:</p> <pre><code> &lt;strong&gt;Multi Update&lt;/strong&gt;&lt;br&gt; &lt;?php $host="localhost"; // Host name $username="user_name"; // Mysql username $password="Password"; // Mysql password $db_name="dbname"; // Database name $tbl_name="tablename"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT mik1vm_product.product_id, mik1vm_product.product_sku, mik1vm_product.product_name, mik1vm_product.product_in_stock, mik1vm_product.product_in_stock_cardiff FROM $tbl_name"; $result=mysql_query($sql); // Count table rows $count=mysql_num_rows($result); ?&gt; &lt;table width="500" border="0" cellspacing="1" cellpadding="0"&gt; &lt;form name="form1" method="post" action=""&gt; &lt;tr&gt; &lt;td&gt; &lt;table width="500" border="0" cellspacing="1" cellpadding="0"&gt; &lt;tr&gt; &lt;td align="center"&gt;&lt;strong&gt;Product Id&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;SKU&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;Name&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;In Stock&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;In Stock Cardiff&lt;/strong&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php while($rows=mysql_fetch_array($result)){ ?&gt; &lt;tr&gt; &lt;td align="center"&gt;&lt;? $product_id[]=$rows['product_id']; ?&gt;&lt;? echo $rows['product_id']; ?&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_sku[]" type="text" id="product_sku" value="&lt;? echo $rows['product_sku']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_name[]" type="text" id="product_name" value="&lt;? echo $rows['product_name']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_in_stock[]" type="text" id="product_in_stock" value="&lt;? echo $rows['product_in_stock']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="&lt;? echo $rows['product_in_stock_cardiff']; ?&gt;"&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php } ?&gt; &lt;tr&gt; &lt;td colspan="4" align="center"&gt;&lt;input type="submit" name="Submit" value="Submit"&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/form&gt; &lt;/table&gt; &lt;?php // Check if button name "Submit" is active, do this if($Submit){ for($i=0;$i&lt;$count;$i++){ $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'"; $result1=mysql_query($sql1); } } if($result1){ header("location:test.php"); } mysql_close(); ?&gt; </code></pre> <p>The page loads fine, all data is loaded correctly. but just doesn't update the DB.</p> <p>Thanks in advance for any help.</p> <h1>UPDATE</h1> <p>OK So I guess I was a bit off the mark here, Obviously I'm new to PHP so please be patient.</p> <p>I modified the code to:</p> <pre><code> &lt;strong&gt;Multi Update&lt;/strong&gt;&lt;br&gt; &lt;?php $host=""; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name=""; // Database name $tbl_name=""; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT mik1vm_product.product_id, mik1vm_product.product_sku, mik1vm_product.product_name, mik1vm_product.product_in_stock, mik1vm_product.product_in_stock_cardiff FROM $tbl_name"; $result=mysql_query($sql); // Count table rows $count=mysql_num_rows($result); ?&gt; &lt;table width="500" border="0" cellspacing="1" cellpadding="0"&gt; &lt;form name="form1" method="post" action=""&gt; &lt;tr&gt; &lt;td&gt; &lt;table width="500" border="0" cellspacing="1" cellpadding="0"&gt; &lt;tr&gt; &lt;td align="center"&gt;&lt;strong&gt;Product Id&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;SKU&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;Name&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;In Stock&lt;/strong&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;strong&gt;In Stock Cardiff&lt;/strong&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php while($rows=mysql_fetch_array($result)){ ?&gt; &lt;tr&gt; &lt;td align="center"&gt;&lt;? $product_id[]=$rows['product_id']; ?&gt;&lt;? echo $rows['product_id']; ?&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_sku[]" type="text" id="product_sku" value="&lt;? echo $rows['product_sku']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_name[]" type="text" id="product_name" value="&lt;? echo $rows['product_name']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_in_stock[]" type="text" id="product_in_stock" value="&lt;? echo $rows['product_in_stock']; ?&gt;"&gt;&lt;/td&gt; &lt;td align="center"&gt;&lt;input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="&lt;? echo $rows['product_in_stock_cardiff']; ?&gt;"&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php } ?&gt; &lt;tr&gt; &lt;td colspan="4" align="center"&gt;&lt;input type="submit" name="Submit" value="Submit"&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/form&gt; &lt;/table&gt; &lt;?php // Check if button name "Submit" is active, do this if(isset($_POST['Submit']) &amp;&amp; $_POST['Submit'] == 'Submit'){ for($i=0;$i&lt;$count;$i++){ $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'"; $result1=mysql_query($sql1); } } if($result1){ header("location:test.php"); } mysql_close(); ?&gt; </code></pre> <p>Which is now giving errors:</p> <p>On initial page load: e: Undefined variable: sql1 in test.php on line 74</p> <p>But when the page is submitted there are loads:</p> <p>Notice: Undefined variable: product_sku in test.php on line 70</p> <p>Notice: Undefined variable: product_name in test.php on line 70</p> <p>Notice: Undefined variable: product_in_stock in test.php on line 70</p> <p>Notice: Undefined variable: product_in_stock_cardiff in test.php on line 70</p> <p>The list goes on......</p> <p>Final line:</p> <p>Notice: Undefined variable: product_in_stock_cardiff in /test/test.php on line 70 UPDATE mik1vm_product SET product_sku='', product_name='', product_in_stock='' , product_in_stock_cardiff='' WHERE product_id='74'</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload