I know this is both part php and mysql, but its something that i cant get my head around and hopefully this would be a good starting point
I have a sub menu pulled down from a database, eg.
music
theatre
dance
film
etc etc.
In another db table i have events with the events.cat1 as a category, i.e. music, dance etc.
If it possible to have a conditional display of the one of the submenus if the amount of events in that category is ‘0’. I feel i can do it one at a time, but as submenus created via a loop from the database, im rather unsure
On one side, it doesnt show the menu title where the there are no events…ie. film isnt displayed…so that bit works
However it had theatre 6 times
(ive sent you a pm)
EDIT: Just made the connection…in the db at the moment there are 6 events that full under the menu title of Theatre…so maybe this is why it is shown 6 times…how do i restrict to show just the one instance of theatre in the menu?
I am still unsure that I could understand what you are exactly trying to get but here is the query you can try:
SELECT
e.*,
m.title
FROM
events2 AS e
INNER JOIN menu1 AS m ON (m.title=e.cat1 OR m.title=e.cat2 OR m.title=e.cat3)
WHERE
e.show_whatson='yes'
AND endate>CURRENT_DATE;
Maybe it is because of my weak understanding knowledge but I am really confused here from your first,second and last post. What do you mean by ‘categories’, ‘events’, ‘menus’? Are the categories and menus same or they are stored in different tables?
yes, events2.cat1 to 3 - an event can full into more than one menu1.title
The structure is really bad as its an old db when constant addons but these are the main bits:
CREATE TABLE IF NOT EXISTS events2 ( eventid int(11) NOT NULL auto_increment, eventtitle varchar(150) NOT NULL default ‘’, startdate date NOT NULL default ‘0000-00-00’, enddate date default NULL, cat1 varchar(50) default NULL, cat2 varchar(20) default NULL, cat3 varchar(20) default NULL, show_whatson varchar(3) default ‘yes’,
PRIMARY KEY (eventid)
CREATE TABLE IF NOT EXISTS menu1 ( menuid int(11) NOT NULL auto_increment, title varchar(255) default NULL, cat varchar(120) default NULL, title varchar(255) default NULL,
PRIMARY KEY (menuid)
One thing that i made a mistake on before…its the menu1.title thats links to the events2. cat1,2,3 not the menu1.cat that i said before. The menu1.cat is used to pull down the menu of event types
SELECT
e.*,
m.title
FROM
events2 AS e
INNER JOIN menu1 AS m ON (m.title=e.cat1 OR m.title=e.cat2 OR m.title=e.cat3)
WHERE
e.show_whatson='yes'
AND endate>CURRENT_DATE
GROUP BY
m.title;
Paul, I think you mean sub query support in MySQL. It does not need MySQL version 5. Sub queries are supported after 4.1 as stated:
In order to use subqueries in MySQL you will need a version of MySQL 4.1. Any version would be OK, but as 4.1 is alpha, you’ll definitely want the latest. So, go and grab the newest 4.1 and get it installed before moving on… or just be a spectator and read the examples.
barney0o0, why do you have cat1,cat2,cat3 fields in events table? Do you mean an event can be in multiple categories? Can you please post the whole structure of two tables (categories and events)?
Conditions:
WHERE menu1.cat = events2.cat1 or events2.cat2 or events2.cat3
COUNT events2.show_whatson = ‘yes’
COUNT events2 WHERE events2.endate > present date
Then echo out menu1.title