[SOLVED] Loops and arrays... problem

[john010117]

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?

[roopurt18]

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.

[john010117]

Will this work with deleting rows from multiple tables that I have now? They all have the same ID as their common row.

[Hypnos]

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.

 

[john010117]

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

[roopurt18]

Echo the SQL query and paste it here.

[john010117]

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

[teng84]

try to remove the limit

[john010117]

That just deletes ALL of the records. I believe I need the limit in there.

[teng84]

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

 

[john010117]

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.

[roopurt18]

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.

[john010117]

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.

[roopurt18]

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

[john010117]

That worked! Thank you all so much for helping me out with this.