update multiple records

[busnut]

G'day, i've found this script on the net to update multiple records, but for some unknown reason, it just ain't updating My php & mysql knowledge is very limited...

 

This is where I found this code, and edited the fields to my requirements. http://www.phpeasystep.com/mysqlview.php?id=10

 

Any help is much appreciated.

<?php
$host="localhost"; // Host name 
$username="bla"; // Mysql username 
$password="bla"; // Mysql password 
$db_name="bla"; // Database name 
$tbl_name="products"; // 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 * 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>Id</strong></td>
<td align="center"><strong>Item</strong></td>
<td align="center"><strong>Price 1</strong></td>
<td align="center"><strong>Price 2</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="item[]" type="text" id="item" value="<? echo $rows['item']; ?>"></td>
<td align="center"><input name="price1[]" type="text" id="price1" value="<? echo $rows['price1']; ?>"></td>
<td align="center"><input name="price2[]" type="text" id="price2" value="<? echo $rows['price2']; ?>"></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 item='$item[$i]', price1='$price1[$i]', price2='$price2[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:update.php");
}
mysql_close();
?>

[waterssaz]

can't see anything obvious after a quick sacn over the code. Do you have any errors outputting on the screen? :-)

[Mark Baker]

if($Submit){

So where's $Submit being set?

[busnut]

At the moment, this is the error I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''18' at line 1

 

I did a search of this forum, and have found several other users posting similar issues of this same script it appears, so there must be something somewhere in the script preventing the database updating the records. So if this issue seems to be the norm with this particular script, can anyone make something that will do what we are trying to achieve, and thats have a form with multiple records from a table that can be edited at once rather than having to individually edit each record?

[Mark Baker]

What version of PHP are you running? and is register_globals on or off?

[busnut]

PHP: Version 5.2.8

Register Globals: On

[mikesta707]

you can't use php arrays in mysql queries like that. try

 

$sql1="UPDATE $tbl_name SET item='" . $item[$i] . "', price1='" . $price1[$i] . " ', price2='" . $price2[$i] . "' WHERE id='" . $id[$i]. "'";

 

I believe you can also surround them with {}, like so

 

$sql1="UPDATE $tbl_name SET item='{$item[$i]}', price1='{$price1[$i]}', price2='{$price2[$i]}' WHERE id='{$id[$i]}'";

 

but about the latter, I am not sure about syntactily(sp?) so I would use the first example.

 

hope that helps!

[busnut]

Did that, and it deleted all the info in those fields

[Maq]

you can't use php arrays in mysql queries like that. try

 

Yes you can.

 

PHP: Version 5.2.8

Register Globals: On

 

You should have registered_globals off for multiple reasons primarily because of security reasons and the fact that it has been deprecated in PHP6.0.

[Mark Baker]

Recommendation #1: set register globals off

Recommendation #2: Replace $Submit, $item, $price1, $price2 and $id with the $_POST superglobal (e.g. $_POST['Submit'], $_POST['item'], etc)

Recommendation #3: escape these values before trying to insert them in your database

Recommendation #4: if price1 and price2 are numeric columns in the database, don't put quotes around the values in your UPDATE statement

Recommendation #5: echo out $sql1 before executing the mysql_query

[mikesta707]

you can't use php arrays in mysql queries like that. try

 

Yes you can.

 

 

Can you? I have ran into problems doing this.

 

also if it deleted all the info (or rather overwrote it with empty values) perhaps your arrays have empty values.

 

sorry for the incorrect information!

[busnut]

I thought I was lost when posting the original thread, now im totally lost. I can do a couple of the items suggested, but turning globals off, no idea! Escaping values, not sure on that one and echoing the sql, also not sure. As I said, my knowledge is very limited.

 

Does anybody have a pre-made script or know of where one can be found on the net, cause i've tried searching...

[mikesta707]

escaping the values : http://us.php.net/manual/en/function.mysql-escape-string.php

 

the function is called mysql_escape_string(). use that to gaurd against sql injection.

 

echoing the sql just means to echo the $sql variable to make sure it is doing what you want it to.

 

like

echo $sql;