barniegilly Posted August 22, 2011 Share Posted August 22, 2011 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] Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/ Share on other sites More sharing options...
fenway Posted August 22, 2011 Share Posted August 22, 2011 Sorry, what's broken? Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260582 Share on other sites More sharing options...
barniegilly Posted August 22, 2011 Author Share Posted August 22, 2011 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'. Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260633 Share on other sites More sharing options...
barniegilly Posted August 22, 2011 Author Share Posted August 22, 2011 This is the front end to my selection form, should I be using php instead of the mysql side? [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260639 Share on other sites More sharing options...
Muddy_Funster Posted August 23, 2011 Share Posted August 23, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260852 Share on other sites More sharing options...
barniegilly Posted August 23, 2011 Author Share Posted August 23, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260858 Share on other sites More sharing options...
fenway Posted August 23, 2011 Share Posted August 23, 2011 There is a freelancing PHP forum. Quote Link to comment https://forums.phpfreaks.com/topic/245428-search-script-with-where-and-mysql-queries/#findComment-1260921 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.