Page 1 of 1
outputting data from mysql
Posted: Thu Jan 24, 2008 9:10 am
by dannyd
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 ?
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 9:51 am
by Zoxive
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 10:13 am
by aceconcepts
If my memory serves me correctly you could make use of GROUP BY and different JOIN types.
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 10:20 am
by dannyd
Thanks for the response!
I tried with a group by but 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
Not sure what joins I could use to get the result ..
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 10:45 am
by dannyd
Would something like this work ? by creating two separate sql statements ? Is this a good way of solving this problem ? Not sure if my syntax is correct ..
$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 $catlabel . '<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 ON b.catid ="' . $catid . "';
$result1 = mysql_query($sql);
mysql_fetch_row($result1);
list($clientname) = mysql_fetch_row($result1);
echo $clientname;
}
}
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 11:21 am
by dannyd
Can anyone correct my syntax or tell me what i could be doing wrong ?
$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 $catlabel . '<BR>';
$i=0;
$num = $count;
while ($i < $num) {
$sql = 'SELECT a.clientname FROM clientsegueads a JOIN product_adcategories b ON a.cadid = b.cadid JOIN product_categories c ON b.catid ="' . $catid . "';
$result1 = mysql_query($sql);
$clientname=mysql_fetch_row($result1);
echo $clientname;
$i++;
}
}
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 12:06 pm
by dannyd
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
Re: outputting data from mysql
Posted: Thu Jan 24, 2008 12:25 pm
by dannyd
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>";
}
can anyone correct my syntax in this for loop ?
Re: outputting data from mysql
Posted: Fri Jan 25, 2008 3:58 am
by aceconcepts
You don't need to use a for loop. If you want to use a nested loop then simply use the foreign keys that realte to the parent loop's primary keys.
e.g.
Code: Select all
//PARENT LOOP - LOOP THROUGH EACH CATEGORY I.E. SPORTS GOODS
$catSql=mysql_query("SELECT * FROM tblCategory ORDER BY whatever");
while($catRow=mysql_fetch_array($catSql))
{
$catId=$catRow['categoryId']; //GET THE CATEGORY ID FOR EACH LOOP
echo $catRow['categoryName'] . '<br />'; /DISPLAY THE CATEGORY - THIS WILL APPEAR AS A HEADING FOR EACH CATEGORY
//NESTED LOOP - GET EACH PRODUCT RELATED/LINKED TO CATEGORY I.E. FOOTBAL, BASEBALL BAT
$prodSql=mysql_query("SELECT * FROM tblProduct WHERE categoryId='$catId' ORDER BY whatever"); /REMEMBER TO DECLARE A "WHERE" CONDITION
while($prodRow=mysql_fetch_array($prodSql))
{
echo $prodRow['productName'] . '<br />'; //DISPLAY YOUR PRODUCTS
}
echo'<br /><br />'; //MAKE A GAP BETWEEN THE LISTS
}
I dont think I missed anything. it's all rather crude but it gives you an idea.