[Solved] Loop difficulty

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
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

[Solved] Loop difficulty

Post by Addos »

Edited
Last edited by Addos on Tue Aug 12, 2008 1:40 pm, edited 2 times in total.
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Loop difficulty

Post by flying_circus »

You need to run 2 different queries. Look in the mysql manual as well as do a search for "while" loops.


You need to get all of the headings from the database.
Then for each heading, you need to get all the prodocuts.

Code: Select all

<?php
 
  $output = '';
 
  mysql_select_db($database_******, $*****);
 
  $xHeadings = mysql_query("SELECT category_Name FROM headingstable");
 
  while($xHeading = mysql_fetch_assoc($xHeadings)) {
    $output .= '<h3>' . $xHeading['category_Name'] . '</h3>';
 
    $xProducts = mysql_query("SELECT product_ID, product_name FROM productstable WHERE category_name = '$xHeading['category_name']'");
    
    while($xProduct = mysql_fetch_assoc($xProducts)) {
      $output .= '<a href="details.php?prodId=' . $xProduct['product_ID'] . '" class="more">' . $xProducts['product_Name'] .'</a><br>';
    }
    print $output;
  }
?>
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: Loop difficulty

Post by Addos »

Edited
Last edited by Addos on Tue Aug 12, 2008 1:41 pm, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Loop difficulty

Post by califdon »

Your query just needs to sort by the headings, then when you fetch the rows in a while loop, you store the heading each time you fetch a row, and compare it with the last one you fetched, if it's different, you echo the heading, if it's the same, you don't. You have to remember to start with a value to be compared. Your while loop should look something like this:

Code: Select all

