[SOLVED] Multiple table counts and counter displays

[webent]

Hi all, my code works just fine, but it just seems sloppy, like it could be shortened up somehow, I was wondering if someone could take a look at it and see if they have any ideas... Here's my code...

 

$apparel = mysql_query("SELECT DSDI_SKU FROM apparel WHERE 1");
    $apparel_count = mysql_num_rows($apparel);       
$arts_crafts = mysql_query("SELECT DSDI_SKU FROM arts_crafts WHERE 1");
    $arts_crafts_count = mysql_num_rows($arts_crafts); 
$at_home = mysql_query("SELECT DSDI_SKU FROM at_home WHERE 1");
    $at_home_count = mysql_num_rows($at_home); 
$automotive = mysql_query("SELECT DSDI_SKU FROM automotive WHERE 1");
    $automotive_count = mysql_num_rows($automotive); 
$b2b_services = mysql_query("SELECT DSDI_SKU FROM b2b_services WHERE 1");
    $b2b_services_count = mysql_num_rows($b2b_services); 
$collectibles = mysql_query("SELECT DSDI_SKU FROM collectibles WHERE 1");
    $collectibles_count = mysql_num_rows($collectibles); 
$electronics = mysql_query("SELECT DSDI_SKU FROM electronics WHERE 1");
    $electronics_count = mysql_num_rows($electronics); 
$gift_items = mysql_query("SELECT DSDI_SKU FROM gift_items WHERE 1");
    $gift_items_count = mysql_num_rows($gift_items); 
$jewelry = mysql_query("SELECT DSDI_SKU FROM jewelry WHERE 1");
    $jewelry_count = mysql_num_rows($jewelry); 
$professional = mysql_query("SELECT DSDI_SKU FROM professional WHERE 1");
    $professional_count = mysql_num_rows($professional); 
$sport_outdoor = mysql_query("SELECT DSDI_SKU FROM sport_outdoor WHERE 1");
    $sport_outdoor_count = mysql_num_rows($sport_outdoor);

 

I was thinking something on the lines of ...

 

mysql_query("SELECT DSDI_SKU FROM 
apparel,
arts_crafts,
at_home,
automotive,
b2b_services,
collectibles,
electronics,
gift_items,
jewelry,
professional,
sport_outdoor
    WHERE 1");

 

But for the life of me I wouldn't know how to separate out the $counts...

 

[fenway]

Stop right there... you need to be running COUNT(*)s, not getting back all of the data and then throwing it out... your current implemention is likely much slower as a result.

[webent]

Ok, thank you... That part's done...

 

$apparel = mysql_query("SELECT COUNT(*) FROM apparel");
    $apparel_count = mysql_result($apparel, 0, 0);       
$arts_crafts = mysql_query("SELECT COUNT(*) FROM arts_crafts");
    $arts_crafts_count = mysql_result($arts_crafts, 0, 0); 
$at_home = mysql_query("SELECT COUNT(*) FROM at_home");
    $at_home_count = mysql_result($at_home, 0, 0); 
$automotive = mysql_query("SELECT COUNT(*) FROM automotive");
    $automotive_count = mysql_result($automotive, 0, 0); 
$b2b_services = mysql_query("SELECT COUNT(*) FROM b2b_services");
    $b2b_services_count = mysql_result($b2b_services, 0, 0); 
$collectibles = mysql_query("SELECT COUNT(*) FROM collectibles");
    $collectibles_count = mysql_result($collectibles, 0, 0); 
$electronics = mysql_query("SELECT COUNT(*) FROM electronics");
    $electronics_count = mysql_result($electronics, 0, 0); 
$gift_items = mysql_query("SELECT COUNT(*) FROM gift_items");
    $gift_items_count = mysql_result($gift_items, 0, 0); 
$jewelry = mysql_query("SELECT COUNT(*) FROM jewelry");
    $jewelry_count = mysql_result($jewelry, 0, 0); 
$professional = mysql_query("SELECT COUNT(*) FROM professional");
    $professional_count = mysql_result($professional, 0, 0); 
$sport_outdoor = mysql_query("SELECT COUNT(*) FROM sport_outdoor");
    $sport_outdoor_count = mysql_result($sport_outdoor, 0, 0);

[fenway]

If you'd like, you can combine them into a single "statment", but it would still be running multiple queries... I suppose you'd save the overhead of multiple query() calls.

 

You can do this:

 

( SELECT COUNT(*) FROM gift_items ) AS gift_items,

......

( SELECT COUNT(*) FROM collectibles ) AS collectibles

FROM DUAL

 

 

[webent]

It's only really called when I log into my control center anyway... so if it gets called once a day, I don't suppose it would make much of a difference, there's only 30,000 combined count... I was just wondering if there was a cleaner way... I have a bad habit of using sloppy code...

 

Thanks fenway