Show if/repeat combo

Hey

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

thanks in advance

its pulled down the menu…

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;

erm…

menu1.title (i.e. Music)

events2.cat1 - 3 (i.e Music)

So the ‘title’ is the category (yes, that was me writing really badly before and confusing the situation)

My second post is right (for the conditions, BUT
WHERE menu1.cat = events2.cat1 or events2.cat2 or events2.cat3,

shouldve been

WHERE menu1.title = events2.cat1 or events2.cat2 or events2.cat3

Maybe it is because of my weak understanding knowledge :stuck_out_tongue: 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?

hey rajug

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

cheers Rajug

Works perfectly

many thanks

So, if there are no matching events, don’t bother showing the menu item, have I understood?

(a very reasonable and courteous thing to aspire to in my book).

If I’m right then you are going to do something like;

“select category from categories where events where cat1 in (select distinct cat1 from events)”

Although this is dependent upon your version of mysql supporting sub-selects (5.1 or better?)

Try grouping the titles then:


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.

Here:
http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

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)?

Cheers Cups…im using 5.0 and im a little confused about your query, so heres some of the elements that i need to use

SELECT events2.eventid, events2.cat1, events2.cat2, events2.cat3, events2.enddate, events2.show_whatson, menu1.cat, menu1.title

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

…does this help…or confuse!

thanks in advance