$lastheading="";
while ($row = mysql_fetch_array($query)) {
    if ($lastheading != $row[0]) {     // assuming $row[0] contains the heading
        echo "<strong>$row[0]</strong><br />";
    }
    $lastheading = $row[0];
    echo "&nbsp; &nbsp; $row[1]<br />";     // assuming $row[1] contains the detail
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Loop difficulty

Post by RobertGonzalez »

For performance I would suggest you run two queries. The first fetches the categories (like califdon suggested). The second fetches all the rows that you are going to display. This is different from califdon's suggestion which has you hitting the database for each category. If you had 100 categories that would be 100 queries to run to get what you want.

What I am suggesting gets all of the data up front. Then you run two loops. The first loop loops the categories. Within each iteration of the loop you run another loop that loops all of the rows. In this loop you compare the cat id for the row against the current cat loop row id. If it matches, display it.

2 queries and loop logic blocks. Nice and simple.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: Loop difficulty

Post by Addos »

Edited
Last edited by Addos on Tue Aug 12, 2008 1:42 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Loop difficulty

Post by RobertGonzalez »

This is what I am talking about. This is untested, but I think you can gleam from this what is necessary.

Code: Select all

<?php
mysql_select_db($database_888888, $88888);
$sql = "SELECT * 
        FROM tbl_prdtcategories,tbl_prdtcat_rel,tbl_products
        WHERE prdt_cat_rel_Cat_ID = category_ID
        AND product_ID =prdt_cat_rel_Product_ID";
$result = mysql_query($query_GetHeadings, $armstrong) or die(mysql_error());
 
// Set your arrays - this can be done with one but for clarity we will use two
while ($row = mysql_fetch_array($result)) {
    $cats = $row[];
}
$rows = $cats;
$catcount = $rowcount  = count($cats);
 
// Now loop cats
for ($i = 0; $i < $catcount; $i) {
    $catid  = $cats[$i]['category_ID'];
    echo '<h3>', $cats[$i]['category_Name'], '</h3>';
 
    // Ok we echoed the heading, now loop the rows to see if any match
    for ($j = 0; $j < $rowcount; $j++) {
        if ($rows[$j]['prdt_cat_rel_Cat_ID'] == $catid) {
            echo '&raquo; ', $rows[$j]['product_Name'], ' is the product<br />';
        }
    }
}
?>
Or something along these lines.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Loop difficulty

Post by califdon »

Everah wrote:For performance I would suggest you run two queries. The first fetches the categories (like califdon suggested). The second fetches all the rows that you are going to display. This is different from califdon's suggestion which has you hitting the database for each category. If you had 100 categories that would be 100 queries to run to get what you want.

What I am suggesting gets all of the data up front. Then you run two loops. The first loop loops the categories. Within each iteration of the loop you run another loop that loops all of the rows. In this loop you compare the cat id for the row against the current cat loop row id. If it matches, display it.

2 queries and loop logic blocks. Nice and simple.
What am I missing here?? Why 2 queries? (and I see only one in your example, Everah) I didn't show the query in my earlier post, but it looks to me like a straightforward many-to-many relationship, so why not just a single query that returns just two columns, Category and Product, ordered by Category? Are you saying that it's an optimization issue? I don't see where the number of categories would dictate the number of queries. How about this:

Code: Select all

<?php
mysql_select_db($database_888888, $88888);
$sql = "SELECT categoryname, productname
       FROM tbl_prdtcategories,tbl_prdtcat_rel,tbl_products
       WHERE prdt_cat_rel_Cat_ID = category_ID
       AND product_ID =prdt_cat_rel_Product_ID
       ORDER BY categoryname";
$result = mysql_query($sql) or die(mysql_error());
 
$lastheading="";
while ($row = mysql_fetch_assoc($query)) {
    extract($row);
    if ($lastheading != $categoryname) { 
        echo "<strong>$categoryname</strong><br />";
    }
    $lastheading = $categoryname;
    echo "&nbsp; &nbsp; $productname<br />";
}
?>
Wouldn't that do the whole job? Am I missing something? Is it too early in the morning? :wink:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Loop difficulty

Post by RobertGonzalez »

Wow... let me see if I can answer all those questions :wink:

Firstly I hadn't seen the query. His query right now is pulling all of the products along with all of the ids. The way I read it at first it seemed like a one to many relationship. One category <-> many products.

Your original example showed a query that fetched the categories, then while looping that dataset, querying again based on the category. If there were 100 categories this would lead to 100 more trips to the database server as the loop querying continues. This is an application performance thing in my opinion, not a database issue.

In my example I showed how you can loop the cats dataset and, while you are in a category, loop the rows dataset to match against cat id per row. This needs only two trips to the database server. Granted the rows dataset will be inherently larger, but rather than running 101 queries to do this you can get away with 2.

Did I answer your questions appropriately? If not, fire back.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Loop difficulty

Post by califdon »

Story of my life--getting blamed for what the other kid did! :| No, that was another poster who proposed 2 queries. I just replied to the OP and said that all he basically needed to do was add an ORDER BY clause, so I didn't even repeat his query, only tried to show him how to determine when to insert a new header. As I understand the OP's problem, he has a categories table and a products table and a 'middle' table to form a many-to-many relationship. And he wants to just list products within categories. If there's something more to it than that, I've missed it. In those circumstances, one query should do it all, with only one call to the database. The result set will be in category order, so he only needs to insert a header when the category changes. Routine stuff, unless I'm still missing something.

Anyway, glad to see that you were up so early this morning. :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Loop difficulty

Post by RobertGonzalez »

Crap damn. You were right Don. I must have cross-contaminated post topics. I think you nailed it with what you said. I think I am wrong in this case.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: Loop difficulty

Post by Addos »

Thanks very much for the help it was very informative. I finally got this working and in the end used flying_circus suggestion. What through me as the .= in the first loop and when I removed this . it worked a treat. Big learning (painful) curve but a lot gained.
Thanks again. :wink:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: [Solved] Loop difficulty

Post by RobertGonzalez »

flying_circus' solution was the solution that I was telling you could be done with only two queries. Running a query inside a loop means that for each iteration of the loop you are running another query. If you had 50 categories that you were looping then you would, in essence, be running 50 queries inside of that loop. You can do the same thing with literally two queries, which was my suggestion.

However, if you got it working then you got it working. From a performance perspective though, you may want to reconsider how you are building your loop/query combination.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: [Solved] Loop difficulty

Post by Chris Corbyn »

Image
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: [Solved] Loop difficulty

Post by flying_circus »

Chris Corbyn wrote:Image
Hah!

While my solution does work, it's not the most efficient. I'm glad I posted in this thread though, I learned something from Everah and CaliDon. One way or the other, glad you got it working.
Post Reply