busnut Posted August 7, 2009 Share Posted August 7, 2009 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(); ?> Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/ Share on other sites More sharing options...
waterssaz Posted August 7, 2009 Share Posted August 7, 2009 can't see anything obvious after a quick sacn over the code. Do you have any errors outputting on the screen? :-) Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892903 Share on other sites More sharing options...
Mark Baker Posted August 7, 2009 Share Posted August 7, 2009 if($Submit){ So where's $Submit being set? Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892907 Share on other sites More sharing options...
busnut Posted August 7, 2009 Author Share Posted August 7, 2009 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? Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892914 Share on other sites More sharing options...
Mark Baker Posted August 7, 2009 Share Posted August 7, 2009 What version of PHP are you running? and is register_globals on or off? Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892919 Share on other sites More sharing options...
busnut Posted August 7, 2009 Author Share Posted August 7, 2009 PHP: Version 5.2.8 Register Globals: On Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892930 Share on other sites More sharing options...
mikesta707 Posted August 7, 2009 Share Posted August 7, 2009 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! Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892934 Share on other sites More sharing options...
busnut Posted August 7, 2009 Author Share Posted August 7, 2009 Did that, and it deleted all the info in those fields Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892942 Share on other sites More sharing options...
Maq Posted August 7, 2009 Share Posted August 7, 2009 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. Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892945 Share on other sites More sharing options...
Mark Baker Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892948 Share on other sites More sharing options...
mikesta707 Posted August 7, 2009 Share Posted August 7, 2009 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! Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892958 Share on other sites More sharing options...
busnut Posted August 7, 2009 Author Share Posted August 7, 2009 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... Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892975 Share on other sites More sharing options...
mikesta707 Posted August 7, 2009 Share Posted August 7, 2009 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; Link to comment https://forums.phpfreaks.com/topic/169220-update-multiple-records/#findComment-892980 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.