dannyd Posted January 24, 2008 Share Posted January 24, 2008 I have a 3 table join that produces results from the query like below: product name | category -------------------------------- cosmetic brush | beauty makeup kitter | beauty ab hammer | fitness ab cruncher | fitness the home skillet | kitchen cnb travel | travel How can I output my results in PHP like this: Beauty Cosmetic brush makeup Kitter Fitness ab hammer ab cruncher Kitchen the home skillet Travel cnb travel any ideas ? Quote Link to comment Share on other sites More sharing options...
revraz Posted January 24, 2008 Share Posted January 24, 2008 Use a GROUP BY in your SQL query and group by Category. Quote Link to comment Share on other sites More sharing options...
dannyd Posted January 24, 2008 Author Share Posted January 24, 2008 That didnt work ... it ends up outputting only 1 product and 1 category see comparison below: Heres my current SQL(without GROUP BY): $sql = 'SELECT a.clientname, c.catlabel FROM clientsegueads a JOIN product_adcategories b ON a.cadid = b.cadid JOIN product_categories c ON b.catid = c.catid WHERE a.disabled = "N" AND a.startdate <= now( ) AND a.stopdate >= now( ) AND a.cadid IN (select cadid from site_categories_bridge WHERE siteid="1") ORDER by c.catlabel ASC'; And heres a piece of the results: Slim N Lift Beauty Hair Club For Men Beauty Scalp Med Beauty Alcis Pain Cream Beauty Dual Action Clense Beauty Youthful Essence Beauty Sheer Cover Beauty Meaningful Beauty Beauty Jerome Alexander Beauty Murad Acne Complex Beauty Hair Free Clinics Beauty Genisphere Beauty Lauren Hutton Face Disc Beauty Tobi Steamer Cleaning Oreck Vacuum Cleaning H2o Vac Cleaning Swivel Sweeper Cleaning H2o Mop Cleaning Mira Bella Mop Cleaning Pure ProAir Cleaning Heaven Fresh PureAir Cleaning Stainz-R-Out Cleaning SRO Woodcare Cleaning SRO VX 100 Cleaning SRO Laundry Sheets Cleaning Green Bags Cleaning Activator Security Electronics Mr Keyz Keyboard Electronics Lifetime of Romance Entertainment Time Life 70s Music Entertainment Malt Shop Entertainment Canadian Music TV Entertainment Classic Soft Rock Entertainment Country Romance Entertainment Opry Classics Entertainment Better Trades Financial Consolidated Credit Financial Tax TV Financial P90 X Fitness Yoga Booty Ballet Fitness Winsor Pilates Fitness The Air Climber Fitness Hip Hop ABS Fitness Bowflex Gym Fitness Orbitrek Elite Fitness Pilates Power Gym Fitness Slim In 6 Fitness Treadclimber Fitness Turbo Jam Fitness Perfect Pushup Fitness Ab Rocket Fitness and so on ......... Heres is the same query with a GROUP BY: $sql = 'SELECT a.clientname, c.catlabel FROM clientsegueads a JOIN product_adcategories b ON a.cadid = b.cadid JOIN product_categories c ON b.catid = c.catid WHERE a.disabled = "N" AND a.startdate <= now( ) AND a.stopdate >= now( ) AND a.cadid IN (select cadid from site_categories_bridge WHERE siteid="1") GROUP BY c.catlabel ORDER by c.catlabel ASC'; And heres the results: Slim N Lift Beauty Tobi Steamer Cleaning Activator Security Electronics Lifetime of Romance Entertainment Better Trades Financial P90 X Fitness Time Life 70s Music Gifts For Her Lifetime of Romance Gifts For Him The Good Feet Store Health H2o Vac Housewares Nuwave Oven Kitchen Paradise Homes Other Paradise Homes Real Estate Quote Link to comment Share on other sites More sharing options...
dannyd Posted January 24, 2008 Author Share Posted January 24, 2008 I figured out this code which almost seems to work but it prints 1 product and then nothing .. see results below code: $sqlcat = 'SELECT a.catid,a.catlabel, count(a.catlabel) FROM product_categories a LEFT JOIN product_adcategories b ON a.catid=b.catid JOIN clientsegueads c ON c.cadid=b.cadid' . ' WHERE c.disabled = "N" AND c.startdate <= now( ) AND c.stopdate >= now( ) AND c.cadid IN (select cadid from site_categories_bridge WHERE siteid="1") GROUP BY a.catlabel'; $result2 = mysql_query($sqlcat); while(list($catid,$catlabel,$count) = mysql_fetch_row($result2)) { echo $catid . ' ' . $catlabel . ' ' . $count . '<BR>'; for ($i=0; $i<$count; $i++) { $sql = 'SELECT a.clientname FROM clientsegueads a JOIN product_adcategories b ON a.cadid = b.cadid JOIN product_categories c WHERE b.catid ="' . $catid . '"'; $result1 = mysql_query($sql); $row=mysql_fetch_field($result1); echo $i . '--->' . $row[$i] . "<BR>"; } } RESULTS: 10002 Beauty 13 0---> 1---> 2---> 3---> 4---> 5---> 6---> 7---> 8---> 9---> 10---> 11---> 12---> 10021 Cleaning 13 0---> 1---> 2---> 3---> 4---> 5---> 6---> 7---> 8---> 9---> 10---> 11---> 12---> 10017 Electronics 2 0---> 1---> 10008 Entertainment 7 etc. any ideas ??? Quote Link to comment Share on other sites More sharing options...
dannyd Posted January 24, 2008 Author Share Posted January 24, 2008 its just not outputting the product names but lists the number of products. In the for loop how can I output the product name ? echo $row[$i]; just doesnt seem to work. Quote Link to comment Share on other sites More sharing options...
resago Posted January 24, 2008 Share Posted January 24, 2008 I have a 3 table join that produces results from the query like below: product name | category -------------------------------- cosmetic brush | beauty makeup kitter | beauty ab hammer | fitness ab cruncher | fitness the home skillet | kitchen cnb travel | travel How can I output my results in PHP like this: Beauty Cosmetic brush makeup Kitter Fitness ab hammer ab cruncher Kitchen the home skillet Travel cnb travel any ideas ? if you had: catagory array[] items array[][] then: array_unique(catagory) then: foreach cat in catagory echo cat foreach item in items[cat] echo item you would have to swap the columns in this example, but it should work. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.