Benbo1980 Posted July 16, 2008 Share Posted July 16, 2008 Hi there, I have successfully setup a page using a tutorial (shown http://www.phpeasystep.com/mysql/10.html) It's all working fine as it stands. I have now added another column to the database that can either be 1 or 0 and I would like to be able to change it on the update page using a checkbox for each record. The code I tried was as follows: <strong>Update multiple rows in mysql</strong><br> <?php $host="localhost"; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $tbl_name="test_mysql"; // 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>Name</strong></td> <td align="center"><strong>Lastname</strong></td> <td align="center"><strong>Email</strong></td> <td align="center"><strong>On / Off</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="name[]" type="text" id="name" value="<? echo $rows['name']; ?>"></td> <td align="center"><input name="lastname[]" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td> <td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td> <td align="center"><input name="ONOFF[]" type="checkbox" id="ONOFF" value="1" <?php if ($rows['ONOFF'] ==1) { echo "checked";} else {} ?> ></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 name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]', ONOFF='$ONOFF[$i]' WHERE id='$id[$i]'"; $result1=mysql_query($sql1); } } if($result1){ header("location:update_multiple.php"); } mysql_close(); ?> What happens is weird - no matter which checkboxes are ticked, the database appears to update the wrong records. For example, if I had checked every other record (4 in total) and clicked submit, the database would have simply changed the top four records, not necessarily the ones I had originally selected. Any ideas?! ??? Thanks, Ben Quote Link to comment Share on other sites More sharing options...
DoddsAntS Posted July 16, 2008 Share Posted July 16, 2008 Hi, Here are a few things for you to think about if you click 4 check boxes your ONOFF array will look like this ONOFF[0] = 1 ONOFF[1] = 1 ONOFF[2] = 1 ONOFF[3] = 1 This is why the system is setting the first x records to be 1 where x is the number of boxes you check. Any records that you have been set to on previously will stay as 1 I would recommend setting the value to be the id of the record you want to update as you only have an on or off value for the field. A Quote Link to comment Share on other sites More sharing options...
Benbo1980 Posted July 16, 2008 Author Share Posted July 16, 2008 Thanks for the speedy reply, If I set the value of the checkbox array to the id (presumably by using <? echo $rows['id']; ?> in the "value") will the UPDATE section need to change from: <?php // Check if button name "Submit" is active, do this if($Submit){ for($i=0;$i<$count;$i++){ $sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]', ONOFF='$ONOFF[$i]' WHERE id='$id[$i]'"; $result1=mysql_query($sql1); } } if($result1){ header("location:update_multiple.php"); } mysql_close(); ?> Thanks, Ben Quote Link to comment Share on other sites More sharing options...
DoddsAntS Posted July 16, 2008 Share Posted July 16, 2008 Yeah, you'll need to change the update query Quote Link to comment Share on other sites More sharing options...
Benbo1980 Posted July 16, 2008 Author Share Posted July 16, 2008 Sorry to push you even more, but what would you suggest I change it to? I'm guessing I would need somthing to tell the UPDATE function that checked = 1 and unchecked = 0 ??? But I'm not so sure how to code that... Thanks in advance, Ben Quote Link to comment Share on other sites More sharing options...
Benbo1980 Posted July 16, 2008 Author Share Posted July 16, 2008 Any ideas anyone? ??? Thanks Ben Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.