downah Posted February 29, 2012 Share Posted February 29, 2012 Hi guys, I am trying out this code but it does not work, any idea what I am doing wrong? <?php include 'connect.php'; $tbl_name="users"; $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); $count=mysql_num_rows($result); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="form1" method="post" action=""> <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td bgcolor="#FFFFFF"> </td> <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td> </tr> <tr> <td align="center" bgcolor="#FFFFFF">#</td> <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Firstname</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Location</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td> <td align="center" bgcolor="#FFFFFF"><strong>Website</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td> <td bgcolor="#FFFFFF"><? echo $rows['userID']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['firstname']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['location']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td> <td bgcolor="#FFFFFF"><? echo $rows['website']; ?></td> </tr> <?php } ?> <tr> <td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <? if($delete){ for($i=0;$i<$count;$i++){ $del_id = $checkbox[$i]; $sql = "DELETE FROM $tbl_name WHERE id='$del_id'"; $result = mysql_query($sql); } if($result){ echo "<meta http-equiv=\"refresh\" content=\"0;URL=showusers2.php\">"; } } mysql_close(); ?> </table> </form> </td> </tr> </table> Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/ Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 Couple things: Please wrap your code in code blocks (the octothorpe in the editor), and please tell us what exactly isn't working in your script. Are you getting an error message? Please be more specific how it's not working. Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322514 Share on other sites More sharing options...
downah Posted February 29, 2012 Author Share Posted February 29, 2012 Hi, sorry I am new here and also very new to PHP & MySQL. this is another script that is trying to do the same thing (deleting rows with checkbox) <?php include "connect.php"; $result = mysql_query("SELECT * FROM users"); if(isset($_POST['delete'])){ if(is_array($_POST['id'])){ echo '<pre>'; print_r($_POST); echo '<pre>'; foreach($_POST['id'] as $id){ $query = "DELETE FROM users WHERE id=".$id; mysql_query($query)or die(mysql_error()); echo "User Deleted"; } } } ?> <form action="<?php $_SERVER['PHP_SELF'] ?>" method="POST"> <? echo "<table border='0'> <tr> <th>Firstname</th> <th>Lastname</th> <th>Location</th> <th>Email</th> <th>Website</th> <th>userID</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['firstname'] . "</td>"; echo "<td>" . $row['lastname'] . "</td>"; echo "<td>" . $row['location'] . "</td>"; echo "<td>" . $row['email'] . "</td>"; echo "<td>" . $row['website'] . "</td>"; echo "<td>" . $row['userID'] . "</td>"; ?> <td><input type="checkbox" name="id[]" value="<?php $row['id'] ?>" /></td> <? echo "</tr>"; } echo "</table>"; echo "<input type='submit' value='Delete Users' name='delete' />"; mysql_close($con); ?> this gives me this error: Array ( [id] => Array ( [0] => ) [delete] => Delete Users ) 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 '' at line 1 And the first script does not seem to do anything at all, no error or anything. Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322515 Share on other sites More sharing options...
Pikachu2000 Posted February 29, 2012 Share Posted February 29, 2012 Where did that code come from? It relies on a certain php.ini file directive being ON. That particular directive has been OFF by default for about the past 10 years. Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322516 Share on other sites More sharing options...
downah Posted February 29, 2012 Author Share Posted February 29, 2012 Ah I see I am not sure I have been doing some research about this by searching in google "deleting rows with checkbox php mysql" and been trying multiple different bits trying to incorporate it in my pathetic code, either way how can I get this to work? Thanks a lot Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322518 Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 I'm not sure what the overall problem is, but you are missing a: <? right before your while statement. Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322521 Share on other sites More sharing options...
downah Posted February 29, 2012 Author Share Posted February 29, 2012 I don't think I am batwimp Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322528 Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 Oh, you're right. I'm brain dead. Time to go home! Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322529 Share on other sites More sharing options...
Pikachu2000 Posted February 29, 2012 Share Posted February 29, 2012 In the second code block, change the short open <? tag to the standard <?php open tag and see if that makes a difference. Short open tags is another directive in the php.ini file that is now OFF by default . . . Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322532 Share on other sites More sharing options...
downah Posted February 29, 2012 Author Share Posted February 29, 2012 No problem batwimp, and thanks pikachu, changed it all but still get the same error, I'd prefer the second block of code to work so thanks for giving some help on that one Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322533 Share on other sites More sharing options...
downah Posted February 29, 2012 Author Share Posted February 29, 2012 Otherwise could anyone help me change the second code? I did not thought it would be this tricky to delete with checkbox Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322535 Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 Go through and make sure all of your: <? are actually: <?php Also, this line: <input type="checkbox" name="id[]" value="<?php $row['id'] ?>" should be: <input type="checkbox" name="id[]" value="<?php echo $row['id'] ?>" Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322536 Share on other sites More sharing options...
Psycho Posted February 29, 2012 Share Posted February 29, 2012 I think I might know what the problem is. You first run a query to get all the current records. Next you run the operations to delete the selected records. Then you take the results from the first query to display o the page. So, if you did delete any records you wouldn't see it in the output because you retireved those records before you did the deleted. If you were to refresh the page you would see them gone. But, assuming that is not the problem, I'll provide some suggestions. It looks like you have the basic idea and may just be missing something. You have some debugging code in there so that should point you in the right direction (yet you failed to tell us what errors, if any, you were getting so we would know as well). Anyway, I see a couple things that I think should be changed. 1. You check to see if the delete button was checked and then test to see if the 'id' value is an array. Why not just do a single check to see if the 'id' value was passed and is an array > 0 elements? 2. You do not need to run an individual query for each selected record. Instead run ONE query to delete all the selected records using the IN() operator 3. I would really suggest adding some code formatting to your HTML output. As it is there are no line breaks. If the problem is in the creation of the form, it is going to be difficult to easily spot it. 4. Your code does not have a good flow. For example, you run the query to get the records to display then you run the code to delete the selected records, then you use the results from the first query. Keep the operations that go together with each other. Also, figure out the logic flow before you write it. 5. Don't use '*' for your select queries unless you really need every field. It is inefficient. Plus, you may be referencing the wrong field for the id value which could also be the problem. If you listed the fields out in the query and one did not exist you would have received an error. 6. I don't see a closing form tag. <?php include "connect.php"; //Check if records were submitted for deletion if(isset($_POST['id']) && count($_POST['id'])) { //Ensure values are ints $deleteIDs = implode(', ', array_map('intval', $_POST['id'])); $query = "DELETE FROM users WHERE id IN ({$deleteIDs})"; //Debug line echo "Delete Query: $query<br>\n"; mysql_query($query) or die(mysql_error()); } //Get any existing records to create form $query = "SELECT `id`, `firstname`, `lastname`, `location`, `email`, `website`, `userID` FROM users ORDER BY `lastname`, `firstname`" $result = mysql_query($query) or die(mysql_error());; //Process results into HTML output and store in variable $userFormList = ''; while($row = mysql_fetch_array($result)) { $userFormList .= " <tr>\n"; $userFormList .= " <td>{$row['firstname']}</td>\n"; $userFormList .= " <td>{$row['lastname']}</td>\n"; $userFormList .= " <td>{$row['location']}</td>\n"; $userFormList .= " <td>{$row['email']}</td>\n"; $userFormList .= " <td>{$row['website']}</td>\n"; $userFormList .= " <td>{$row['userID']}</td>\n"; $userFormList .= " <td><input type='checkbox' name='id[]' value='{$row['id']}' /></td>\n"; $userFormList .= " </tr>"; } mysql_close($con); ?> <html> <body> <form action="" method="POST"> <table border='0'> <tr> <th>Firstname</th> <th>Lastname</th> <th>Location</th> <th>Email</th> <th>Website</th> <th>userID</th> </tr> <?php echo $userFormList; ?> </table> <input type='submit' value='Delete Users' name='delete' /> </form> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322537 Share on other sites More sharing options...
downah Posted March 1, 2012 Author Share Posted March 1, 2012 Thanks Psycho, I am a beginner to php and mysql but for me it seems like just getting stuck in is the best to learn, but I really appreciate your advice and read it atleast 4 times over and looking some stuff up as I wasn't sure of some things but thanks again, with the code you gave <?php include "connect.php"; //Check if records were submitted for deletion if(isset($_POST['id']) && count($_POST['id'])) { //Ensure values are ints $deleteIDs = implode(', ', array_map('intval', $_POST['id'])); $query = "DELETE FROM users WHERE id IN ({$deleteIDs})"; //Debug line echo "Delete Query: $query<br>\n"; mysql_query($query) or die(mysql_error()); } //Get any existing records to create form $query = "SELECT `id`, `firstname`, `lastname`, `location`, `email`, `website`, `userID` FROM users ORDER BY `lastname`, `firstname`"; $result = mysql_query($query) or die ( mysql_error () ); //Process results into HTML output and store in variable $userFormList = ''; while($row = mysql_fetch_array($result)) { $userFormList .= " <tr>\n"; $userFormList .= " <td>{$row['firstname']}</td>\n"; $userFormList .= " <td>{$row['lastname']}</td>\n"; $userFormList .= " <td>{$row['location']}</td>\n"; $userFormList .= " <td>{$row['email']}</td>\n"; $userFormList .= " <td>{$row['website']}</td>\n"; $userFormList .= " <td>{$row['userID']}</td>\n"; $userFormList .= " <td><input type='checkbox' name='id[]' value='{$row['id']}' /></td>\n"; $userFormList .= " </tr>"; } mysql_close($con); ?> <html> <body> <form action="" method="POST"> <table border='0'> <tr> <th>Firstname</th> <th>Lastname</th> <th>Location</th> <th>Email</th> <th>Website</th> <th>userID</th> </tr> <?php echo $userFormList; ?> </table> <input type='submit' value='Delete Users' name='delete' /> </form> </body> </html> I get an error saying: Parse error: syntax error, unexpected T_VARIABLE in /Applications/XAMPP/xamppfiles/htdocs/php/showusers2.php on line 21 I searched around and it seems to mean there is most likely a { ( etc missing and I found a ; was missing and a ; was added somewhere else by accident it seems, either way I got rid of that and now I get an error saying " Unknown column 'id' in 'field list' " Is this by any chance talking about a primary key? I have read about this and feel like I made my table wrong, I have a unique auto increment in there called userID and not an id. Appreciated Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322547 Share on other sites More sharing options...
Psycho Posted March 1, 2012 Share Posted March 1, 2012 I don't normally test code - especially when it requires me to have a database which isn't provided. So it is not uncommon for there to be syntax errors. However THIS is your problem " Unknown column 'id' in 'field list' " Is this by any chance talking about a primary key? I have read about this and feel like I made my table wrong, I have a unique auto increment in there called userID and not an id. I was going to ask you about that - I was wondering if 'id' and 'userID' were supposed to be the same. In fact, that is EXACTLY why I said you should NOT use '*' in your select statement and instead should list out the fields. In the code to create the form you were displaying $row['userID'] but you were then using $row['id'] to populate the value of the checkbox fields. Since there was no field with that index in the result set the values were getting set as empty strings. So, when you checked the checkboxes you were only passing those empty strings - not the IDs you wanted to delete! You can use whatever name you want for your id field and it should be an auto-increment int field. you just need to make sure you are USING the values. I've revised the code to use the correct field name - but I'm not getting the syntax error. <?php include "connect.php"; //Check if records were submitted for deletion if(isset($_POST['id']) && count($_POST['id'])) { //Ensure values are ints $deleteIDs = implode(', ', array_map('intval', $_POST['id'])); $query = "DELETE FROM users WHERE id IN ({$deleteIDs})"; //Debug line echo "Delete Query: $query<br>\n"; mysql_query($query) or die(mysql_error()); } //Get any existing records to create form $query = "SELECT `firstname`, `lastname`, `location`, `email`, `website`, `userID` FROM users ORDER BY `lastname`, `firstname`"; $result = mysql_query($query) or die ( mysql_error () ); //Process results into HTML output and store in variable $userFormList = ''; while($row = mysql_fetch_array($result)) { $userFormList .= " <tr>\n"; $userFormList .= " <td>{$row['firstname']}</td>\n"; $userFormList .= " <td>{$row['lastname']}</td>\n"; $userFormList .= " <td>{$row['location']}</td>\n"; $userFormList .= " <td>{$row['email']}</td>\n"; $userFormList .= " <td>{$row['website']}</td>\n"; $userFormList .= " <td>{$row['userID']}</td>\n"; $userFormList .= " <td><input type='checkbox' name='id[]' value='{$row['`userID`']}' /></td>\n"; $userFormList .= " </tr>"; } mysql_close($con); ?> <html> <body> <form action="" method="POST"> <table border='0'> <tr> <th>Firstname</th> <th>Lastname</th> <th>Location</th> <th>Email</th> <th>Website</th> <th>userID</th> </tr> <?php echo $userFormList; ?> </table> <input type='submit' value='Delete Users' name='delete' /> </form> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322551 Share on other sites More sharing options...
BigTime Posted March 1, 2012 Share Posted March 1, 2012 I think psycho forgot to change it to user ID in the delete directive $query = "DELETE FROM users WHERE id IN ({$deleteIDs})"; should be $query = "DELETE FROM users WHERE Userid IN ({$deleteIDs})"; Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322564 Share on other sites More sharing options...
downah Posted March 1, 2012 Author Share Posted March 1, 2012 I've now changed the table name users is now called user userID is now called id (also primary key) added in extra fields - all shows up but when I try to delete it gives this error: Delete Query: DELETE FROM user WHERE id IN (0) <?php include "connect.php"; //Check if records were submitted for deletion if(isset($_POST['id']) && count($_POST['id'])) { //Ensure values are ints $deleteIDs = implode(', ', array_map('intval', $_POST['id'])); $query = "DELETE FROM user WHERE id IN ({$deleteIDs})"; //Debug line echo "Delete Query: $query<br>\n"; mysql_query($query) or die(mysql_error()); } //Get any existing records to create form $query = "SELECT `id`, `firstname`, `lastname`, `email`, `website`, `password`, `address1`, `address2`, `city`, `county`, `postcode`, `sex`, `dobmonth`, `dobday`, `dobyear`, `customerfeedback`, `wgtffeedback` FROM user ORDER BY `lastname`, `firstname`"; $result = mysql_query($query) or die ( mysql_error () ); //Process results into HTML output and store in variable $userFormList = ''; while($row = mysql_fetch_array($result)) { $userFormList .= " <tr>\n"; $userFormList .= " <td>{$row['id']}</td>\n"; $userFormList .= " <td>{$row['firstname']}</td>\n"; $userFormList .= " <td>{$row['lastname']}</td>\n"; $userFormList .= " <td>{$row['email']}</td>\n"; $userFormList .= " <td>{$row['website']}</td>\n"; $userFormList .= " <td>{$row['password']}</td>\n"; $userFormList .= " <td>{$row['address1']}</td>\n"; $userFormList .= " <td>{$row['address2']}</td>\n"; $userFormList .= " <td>{$row['city']}</td>\n"; $userFormList .= " <td>{$row['county']}</td>\n"; $userFormList .= " <td>{$row['postcode']}</td>\n"; $userFormList .= " <td>{$row['sex']}</td>\n"; $userFormList .= " <td>{$row['dobmonth']}</td>\n"; $userFormList .= " <td>{$row['dobday']}</td>\n"; $userFormList .= " <td>{$row['dobyear']}</td>\n"; $userFormList .= " <td>{$row['customerfeedback']}</td>\n"; $userFormList .= " <td>{$row['wgtffeedback']}</td>\n"; $userFormList .= " <td><input type='checkbox' name='id[]' value='{$row['`id`']}' /></td>\n"; $userFormList .= " </tr>"; } mysql_close($con); ?> <html> <body> <form action="" method="POST"> <table border='0'> <tr> <th>id</th><br> <th>First Name</th><br> <th>Last Name</th><br> <th>Email</th><br> <th>Website</th><br> <th>Password</th><br> <th>Address Line 1</th><br> <th>Address Line 2</th><br> <th>city</th><br> <th>county</th><br> <th>postcode</th><br> <th>sex</th><br> <th>Date of Birth (month)</th><br> <th>Date of Birth (day)</th><br> <th>Date of Birth (year)e</th><br> <th>Customer Feedback</th><br> <th>WGTF Feedback</th><br> </tr> <?php echo $userFormList; ?> </table> <input type='submit' value='Delete Users' name='delete' /> </form> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322571 Share on other sites More sharing options...
Psycho Posted March 1, 2012 Share Posted March 1, 2012 You only use backticks when referencing field in a query. Remove them from this line $userFormList .= " <td><input type='checkbox' name='id[]' value='{$row['`id`']}' /></td>\n"; $row['`id`'] should be $row['id'] Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322582 Share on other sites More sharing options...
downah Posted March 1, 2012 Author Share Posted March 1, 2012 Thanks psycho, all working, much appreciated everyone for their help and brains Quote Link to comment https://forums.phpfreaks.com/topic/258008-super-basic-deleting-rows-with-checkbox/#findComment-1322809 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.