[SOLVED] mysql delete

[phorcon3]

mysql table:

 

id

name

check

reply

 

1

test1

1

1

 

2

test2

[/td]

[td]1

 

and now i wanna have a cronjob, and i wanna do the following

 

first of all i have to group reply, meaning they must have the same ID number

 

and then i wanna delete those, but ONLY then when ALL those rows, with the same reply ID number ALSO have a 1 in the check column

 

does that make sense?

 

i tried, the following

 

<?php
$a = mysql_query("SELECT `reply` FROM `table` WHERE `check` = '1' GROUP BY `reply`");
while($b = mysql_fetch_assoc($a))
{
mysql_query("DELETE FROM `table` WHERE `reply` = '$b[reply]'");
}
?>

 

but it would delete ALL rows with the same reply ID number, even tho i ONLY want to delete them when ALL of them have check = 1, but its ignoring it, and i suppose its because of GROUP BY?

 

i hope this makes sense lol

[phorcon3]

oh cmon guys ...nothin? lol ..no ideas?

 

i hate that crap, nothin works the way i want it to ^^

[haku]

Couldn't understand your explanation of what you were trying to do.

[phorcon3]

lol ...i must really suck at explainin things

 

1st row:

ID: 1

Name: test1

Check: 1

Reply: 1

 

2nd row:

ID: 2

Name: test2

Check: 0

Reply: 1

 

3rd row:

ID: 3

Name: test3

Check: 0

Reply: 3

 

so, what i wanna do is, group all rows ..which have the same reply ID number, which in this case would be the [1st and 2nd row] and the [3rd row]

 

and then i wanna check, if both have a 1 in their check column ...and if BOTH and ONLY then have a 1 in there, i wanna delete them

 

if just ONE has a 1 in their check column (like in my example above) nothing should happen

 

does it make sense now? lol

[phorcon3]

oops

[haku]

I got it. Thats a tricky query! I'll play around with it for a bit. No guarantees.

[phorcon3]

thanks lol ...i know its pretty dumb, but i cant really come up with some better..

 

u try to make a fancy messaging system ..and then somethin like that happens

[clearstatcache]

 

 

<?php

$a = mysql_query("

            select q1.reply

            from (

                    select reply, count(*) as data_cnt from sample where 1 group by reply

                  ) as q1

          left join (

                    select reply, count(*) as check_cnt from sample where `check`=1 group by reply

          ) as q2

                  on q1.reply=q2.reply

          where data_cnt = check_cnt

      ");

while($b = mysql_fetch_assoc($a))

{

mysql_query("DELETE FROM `table` WHERE `reply` = '$b[reply]'");

}

?>

[phorcon3]

woot! thanks, man ..imma gonna try it out! appreciate it