Slow Page loading

[Anon]

Hi,

I have 2 tables called categories and companies

the tables have the structure as below:

table name: categories

fields: Categories-Section
CategoryID (primary key)
CategoryName

tables: company

fields: CompanyID
Category-Section
CategoryID (foriegn key)

The Page is supplied with a category section.
I need to output the categoryNames and the number of companies which has these categories.

I have done this using 2 Mysql statements, The First gets the catgories names and category Id. Then the second Mysql counts the number of companies with this infomation.

However, Although this returns the correct infomation the pages loads slowly.

I've tried to join the tables with but no luck.

Is there a better way of doing this???

Thanks

Sue

[amcgrath]

How much data are you dealing with? can you paste the source code?

select count(co.companyID),ca.categoryName from company co, categories ca
where co.categoryID = ca.categoryID
group by categoryName

should work for you....

[Anon]

Hi,

Here's the source code. I'm dealing with about 2000 records.But most of slow page occur when you bring back about 200 or more results.

Thanks

Sue

<?php

include("connectdb.php");

$sql="SELECT Category_Section,CategoryID,Category FROM categories WHERE Category_Section = '$Category_Section' order by Category ";
$results = mysql_query($sql);

$mycount = 0;

while ($myrow = mysql_fetch_array($results)) {

$newID = $myrow['CategoryID'];
$newSection = $myrow['Category_Section'];

$query = "SELECT CompanyID FROM companies_to_categories where CategoryID = $newID and Category_Section = '$newSection'";

$newresult = mysql_query($query);
$myrow1 = mysql_fetch_array($newresult);
$num = mysql_num_rows($newresult);

echo ("category[" . $mycount . "] = new categories(\"" . $myrow["Category"] . "\",'" . $myrow["CategoryID"] . "','" . $myrow["Category_Section"] . "','" . $num . "')" . "\n");


$mycount++;

}

?>

[amcgrath]

Umm, unless I'm missing something, this looks wrong. Try something like this:

<?
include("connectdb.php");

$sql = "select count(ctc.categoryID) as catCount,ctc.Category_Section " .
"from companies_to_categories ctc, categories cat " .
"where ctc.categoryID = cat.categoryID " .
"group by ctc.Category_Section";

$results = mysql_query($sql);

while ($myrow = mysql_fetch_array($results)) {

echo "<li>$myrow[Category_Section] has $myrow[catCount] companies....";

}

?>

I don't use mysql, so i can't debug.

[amcgrath]

The slowness has to be coming from the way your construct the queries (i just read the counts). Think aobut it: if you have 2000 categories, then you are running 2000 queries against the company table... not a good idea. Let me know if my suggestion helps you or not.

[Anon]

Hi Allen,

The query you sent seemed to returned the correct information, however the counting for the no. of companies returned the wrong number.Any Ideas why??

Thanks for all your help so far!!

Sue

[Anon]

Sorry my mistake...it worked....Many Thanks

Sue