Muliple stores within subcategories

[freakystreak]

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>';

[freakystreak]

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.

[co.ador]

I am facing a similar problem.

[freakystreak]

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.

[crmalibu]

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.

[oddz]

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

[freakystreak]

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>';
?>

[freakystreak]

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.

[oddz]

Same idea different context

[freakystreak]

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.

[oddz]

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.

[freakystreak]

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]

[r937]

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

[freakystreak]

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?

[r937]

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

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

[freakystreak]

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.

[r937]

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?

[freakystreak]

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.

[r937]

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)

[freakystreak]

OK, thanks but how would I join the tables together within my query?

[r937]

you would join the tables with JOIN syntax

but first, you gots to redesign the tables

[oddz]

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.

[freakystreak]

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.

[freakystreak]

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:

	
	
$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()); 

[freakystreak]

No worries, I have sorted it myself and it seems to be working great.