SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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?)

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    "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?)
    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/...es_part_1.html

    Quote Originally Posted by barney0o0 View Post
    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
    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)?
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am still unsure that I could understand what you are exactly trying to get but here is the query you can try:
    Code mysql:
    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;
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try grouping the titles then:
    Code mysql:
    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;
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  11. #11
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers Rajug

    Works perfectly

    many thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •