SEARCH SCRIPT with WHERE AND MYSQL QUERIES

[barniegilly]

Hi am working on this script and need a little help with the MYSQL bit,  The front end is a table with 4 choices where the user can choose either one choice or a combination of choices.  I have it working where if they just choose the venue it returns the correct answer however am not coding the rest of the mysql correctly when multipule choices are entered.

 

The database structure consists of multiple tables. 

 

My Mysql query is this so far

		$sql = "SELECT * \n"
		. "FROM events \n"
		. "LEFT OUTER JOIN eventdisciplines \n"
		. "ON events.event_id = eventdisciplines.event_id \n"
		. "LEFT OUTER JOIN discipline \n"
		. "ON eventdisciplines.dis_id = discipline.dis_id \n"
		. "LEFT OUTER JOIN venue \n"
		. "ON events.ven_id = venue.ven_id \n"
		. "LEFT OUTER JOIN county \n"
		. "ON venue.county_id = county.county_id \n"
		. "WHERE events.startdate > NOW()\n"
		. "AND events.ven_id = {$ven_id} \n";

 

 

The key search criteria is on the county_id and events >NOW  from there on it needs to be so that if either of the  selections of  are made that it returns the records.

 

The logic is that if events.county_id = 2 and events.startdate is greater then today  with events.ven_id = 2  and / or  events.dis_id = 3 then return the rows.

 

Just missing that and or bit of the mysql I think, unless there is another way I should be doing it.

 

PS JUST READ THE RULES!!  MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $

 

 

 

[attachment deleted by admin]

[fenway]

Sorry, what's broken?

[barniegilly]

I have worked on it a little more this is now my mysql query

		$sql = "SELECT DATE_FORMAT (events.startdate, '%a, %d, %b') 
			   AS stdate, events.event_id, events.title, events.ven_id, events.org_id,
			   venue.county_id, venue.ven_id, eventdisciplines.event_id, eventdisciplines.dis_id, county.county_id, discipline.dis_id \n"
		. "FROM events \n"
		. "LEFT OUTER JOIN eventdisciplines \n"
		. "ON events.event_id = eventdisciplines.event_id \n"
		. "LEFT OUTER JOIN discipline \n"
		. "ON eventdisciplines.dis_id = discipline.dis_id \n"
		. "LEFT OUTER JOIN venue \n"
		. "ON events.ven_id = venue.ven_id \n"
		. "LEFT OUTER JOIN county \n"
		. "ON venue.county_id = county.county_id \n"
[i]		. "WHERE (events.ven_id = {$ven_id} AND events.org_id = {$org_id}) \n"[/i]
		. "AND events.startdate > NOW()\n"
		. "AND venue.county_id = {$countyid}  \n"
		. "ORDER BY startdate ASC";

 

I want the user's form as shown on my screen print to be able to either have one or more choices entered,  I am unsure of the correct mysql and believe the line that I need to change is as indicated. 

 

My code works with both variables entered but My query needs to be  and 'AND' 'OR'.

[barniegilly]

This is the front end to my selection form,  should I be using php instead of the mysql side?

 

[attachment deleted by admin]

[Muddy_Funster]

For multiple choice filtering you need to dynamicaly genarate the WHERE clause in your PHP depending on options chosen.  You will need a bunch of if conditions and a check to see whither the chosen option is the first chosen option, or if there is another before it.

 

[barniegilly]

Hi

 

thanks for that, I think I will need to outsource this as beyond what I can do, just need to know which way to go  so thank you for your help.

 

 

[fenway]

There is a freelancing PHP forum.