Jump to content

[SOLVED] Loops and arrays... problem


Recommended Posts

I'll get right down to the problem. First of all, here's one of the part's I'm having trouble with.

// 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">


As you can see, for each of the results, it displays a checkbox.


Here's result.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?

Link to comment
Share on other sites

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:


$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.

Link to comment
Share on other sites

I would think you would want a query more like this:

DELETE FROM table1, table2 WHERE

(table1.pid = $id AND table1.session = $session)


(table2.pid = $id AND table2.pid = $session)


(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.


Link to comment
Share on other sites

@ 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:

$query = "DELETE
                 $privmsgs_tbl_name.msg_id IN (" . implode(', ', $_POST['pm_delete']) . ") LIMIT " . count($_POST['pm_delete']);


Any ideas?


@ Hypnos: I'll try it out.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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


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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.