Correct SQL Select statement when using BETWEEN

I need to do a few things here and I'm confused on how this should work.

Here are the variables I need to work with:

Date Range
Employee ID
Team

and a possible additional search term (acct_number='$_REQUEST[q]') if someone wants to search for a specific account number.

Each variable is populated with a drop down select, the first option being * for all records. The dates are s开发者_运维知识库et using a date picker, calendar style, and it's automatically set as date 1=seven days ago and date 2=today.

My query:

SELECT * FROM table
WHERE job_date BETWEEN '$search_date1' AND '$search_date2' AND
    employee_id='$searched_employee' AND team='$searched_team' AND
    acct_number='$_REQUEST[q]'
ORDER BY employee_id desc

I know the records are there but my search still returns no records. After the date, the AND employee_id='$searched_employee' could be * so it should return all records and the same goes for the team.

Echoing the variables to try make sure something was set gives me this: No records found for "Employee" for dates between 2011-05-01 and 2011-05-31 and on team "Team".

Can I not request more with AND in the Select statement after I use the BETWEEN?

Thanks in advance!

--Joel

You cannot use '*' wildcard for WHERE clause.

Try using variables for conditions you put in your query, like:

if( is_numeric( $searched_employee ) )
  $employee_cond = " AND employee_id=$searched_employee";
else
  $employee_cond = "";

and put $employee_cond inside your query instead of AND employee_id='$searched_employee'. Repeat for every condition you need.

Put parenthesis around the date range condition:

SELECT * FROM table WHERE (job_date BETWEEN '$search_date1' AND '$search_date2') AND employee_id='$searched_employee' AND team='$searched_team' AND acct_number='$_REQUEST[q]' ORDER BY employee_id desc

I don't think its a matter of your BETWEEN statement as it is the amount of criteria you are "AND"ing together. What you are asking for is ALL of the criteria must be true for it to be included in the set...

Ex: Your from/to dates are May 1 to May 31, but the $Searched_Employee you are looking for had no activity within that range, or the employee is not associated with $searched_team, etc with your Acct_Number.

You are explicitly looking for ALL those conditions. You MAY intend to have some "OR" conditions going on... such as

select 
       Jobs.*,
       Employees.LastName,
       Employees.FirstName,
       Employees.{whatever other columns}
   from 
      Jobs 
         join Employees on Jobs.EmployeeID = Employees.ID
   where
          Jobs.job_Date between StartDate and StopDate
      AND Jobs.employee_id = SearchedEmployee
      AND Jobs.team = SearchedTeam
      AND Jobs.Acct_Number = SearchedAccount

The * is only really used such as count() or for fields to be returned from a query, and NOT within where/join conditions (unless its a count() ). So, above has been modified to keep ALL your "AND" clauses, but also show to get specific or all fields from joined table.

The table names querying from are modified as your actual tables were not available. As you can see, I did the Jobs.* to return ALL fields from that table, but explicitly included Employees.{certain individual fields} from that.

Hope this clarification helps you with future querying...