Please help me speed up - There must be a better way.

[dv8er88]

Hi This might be a basic question but I'm not very good with MySql.

I have 3 tables
cat: The Main Category
scat: The Sub Category
product: The Product info, cat_ID and scat_ID

I need to output all product BY Main Category then sub category.

Right Now I get all the records for Main Category then
loop though and in the loop I get all Sub Categories Where cat_ID is the current Main Category.

Then I loop through again and get all product where the scat_ID id the current s_cat.

Then I loop through and output the product info.

Anyway the page load times are not great and I'm sure I am hitting the database way to much.

Thanks in advance,
D

 

[r937]

any time you find yourself doing a query inside a loop, you are almost certainly doing something wrong

I need to output all product BY Main Category then sub category

select m.name as category_name
     , s.name as subcategory_name
     , p.product_info
  from product as p
inner
  join scat as s
    on s.id = p.scat_id
inner
  join cat as c
    on c.id = p.cat_id
order
    by m.name 
     , s.name 
     , p.product_info

r937.com | rudy.ca

 

[dv8er88]

Thanks, I am trying to apply this but I'm getting this error:

Unknown column 'm.name' in 'field list'

Here is my code:
<CFQUERY Name="get" datasource="#var_datasource#" username="#var_username#" password="#var_password#">


select
m.name as cat_name
, s.name as scat_name
, p.name
from product as p

inner join
scat as s
on s.ID = p.scat_ID

inner join
cat as c
on c.ID = p.cat_ID

order
by m.name
, s.name
, p.name

</CFQUERY>

<cfoutput query=" get">
#m.name# - #s.name# - #p.name# <br />
</cfoutput>

 

[r937]

sorry, change it to c.name in both places

r937.com | rudy.ca

 

[dv8er88]

Now I get:

Unknown column 'c.name' in 'field list'


Thanks

 

[r937]

does your cats table actually contain a column called name?

i was just guessing at the columns, because you didn't show your table layouts

r937.com | rudy.ca

 

[dv8er88]

OK sorry for not being more specific. Here is the result I need and below is my table structure:

Here is a link to the actual page as it works now:

http://sreptiles.delcoro.com/price_list/plist.cfm

One more thing, I need to output everything alphabetically with a few acceptions. Specials and New Arrivals needs to be the 1st Category and Imperfect needs to be the last. So I added the priority field to the cat table and I am ordering by priority,cat_name

Specials and New Arrivals is priority 0
Imperfect is priority 2
everything else is priority 1

Example:
Specials and New Arrivals :NO LIVE ARRIVAL GUARANTEE!!!(Note: Specials and New Products is the cat_name - :NO LIVE ARRIVAL GUARANTEE!!! is the cat_desc from the cat table)
Frog and Toad: (Note: Frog and Toad is the scat_name from the scat table)
Product 1 - Price1 - Price 2 (Note: Product 1 is the name and Price1 is p1 from the product table)
Product 2 - Price1 - Price 2
Product 3 - Price1 - Price 2
Product 3 - Price1 - Price 2

Lizard: (Note: Lizard is the scat_name from the scat table)
Product 1 - Price1 - Price 2 (Note: Product 1 is the name and Price1 is p1 from the product table)
Product 2 - Price1 - Price 2
Product 3 - Price1 - Price 2
Product 3 - Price1 - Price 2

Arachnid :NO LIVE ARRIVAL GUARANTEE!!! (Note: Arachnid is the cat_name - :NO LIVE ARRIVAL GUARANTEE!!! is the cat_desc from the cat table)
Scorpion: (Note: Scorpion is the scat_name from the scat table)
Product 1 - Price1 - Price 2 (Note: Product 1 is the name and Price1 is p1 from the product table)
Product 2 - Price1 - Price 2
Product 3 - Price1 - Price 2
Product 3 - Price1 - Price 2

Spiders & Tarantulas: (Note: Lizard is the scat_name from the scat table)
Product 1 - Price1 - Price 2 (Note: Product 1 is the name and Price1 is p1 from the product table)
Product 2 - Price1 - Price 2
Product 3 - Price1 - Price 2
Product 3 - Price1 - Price 2



--
-- Table structure for table `cat`
--

CREATE TABLE `cat` (
`cat_ID` int(11) NOT NULL auto_increment,
`cat_name` varchar(60) NOT NULL,
`cat_desc` varchar(200) NOT NULL,
`image` varchar(200) NOT NULL,
`priority` int(11) NOT NULL default '1',
PRIMARY KEY (`cat_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `scat`
--

CREATE TABLE `scat` (
`scat_ID` int(11) NOT NULL auto_increment,
`cat_ID` int(11) NOT NULL,
`scat_name` varchar(60) NOT NULL,
`scat_desc` varchar(200) NOT NULL,
`image` varchar(200) NOT NULL,
PRIMARY KEY (`scat_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;


-- --------------------------------------------------------
--
-- Table structure for table `product`
--

CREATE TABLE `product` (
`ID` int(11) NOT NULL auto_increment,
`cat_ID` int(11) NOT NULL,
`scat_ID` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`latin` varchar(200) NOT NULL,
`p1` varchar(100) NOT NULL,
`p6` varchar(100) NOT NULL,
`p12` varchar(100) NOT NULL,
`p25` varchar(100) NOT NULL,
`free_form` varchar(255) NOT NULL,
`active` int(11) NOT NULL default '1',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=532 ;

 

[r937]

just change the column names in the query to match the column names in your tables -- you'll get errors if you skip any

r937.com | rudy.ca