Add a new criteria to a SQL search (PHP)

Dev Shed Forums Sponsor:

February 6th, 2012, 01:30 PM

justinpugh

Registered User

Join Date: Feb 2012

Posts: 2

Time spent in forums: 42 m 51 sec
Reputation Power: 0

Add a new criteria to a SQL search (PHP)

Hi (please go straight to the bottom as it may save you lots of time)

Forgive me ignorance. I have a program from a company that I use to list events on a calendar on the internet.

The calendar allows users to add locations and specify what type of event or events are going to take place at this location and on what date. The calendar works well but I now want user to be able to search for events that take place of a "specific event type" eg All upcoming events that involve "show jumping".

The problem I have is that the calendar I use does not have this facility. It has a facility to all the user to display all upcoming events or even all upcoming events at a specific location. I've studied the database and I see why. An event can only have one location but may have several event categories taking place. There is a table for locations called Hc_locations and a table called hc_events for event descriptions a table called hc_categories for category classification and then also a table called hc_eventcategories that contains the event_id and the category_id. eg and event to be held at Liverpool Arena may have show jumping, dressage and cross country all on the same day.

The problem that I have is the SQL statement shown here "$result = doQuery("SELECT * FROM " . HC_TblPrefix . "events WHERE IsActive = 1 AND IsApproved = 1 AND StartDate >= '" . date("Y-m-d",mktime($hourOffset,date("i"),date("s"),date("m"),date("d"),date("Y"))) . "'");"

only references the one table(I think) and returns the results, in this case the SQL query returns all the events upcoming.

Please can you show me how to alter this statement so that the query then returns all the events that have a specific eventcategory. eg all events upcoming that include show jumping.

This to me is slightly more difficult (out of my depth) as it will also involve searching the hc_eventcategories table for all events that include "show jumping" + including the contents of the above query which I believe verifies the data to make sure its of a future date.

I've tried to read this back and not sure if its going to make sense.

Here is the SQL simplified (without the date bit)
SELECT * FROM `hc_events` WHERE `IsActive`=1 AND `IsApproved`=1

I then basically want to include in this search this search
SELECT * FROM `hc_eventcategories` WHERE `CategoryID`=4

which queries the hc_eventcategories for category id=4 (4 is show jumping id)

So I will only get the result if both statements are true.

Thanks for your time and patience on this, any more info please ask..
Justin (very very newbee)

Basically I think the statment should look like this but it doesnt work

SELECT * FROM `hc_events` WHERE `PkID`=(SELECT * FROM `hc_eventcategories` WHERE `CategoryID`=4)

Last edited by justinpugh : February 6th, 2012 at 01:46 PM. Reason: Tried to make it simple!

February 6th, 2012, 02:45 PM

SimonJM

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2006

Posts: 1,914

Time spent in forums: 1 Month 5 Days 11 h 34 sec
Reputation Power: 1297

You are going to want to JOIN the tables:

Code:

SELECT e.* 
  FROM `hc_events`e
    INNER JOIN `hc_eventcategories` c
      ON e.? = c.?
            AND
          c.`CategoryID`= 4
  WHERE e.IsActive = 1
    AND e.IsApproved = 1
    AND e.StartDate >= '" . date("Y-m-d",mktime($hourOffset,date("i"),date("s"),date("m"),date("d"),date("Y"))) . "'")

What we don't know is how the two tables are linked - hence the ?s.

__________________
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc

February 6th, 2012, 02:50 PM

justinpugh

Registered User

Join Date: Feb 2012

Posts: 2

Time spent in forums: 42 m 51 sec
Reputation Power: 0

Quote:

Originally Posted by SimonJM

You are going to want to JOIN the tables:

Code:

SELECT e.* 
  FROM `hc_events`e
    INNER JOIN `hc_eventcategories` c
      ON e.? = c.?
            AND
          c.`CategoryID`= 4
  WHERE e.IsActive = 1
    AND e.IsApproved = 1
    AND e.StartDate >= '" . date("Y-m-d",mktime($hourOffset,date("i"),date("s"),date("m"),date("d"),date("Y"))) . "'")

What we don't know is how the two tables are linked - hence the ?s.

Thanks Simon for your help,,, I fiddled with the SQL "tester" and came up with SELECT * FROM hc_events WHERE PKID IN (SELECT eventid FROM hc_eventcategories WHERE categoryid=4)

I then transposed this to the PHP file so it now looks like this

$result = doQuery("SELECT * FROM " . HC_TblPrefix . "events WHERE PKID IN(SELECT eventid FROM hc_eventcategories WHERE categoryid=4) AND IsActive = 1 AND IsApproved = 1 AND StartDate >= '" . date("Y-m-d",mktime($hourOffset,date("i"),date("s"),date("m"),date("d"),date("Y"))) . "'");

It seems to work ok, Do you think that this may not work correctly under some circumstances or should it work ok.

Thanks in advance.
Justin