help with syntax

[dadamssg87]

I have the table with the following structure

 

id bigin(11) auto_increment

group bigint(11)

date date

 

 

I'm trying to delete several dates within the same group. So i may be deleting one row or 100..who knows. I use a function that takes a dates array and then converts into into sql language. Its below

 

<?php
function array_to_sql($array)
	{
		$first = $array['0'];
		$sql = "AND date = '$first' ";

		$shift = array_shift($array);
	   
		if(!empty($array))
		{	
			foreach($array as $key => $date)
			{
				$sql .= "AND date = '$date' ";
			}
		}

		return $sql;

	}
?>

 

So if i have an array like the following

 

Array

(

    [0] => 2011-07-27

    [1] => 2011-07-28

    [2] => 2011-08-03

    [3] => 2011-08-05

)

 

 

 

using my function will spit out

 

DELETE FROM Rate_exceptions WHERE `group` = '4' AND date = '2011-07-27' AND date ='2011-07-28' AND date = '2011-08-03' AND date = '2011-08-05'

 

this query doesn't delete anything and i don't know why. If only use one date the deletion works for that one date but i want to be able to input several dates

 

anybody know what im doing wrong?

[AyKay47]

how can your date field equal all those things at once? I think you are confused in how this is done..

also, are you using mysql_error to debug this query?

[dadamssg87]

I remember doing this a while back just can't remember how.

 

It would delete all rows that matched the group and any date defined in the query.

 

Yeah i'm using mysql_error and also double checking in the phpadmin sql box. It doesn't return any errors. Just no rows get deleted.

[Pikachu2000]

Implode the array with single quotes and commas as the glue, and then use MySQL's IN() instead.

 

$dates = implode( "', '", $array );
$query = "DELETE FROM table WHERE date_field IN ('$dates')";

[dadamssg87]

awesome thanks...i also figured out i could do it this way...but in() is cleaner. thanks!

 

DELETE FROM Rate_exceptions WHERE `group` = '4' AND date = '2011-07-27' OR (`group` = '4' AND date ='2011-07-28') OR (`group` = '4' AND date ='2011-07-29') OR (`group` = '4' AND date ='2011-07-30')