Jump to content


Photo

displaying items in groups


  • Please log in to reply
4 replies to this topic

#1 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 07 May 2006 - 06:53 AM

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!!

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 07 May 2006 - 08:22 AM

i guess it kinda depends on how your table(s) is/are setup
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 07 May 2006 - 10:06 AM

Assuming

category           company            item
==========         ==========         ==========
catID    <---+     compID   <---+     itemID
category     |     coname       |     itemname
             +---- catID        +---- compID

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 07 May 2006 - 11:58 PM

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.


<?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";
}
?>


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.

#5 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 08 May 2006 - 05:37 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users