So many possibilities

Reformed

I'm hoping that someone out there can help me out with mulitple conditions on database queries. I'm working with a script that records timesheets and has many tables in my database to store timesheet information for things such as client information, project info, work categories and a few others.

My problem is that now that I'm able to record these records, I need to generate reports based on many different conditions (hopefully into pdf's). I have so many different possible select conditions that I'm going to have a ton of if statements with queries for each one.

For example if a user wants a report for all clients, a particular project, all categories, previously billed invoices, on non-rush projects, it looks like I'll have to make an if conditional based on this possibility and many, many others. I wondered if there was another way to approach this.

Here is a screenshot of what I'm working with: Click Me

stolzyboy

looks like you will have to stick with multiple if's/switches/etc...

Reformed

That's what I was afraid of. Thanks for your help. BTW, it's probably really cool in Fargo now, as compared our (Phoenix) 105 degree heat. 🙁

BlueMeteor

I have read your page's source...

I had do some work like yours,is easy,use JS,"onChange" event

and in your code ,option 'ALL'-value should be '%'

in your QUERY,use 'like'

Reformed

Good idea with the % and the Like in my queries. That will help for sure. What do you mean about the JavaScript onChange event? I'm not too sure what you mean.
🙂

Reformed

Okay, now I see what BlueMeteor meant about the onChange function. Instead of doing that, I made my dropdown select 'All' options have a value of '%'. So now if a user at any time selects 'All' as an option the database will query all records for that row when used with a LIKE operator.

This cuts down a ton of code and makes life much easier. Thanks BlueMeteor for the great idea. And in case anyone else runs into a similar problem, here is what the query code looks like when using this technique:

AND 

lu_category_codes.category_id LIKE 

'220' AND user_login.user_id LIKE '%'

Where you see the '%' all records are retrieved and that correspond to the value of 'All' in the select list. Hope this helps someone.

mystrymaster

I did a reporting tool like this and I had the query built a the end with all the choices.

here is a sample of the way that I had to do it

//Define the begining of the query
  $begQuery = "SELECT problem.Battery, problem.County, problem.ERS, problem.Field, problem.CallNum, report.Date, garage.num, problem.reportID FROM report, problem, garage WHERE ";
//Define the rest of the where clause
	$whereQuery = "problem.ReportID = report.ID AND problem.SupRead = '1' AND problem.Garage = garage.ID AND report.ID = problem.reportID ORDER BY report.Date,garage.num";
// Now begin to build the dynamic portion of the query
  if ($Shop != '-1') {
  	// This is the shop query
  		$shopSearch = "problem.garage = $Shop AND ";
  } else {
  		$shopSearch = "";
  }
  if ($Shift != '-1') {
  	// This is the shift query
  		$shiftSearch = "report.Shift = $Shift AND ";
  } else {
  		$shiftSearch = "";
  }
  if ($County != '-1') {
  	// This is the county query
  		$countySearch = "problem.County = '$County' AND ";
  } else {
  		$countySearch = "";
  }
  if ($BegDate != '-1' && $EndDate != '-1'){
  	// This is the date range query
  	$dateRange = "report.date BETWEEN '$BegDate' AND '$EndDate' AND ";
  } else {
  	$dateRange = "";
  }
// Now that all of that is taken care of Lets build the Query
  $query = ("$begQuery $dateRange $shopSearch $shiftSearch $countySearch $whereQuery");

maybe this will help you