outputting data from mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

outputting data from mysql

Post 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 ?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: outputting data from mysql

Post by Zoxive »

viewtopic.php?t=77916
Before Post Read: General Posting Guidelines wrote:10. Don't cross-post, once you've chosen the most relevant forum there is no need to post the same question in any of the others.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: outputting data from mysql

Post by aceconcepts »

If my memory serves me correctly you could make use of GROUP BY and different JOIN types.
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: outputting data from mysql

Post 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 ..
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: outputting data from mysql

Post 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 .. :cry:

$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;

}



}
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: outputting data from mysql

Post 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++;
}
}

dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: outputting data from mysql

Post 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
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: outputting data from mysql

Post 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 ?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: outputting data from mysql

Post 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.
Post Reply