john010117 Posted July 2, 2007 Share Posted July 2, 2007 I'll get right down to the problem. First of all, here's one of the part's I'm having trouble with. <?php // Above contains the SELECT query if(mysql_num_rows($query) > 0) { while($row = mysql_fetch_array($result)) { echo '<form action="result.php" method="POST"> <input name="pm_delete[]" type="checkbox" value="' . $row['msg_id'] . '"><br /> <input type="submit" name="submit" value="Submit"> </form>'; As you can see, for each of the results, it displays a checkbox. Here's result.php: <?php // All the checks go above here $query = DELETE $privmsgs_tbl_name, $privmsgs_spec_tbl_name FROM $privmsgs_tbl_name, $privmsgs_spec_tbl_name WHERE "; foreach($_POST['pm_delete'] as $arr => $pid) { $query .= "$privmsgs_tbl_name.msg_id = '$pid' AND $privmsgs_tbl_name.recipient_uid = '$session_uid' AND $privmsgs_spec_tbl_name.msg_id = '$pid' AND $privmsgs_spec_tbl_name.user_id = '$session_uid' } Ok. Let me explain. I put the selected checkbox(es) in an array. Now, for each of the checkboxes, I want to delete the row associated with it (with the "$row['msg_id']" being the common "thing" between them). But, I need an AND statement at the end of each query (otherwise, it would look like this: AND $privmsgs_spec_tbl_name.user_id = '$session_uid'$privmsgs_tbl_name.msg_id = '$pid' ... which of course, would throw an error). But if I just add an " AND " at the end, the last element would look like this: AND $privmsgs_spec_tbl_name.user_id = '$session_uid' AND ... which will also throw an error. So how would I configure the code so that it adds an " AND " at the end of each pass without adding one at the last pass? Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 2, 2007 Share Posted July 2, 2007 Let's say you have an array of integer IDs where each ID corresponds to a record in a table. You can easily delete all of the records corresponding to those IDs in the array: <?php $IDArr = Array( 1, 3, 5, 7, 9 ); $sql = "DELETE * FROM tableName WHERE tableName.id IN (" . implode(', ', $IDArr) . ") LIMIT " . count($IDArr); $q = mysql_query($sql); ?> Hope you can apply that to your situation. Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 Will this work with deleting rows from multiple tables that I have now? They all have the same ID as their common row. Quote Link to comment Share on other sites More sharing options...
Hypnos Posted July 2, 2007 Share Posted July 2, 2007 I would think you would want a query more like this: DELETE FROM table1, table2 WHERE (table1.pid = $id AND table1.session = $session) OR (table2.pid = $id AND table2.pid = $session) OR (table1.pid = $id AND table1.session = $session) Of course, ignore the vars and that I didn't use quotes, but you get the idea. That said, to answer your question, I would use substr to remove the last AND/OR from the string. Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 @ roopurt18: I got this error: 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 'LIMIT 2' at line 8 My query now looks like this: <?php $query = "DELETE $privmsgs_tbl_name, $privmsgs_spec_tbl_name FROM $privmsgs_tbl_name, $privmsgs_spec_tbl_name WHERE $privmsgs_tbl_name.msg_id IN (" . implode(', ', $_POST['pm_delete']) . ") LIMIT " . count($_POST['pm_delete']); ?> Any ideas? @ Hypnos: I'll try it out. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 2, 2007 Share Posted July 2, 2007 Echo the SQL query and paste it here. Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 Echo the SQL query and paste it here. DELETE privmsgs, privmsgs_spec FROM privmsgs, privmsgs_spec WHERE privmsgs.msg_id IN (4,5) LIMIT 2 Quote Link to comment Share on other sites More sharing options...
teng84 Posted July 2, 2007 Share Posted July 2, 2007 try to remove the limit Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 That just deletes ALL of the records. I believe I need the limit in there. Quote Link to comment Share on other sites More sharing options...
teng84 Posted July 2, 2007 Share Posted July 2, 2007 i think this is wrong DELETE privmsgs, privmsgs_spec FROM privmsgs, privmsgs_spec WHERE privmsgs.msg_id IN (4,5) LIMIT 2 try to change this to DELETE FROM table_name WHERE column_name = some_value Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 try to change this to DELETE FROM table_name WHERE column_name = some_value Since I'm dealing with multiple tables, that wouldn't work. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 2, 2007 Share Posted July 2, 2007 What are the joining columns on these tables? You want something like: DELETE t1.*, t2.* FROM tableName1 t1, tableName2 t2 WHERE t1.id IN (1, 2, ..., n) AND t1.col=t2.col From the SQL documentation: For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used. So remove the LIMIT. Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 I don't really get what you mean by the question, but the field "msg_id" is the same in both tables. I'll try that out. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 2, 2007 Share Posted July 2, 2007 Then maybe: DELETE t1.*, t2.* FROM tableName1 t1, tableName2 t2 WHERE t1.msg_id IN (1, 2, 3, ..., n) AND t1.msg_id=t2.msg_id Quote Link to comment Share on other sites More sharing options...
john010117 Posted July 2, 2007 Author Share Posted July 2, 2007 That worked! Thank you all so much for helping me out with this. 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.