[SOLVED] Using WHERE statement

[inspireddesign]

Hello guys -

 

I have a small problem.  How would one select multiple ID's using the WHERE clause?  Is there an other way other than the way I'm accomplishing it below?  I currently have a Selection Box (Multiple Selection can be made) and taking that Array and passing in to the WHERE clause which is indicated below. 

 

It DOES query the selected Employee's but the date range fails and dumps ALL data and ignores the BETWEEN two dates clause.

 

The SQL output selecting three from the list is as follows:

 

SELECT *, DATE_FORMAT( Clockin, '%m/%d/%Y' ) AS FormattedDate, DATE_FORMAT( Clockin, '%H:%i' ) AS FormattedTimeIn, DATE_FORMAT( Clockout, '%H:%i' ) AS FormattedTimeOut, TIMEDIFF( Clockout, Clockin ) AS HoursWorked FROM time_clock AS tc JOIN employees AS e ON e.Id = tc.Emp_id JOIN location AS loc ON loc.LocationID = tc.LocationID WHERE tc.Clockin BETWEEN '2009-10-14' AND '2009-10-21' OR e.Id=30 OR e.Id=53 OR e.Id=60 ORDER BY e.LastName ASC

 

*** END ***

 

I know what my problem is but can't figure out the best way to write the WHERE clause so it can handle both SQL arguments.  If I write the above code using as follows it works fine:

 

WHERE tc.Clockin BETWEEN '2009-10-14' AND '2009-10-21' AND e.Id=30 OR e.Id=53 OR e.Id=60 ORDER BY e.LastName ASC

 

Currently My foreach loop adds OR and not the AND.  How can I structure the Statement to allow for the proper syntax?  Should the foreach loop be rewritten or can it be accomplished using what I have now?

 

Any help would be great!  Thanks!

 

<?php

	$selectedEmps = $_POST['emp_list'];

// Array of selected employee's -- build SQL per selection

	if ($selectedEmps){
		 foreach ($selectedEmps as $s){ 
			$selEmp .= ' OR e.Id=' . $s . ' ';
		}
	}

	  $sql = "SELECT *,
			  DATE_FORMAT( Clockin, '%m/%d/%Y' ) AS FormattedDate,
			  DATE_FORMAT( Clockin, '%H:%i' ) AS FormattedTimeIn,				  
			  DATE_FORMAT( Clockout, '%H:%i' ) AS FormattedTimeOut,				  
			  TIMEDIFF( Clockout, Clockin ) AS HoursWorked 
			  FROM time_clock AS tc
			  JOIN employees AS e ON e.Id = tc.Emp_id 
			  JOIN location AS loc ON loc.LocationID = tc.LocationID

			  WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
			   " . $selEmp . " 
			  ORDER BY e.LastName ASC";

	$listPayroll = mysql_query( $sql );	

?>

[Calver]

I think you just need to add some parantheses and arrange for the WHERE clause to look like this...

 

WHERE (tc.Clockin BETWEEN '2009-10-14' AND '2009-10-21') AND (e.Id=30 OR e.Id=53 OR e.Id=60)

[Calver]

You'll need to remove the first ' OR ' from the string...

 

$selEmp = substr($selEmp, 4, strlen($selEmp) - 4);

 

There's probably a better way of doing that!

 

Then the WHERE clause...

 

WHERE (tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."') AND (
    " . $selEmp . " )
    ORDER BY e.LastName ASC";