displaying items in groups

[darga333]

hi there! I have an indepth script that needs to display categories, and then it needs to loop through the database and display all the businesses for that category, but then while it is on that business name it needs to display all the items for that business name, then once there are no more business names for that category, it needs to move to the next category and do the same thing until it displays every category.

We would make sure that the category name is only displayed once, and that the business name is only displayed once

for instance...

you have categories: food, auto, retail

This is what you want to display

Food

McDonalds
Big Mac
Fries
Shake

Checkers
Fries
Spicy Chicken Sandwhich
Soft drink


Auto

Midas
Brakes
Transmission

Mobil
Oil Changes
Rodars
Tires
Transmission

Retail
Walmart
Electronics
Food
Clothes
Garden Supplies

Thank you so much for the help!!

[.josh]

i guess it kinda depends on how your table(s) is/are setup

[Barand]

Assuming

[code]category           company            item
==========         ==========         ==========
catID    <---+     compID   <---+     itemID
category     |     coname       |     itemname
             +---- catID        +---- compID[/code]

then
[code]    $sql = "SELECT ca.category, co.coname, it.itemname
            FROM item it
                INNER JOIN company co ON it.compID = co.compID
                INNER JOIN category ca ON co.catID = ca.catID
            ORDER BY ca.category, co.coname, it.itemname";
            
    $res = mysql_query($sql) or die(mysql_error());
    
    $lastcat = $lastco = '';
    while (list($cat, $co, $item) = mysql_fetch_row($res)) {
        // has category changed?
        if ($lastcat != $cat) {
            echo "<H1>$cat</H1>\n";
            echo "<H2>$co</H2>\n";
        }
        elseif ($lastco != $co) {
            echo "<H2>$co</H2>\n";
        }
        echo "$item<br />";
        
        $lastcat = $cat;
        $lastco = $co;
    }
[/code]

[darga333]

I sincerely apologize but I tried to see how I could incorporate what you wrote but if I was to use that I would have to rewrite the entire script and it is very long. I want you to know that I did study the code that you wrote and I appreciate that you took the time to write that out. Hopefully it will help someone else that reads this post.

As for the script I have. It is 100% complete, I just cant find out how to format it as follows:

When I run it, it needs to display the following: It needs to print the first category name ASC,.. then it needs to loop through and display the first business name in that category ASC, then it needs to display all of the discounts for that business name... after it displays all the discounts for that business, then it needs to go through every business name and do the same thing for the rest of the businesses in that category. After it is finished going through all the business names in that category it needs to move on to the next category and do the same thing until it has went through every category it finds in the SQL statement.

I have a 75 line script if you do not mind me posting it.

Here is the code.


[code]<?php
$yesterday = "2006-05-06";

//Select all discounts in all categories for each discount that has been updated today

$sql_1  = "SELECT *,ucfdiscountcategory.sName,ucfdiscount.sDescription FROM `ucfdiscount`,ucfdiscountcategory,ucfmembers WHERE ucfmembers.user_id = ucfdiscount.iMemberId AND ucfdiscount.sActive = 1 AND ucfdiscount.deleted != 1 AND ucfdiscount.last_update = '$yesterday' AND ucfdiscount.iDisCategoryId = ucfdiscountcategory.idiscategoryid ORDER BY ucfmembers.sBusinessname, ucfdiscountcategory.sName ASC";
$result_1 = mysql_query($sql_1);
$num33 = mysql_num_rows($result_1);

//If there is atleast one row send an email with all the discounts.        
if ($num33 > 0)  {  
    
    $a = 1;
    $discount_message = "";
    while($row_1 = mysql_fetch_array($result_1))  {
    $business_name     = stripslashes($row_1['sBusinessname']);
    $user_id        = stripslashes($row_1['user_id']);
    if (!isset($current_catid) || $current_catid != $row_1['iDisCategoryId'])  {
        $current_catid = $row_1['iDisCategoryId'];
        $discount_message .= "<hr>";
        $discount_message .= "<h2>".stripslashes($row_1['sName'])."</h2>";
    }
    if (!isset($sBusinessname) || $sBusinessname != $row_1['sBusinessname'])  {
        $sBusinessname = $row_1['sBusinessname'];
        $discount_message .= "<br>";
        $discount_message .= "<p><strong><font color='red'>
                                ".stripslashes($sBusinessname)."</font></strong><br>\n";
                }
    $discount_message .= "<strong>".stripslashes($row_1['sHeadlines'])."</strong><br>\n";
    $discount_message .= stripslashes($row_1['sDescription'])."<br>\n";
    $discount_message .= stripslashes($row_1['sBusinessPhone'])."<br>\n";
    $discount_message .= "<a href=\"viewcoupon.php?id=".$row_1['idiscountid']."\">
                                                   View this coupon!</a><br /><br />";
    $a++;
}
        
    //All people that have iNewDisc = 1 ((all people that have elected to receive an email))
    $sql_0 = "SELECT t1.user_password,t1.username,t1.user_email,
                               t1.user_id,t1.sFirstName,t1.sLastName, t2.imemberid,t2.iNewDisc
                               FROM ucfnotifications t2,ucfmembers t1
                               WHERE t2.iNewDisc=1 and t2.imemberid=t1.user_id AND t1.bBusiness = 0
                               ORDER BY t1.user_id ASC";
    $result_0 = mysql_query($sql_0);

    while($row_0 = mysql_fetch_array($result_0)){
    $user_id_member     = $row_0['user_id'];
    $rep= stripslashes($row_0['sFirstName']);
    $rep.= " ".stripslashes($row_0['sLastName']);
    $username    = stripslashes($row_0['username']);
    $pass        = stripslashes($row_0['user_password']);
    $user_email = stripslashes($row_0['user_email']);

    //Format the email
    $message = file_get_contents("../templates/new_discounts.php");
    $message = str_replace("%REP%",$rep,$message);
    $message = str_replace("%USERNAME%",$username,$message);
    $message = str_replace("%PASS%",$pass,$message);
    $message = str_replace("%DISCOUNT_MESSAGE%",$discount_message,$message);            
    echo $message;
    
    // stuff for all emails
    $subject           = "This is the subject!";  
    $from              = "support@testing.com";
    $headers           = "From: $from\n";
    $headers          .= "MIME-Version: 1.0\r\n";
    $headers          .= "Content-type: text/html; charset=iso-8859-1\r\n";
    
    //Mail the members (I have this commented out so it doesnt email members while testing)
    //mail("$user_email","$subject","$message","$headers") or die('Could not send mail!');

    //Store a value in the database so we know who we have sent mail to    
    $sql_3 = " INSERT INTO `tmp_mail_count` VALUES ('$user_id_member') ";
    $result_3 = mysql_query($sql_3);
        
    }

echo "<h1>You are finished!</h1>";
}else{
echo "No new discounts exist for today";
}
?>[/code]


You will notice the SQL statements are pretty in depth thats because there is a lot of info i need to provide.. i cleared out about 100 lines of code to make it easier to post on here.

[darga333]

I am not a fan of large code posts. I apologize for that. I understand the main thing we need to look at here are the two loops.