Updating Multiple Rows

[carbonxps]

Hi All,

 

Can't get this code working. the statement works fine directly on the table, just not when executed from the PHP file:

 

<strong>Multi Update</strong><br>

<?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);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Product Id</strong></td>
<td align="center"><strong>SKU</strong></td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>In Stock</strong></td>
<td align="center"><strong>In Stock Cardiff</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $product_id[]=$rows['product_id']; ?><? echo $rows['product_id']; ?></td>
<td align="center"><input name="product_sku[]" type="text" id="product_sku" value="<? echo $rows['product_sku']; ?>"></td>
<td align="center"><input name="product_name[]" type="text" id="product_name" value="<? echo $rows['product_name']; ?>"></td>
<td align="center"><input name="product_in_stock[]" type="text" id="product_in_stock" value="<? echo $rows['product_in_stock']; ?>"></td>
<td align="center"><input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="<? echo $rows['product_in_stock_cardiff']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$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();
?>

 

The page loads fine, but just doesn't update the DB.

 

Thanks in advance for any help.

[MMDE]

Try this:

error_reporting(E_ALL);
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$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]'";
	echo $sql1;
	$result1=mysql_query($sql1) or die(mysql_error());
}
}

It's called proper debugging.

[carbonxps]

Thanks for that, I thought debugging had to be enable on the server in php.ini. I am very new to php obviously.....

 

At least I'm getting some errors now:

 

Notice: Undefined variable: Submit in test.php on line 68

 

Notice: Undefined variable: result1 in test.php on line 76

 

I also thought variables were declared as they were used???

 

Thanks again.

[MMDE]

Thanks for that, I thought debugging had to be enable on the server in php.ini. I am very new to php obviously.....

 

At least I'm getting some errors now:

 

Notice: Undefined variable: Submit in test.php on line 68

 

Notice: Undefined variable: result1 in test.php on line 76

 

I also thought variables were declared as they were used???

 

Thanks again.

While you usually don't have to declare what type it is, then this is wrong way to check if it is set:

if($Submit){

Here's how you do that:

if(isset($Submit)){

 

You should enable error reporting at the start of your file:

error_report(E_ALL);

 

Do also notice I added die(mysql_error()) at the end of your mysql_query so that you can actually see if there's any mysql errors, and understand what the syntax errors are much easier.

[carbonxps]

Thanks so much for your help. I tried this and now I get an error:

 

Fatal error: Call to undefined function is_set() in test.php on line 68

[MMDE]

Thanks so much for your help. I tried this and now I get an error:

 

Fatal error: Call to undefined function is_set() in test.php on line 68

sorry, that was a typo. PHP usually add a underscore between each word in function names, but they are not very consistent about it.

Should have been isset instead.

[carbonxps]

Ah cool, I corrected this and got a similar error on $result1 so I changed that to:

 

if(isset($result1))

 

So no errors now, but the table still isn't updating. As soon as I hit submit any changes lost and the page re-loads the original data.

 

Any ideas? Sorry I'm very new to php...

[MMDE]

When is $submit set by the way?

PHP runs on the server side. It can not see what the user does unless they send some data to the server.

 

When the user submit post data with your form, you can access it by using $_POST.

 

For example:

if(isset($_POST['product_sku'])){
foreach($_POST['product_sku'] AS $product_sku){
	echo $product_sku;
}
}

[Psycho]

The function will never run because the variable $submit is never defined. I think you mean to use

if(isset($_POST['Submit']))