SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Muliple stores within subcategories

    I have 3 tables, Category, Subcategory and Stores

    What I want to do is list like this

    Heading --->Category
    Subheading --->Subcategory
    Store ----->Store Name

    Subheading--->Subcategory
    Store------>Store Name

    Basically it lists the main selected category with the relevant subcategories and stores assocaited with them within a loop. Now I've got this working using a LEFT Join within my query. The columns counter allows the stores to be displayed in 4 columns. However, each subcategory has one store in it. I want to be able to have stores appear in multiple subcategories. What is the best way of doing this? I was thinking of a lookup table (subcategories_to_stores) but how do I set it up to work with the query? Or is there another better way of doing this?

    Code:

    $result = mysql_query("SELECT s.subcatName, s.store_name, s.thumb_nail FROM subcategory s LEFT JOIN store s s.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

    $columns_counter=4;

    echo '<tr>';

    $currentCat = '';
    while($row = mysql_fetch_array($result)) {
    $subcatName = $row['subcatName'];
    $merchant_name = $row['store_name'];
    $thumb_nail = $row['thumb_nail'];

    extract($row);
    if ($currentCat != $subcatName) {
    //if a new category, write category head
    echo "<tr><td class='titlebar_sml' colspan=\"4\">$subcatName</td></tr>";
    $currentCat = $subcatName;
    }

    if(isset($store_name)) {
    echo '<td align = "center" width="25%">';
    echo '<A HREF="'.$store_name.'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$store_name).'">'.$store_name.'</A></H4>';

    echo '</td>';

    }else{
    echo '<tr><td align ="center" colspan=\"4\">Sorry but this category is empty.</td></tr>';

    }
    $columns_counter--;
    if(!$columns_counter)
    {
    echo '</tr><tr>';
    $columns_counter=4;
    }
    }

    echo '</tr></table>';

  2. #2
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's some more info about my problem.

    At the moment I am storing the selected subcategory ids in a column called msubcid within my store table. I have used implode to create an array with the subcategory ids in e.g. 1,2,3

    Can I use explode and loop to get the subcategory id from the array and use it within my existing query to build the subcategory tree?

    Any help would be greatly appreciated as I'm running out of ideas on this one and I need to accept multiple stores within subcategories.

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am facing a similar problem.

  4. #4
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can anyone help? Hate to ask but this forum has so many helpful people on it that I thought that this was the best place to ask.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm probably not the only one who isnt clear on exactly what you want to achieve. Maybe take some time to provide better examples. Use formatting when you post, like [code] and [php] tags.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This what your looking at in terms of a schema:
    stores
    • id (primary key)
    • name


    category_types
    • id (primary key)
    • name


    categories
    • id (primary key
    • category_type (references category_types(id))
    • name


    store_categories (look-up table)
    • store (references stores(id))
    • category (references categories(id))
    • primary key(store,category)


    Associations
    • stores belongs to and has many categories (through store_categories)
    • category_type has many categories
    • category belongs to category_type

  7. #7
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for my formatting. Oddz, that look about right.

    [Code]

    <?php
    $result = mysql_query("SELECT s.subcatName, s.store_name, s.thumb_nail FROM subcategory s LEFT JOIN store s s.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

    $columns_counter=4;

    echo '<tr>';

    $currentCat = '';
    while($row = mysql_fetch_array($result)) {
    $subcatName = $row['subcatName'];
    $merchant_name = $row['store_name'];
    $thumb_nail = $row['thumb_nail'];

    extract($row);
    if ($currentCat != $subcatName) {
    //if a new category, write category head
    echo "<tr><td class='titlebar_sml' colspan=\"4\">$subcatName</td></tr>";
    $currentCat = $subcatName;
    }

    if(isset($store_name)) {
    echo '<td align = "center" width="25%">';
    echo '<A HREF="'.$store_name.'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$store_name).'">'.$store_name.'</A></H4>';

    echo '</td>';

    }else{
    echo '<tr><td align ="center" colspan=\"4\">Sorry but this category is empty.</td></tr>';

    }
    $columns_counter--;
    if(!$columns_counter)
    {
    echo '</tr><tr>';
    $columns_counter=4;
    }
    }

    echo '</tr></table>';
    ?>

  8. #8
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    I'm probably not the only one who isnt clear on exactly what you want to achieve. Maybe take some time to provide better examples. Use formatting when you post, like [code] and [php] tags.
    Sorry if it's not clear let me try and explain a bit more.

    I would like to display data like this;

    Category Name
    ------------------

    Subcategory Name
    ----------------------

    Store Name Store Name Store name
    Store Image Store Image Store Image

    Subcategory Name
    ----------------------

    Store Name
    Store Image

    and so on.....

    I want all subcategories and stores per category to be displayed in this way. There will be stores that belong to more than one subcategory.

    The code I have posted on this thread currently does exactly what I need but only allows for one store to be displayed not more than one.

    I realise that the LEFT JOIN is allowing this to happen but that is set up with single values. i.e.

    msubcid 1, subcid 1
    msubcid 4, subcid 4

    My main question is what is the best way to allow multiple ids to be placed into the msubcid column and query to bring it out into the query.

    i.e.
    msubcat 1, subcid 1
    msubcat 1, subcat 3

    I hope that this explains a bit better about what I want to achieve.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

  10. #10
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry Oddz, that topic is just way over my head. Not a seasoned pro in php, just starting out, know a little bit to build simple queires but as you probable realise from my questions not a Php wizard like yourself.

    Any chance you could perhaps simplify or explain it a little.

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What does the result set look like?

    Could you also post the actual tables.

    Th best way to do this is to use the primary keys. A primary key identities a unique node. Once I see the tables I'll explain why this matters.

  12. #12
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    What does the result set look like?

    Could you also post the actual tables.

    Th best way to do this is to use the primary keys. A primary key identities a unique node. Once I see the tables I'll explain why this matters.
    The result set looks like

    [Code]
    <?php

    $result = mysql_query("SELECT s.subcatName, s.store_name, s.thumb_nail FROM subcategory s LEFT JOIN store s s.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

    ?>

    Best way of adding tables to this forum?

    [End of code]
    Last edited by freakystreak; May 14, 2009 at 06:53. Reason: adding content

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    there is no result set because that query cannot execute, it has syntax errors

    you cannot assign the same table alias ("s") to two different tables, and the ON clause is missing
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, sorry getting a bit confused here, been adjusting and swapping things trying to get it to work.

    [code]
    <?php

    $result = mysql_query("SELECT s.subcatName, m.store_name, m.thumb_nail FROM subcategory s LEFT JOIN store m ON m.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

    ?>

    [Code]

    What's the best way of putting tables here for people to view and investigate?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by freakystreak View Post
    What's the best way of putting tables here for people to view and investigate?
    run the SHOW CREATE TABLE command for each table, and show the results like this --
    Code:
    CREATE TABLE games 
    ( gameID        INTEGER     NOT NULL
    , datePlayed    DATE        NOT NULL
    , location      VARCHAR(99) NOT NULL
    , homeTeamID    INTEGER     NOT NULL
    , awayTeamID    INTEGER     NOT NULL
    , homeTeamScore INTEGER      NULL
    , awayTeamScore INTEGER      NULL
    ) ENGINE=InnoDB    CHARSET=latin1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here are my tables:

    CREATE TABLE `category` (
    `cid` int(8) NOT NULL auto_increment,
    `category_name` varchar(30) NOT NULL,
    `category_description` varchar(200) NOT NULL,
    `active` int(1) NOT NULL default '1',
    PRIMARY KEY (`cid`)
    )

    CREATE TABLE `subcategory` (
    `subcid` int(8) NOT NULL auto_increment,
    `subcatName` varchar(35) NOT NULL,
    `cid` int(8) NOT NULL,
    `active` int(1) NOT NULL,
    PRIMARY KEY (`subcid`)
    )

    CREATE TABLE `store` (
    `id` int(11) NOT NULL auto_increment,
    `store_name` varchar(200) default NULL,
    `text_content` text,
    `thumb_nail` text,
    `url` varchar(50) default NULL,
    `msubcid` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
    )

    Thanks to r937 for the tip.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT s.subcatName
         , m.store_name
         , m.thumb_nail 
      FROM subcategory s 
    LEFT OUTER 
      JOIN store m 
        ON m.msubcid = s.subcid 
     WHERE s.cid = $categorytarget 
       AND s.active = 1 
    ORDER 
        BY s.subcatName ASC
    just minor changes to your query --

    - indentation and line breaks
    - fully qualified column names
    - no quotes around numeric values
    - always code optional OUTER keyword

    as i said, these are just minor, your query should run the way you wrote it, too

    what was your problem again?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My problem is that I want to have stores that appear in more than one subcategory. At the moment it only shows 1 store per subcategory.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by freakystreak View Post
    My problem is that I want to have stores that appear in more than one subcategory. At the moment it only shows 1 store per subcategory.
    actually, the table design allows only one subcategory per store, but many stores per subcategory

    if you really want a store to appear in more than one subcategory, you need another table to relate them (and you will need to remove msubcid from the store table)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, thanks but how would I join the tables together within my query?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you would join the tables with JOIN syntax

    but first, you gots to redesign the tables

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Yeah, your tables won't support a many to many relationship. If your having trouble from what I recall this post should be along the lines of how the tables should be set-up. Once you have those in place then the application level parsing can be discussed.

  23. #23
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,

    I suspected my tables would need to be redesigned. I will take another look at designing them into a format that will work to what I need.

  24. #24
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Had a look at my tables and have redone them plus included a new (lookup) stores_categories table.

    CREATE TABLE `store` (
    `id` int(11) NOT NULL auto_increment,
    `store_name` varchar(200) default NULL,
    `text_content` text,
    `thumb_nail` text,
    `url` varchar(50) default NULL,
    PRIMARY KEY (`id`)
    )

    CREATE TABLE `subcategory` (
    `id` int(8) NOT NULL auto_increment,
    `subcatName` varchar(35) NOT NULL,
    `cid` int(8) NOT NULL,
    `active` int(1) NOT NULL,
    PRIMARY KEY (`id`)
    )

    CREATE TABLE `category` (
    `cid` int(8) NOT NULL auto_increment,
    `category_name` varchar(30) NOT NULL,
    `category_description` varchar(200) NOT NULL,
    `active` int(1) NOT NULL default '1',
    PRIMARY KEY (`cid`)
    )

    CREATE TABLE `stores_categories` (
    `merchant_id` int(8) NOT NULL,
    `subcategories_id` int(8) NOT NULL,
    PRIMARY KEY (`merchant_id`,`subcategories_id`)
    )

    What would I have to adjust/change to my existing query or in fact would this have to be rewritten? I have done some searching on Google and there are some good tutorials on joins but not with a third, look up table like I need.

    I understand that my current query 'JOIN ON' the tables with msubcid but how do I make the join with a lookup table? Confused.

    My existing query is:
    Code:
    PHP Code:
    $result mysql_query("SELECT s.subcatName, m.merchant_name, m.thumb_nail FROM subcategory s LEFT JOIN merchant m ON m.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " mysql_error()); 

  25. #25
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries, I have sorted it myself and it seems to be working great.


Tags for this Thread

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
  •