SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    simple loop question

    I made a post over in the mySQL section of the forum about trying to reduce the number of queries on a page. that post is here

    Here is my query:
    Code:
    SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC
    It was recommended that looping through the results would be the best way to display them based on CatID.

    I've been googling around about looping in PHP for a few hours and I'm completely lost... I have found for loops, while loops, and so on and I'm not quite sure which one I would need to use in my situation.

    Let's say I just wanted to display the Names from the above query where The CatID equals 1, how would I go about doing that?

    Thanks in advance!

  2. #2
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I hope I'm understanding you correctly...so, here goes.

    PHP Code:
    $query "SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    WHERE CatID='1'
    ORDER BY CatID, Rank ASC"
    ;
    $result mysql_query($query);
    while(
    $com mysql_fetch_assoc($result))
    {
    echo 
    'Name: '.$com['Name'].'<br />';

    The While Loop will go through every row returned by that query and mysql_fetch_assoc will return each row as an associative array.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your response.

    What I'm wanting to do exactly is have just one query that returns all results from the community table, so I assume I would not want the WHERE in that query.

    Then in various areas on the page display all of the results based on the CatID.

    So say I have the query return everything from the table. In one area of my page I want to ouput all Names Where CatID=1 and in another area I want to output all Names Where CatID=2, and so on.

    I hope that makes sense...
    Thanks again!

  4. #4
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha, so then, here is what you could do. I'm not sure it's the best way, but it's how I do it.

    PHP Code:
    $cats = array();
    $query "SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC"
    ;
    $result mysql_query($query);
    while(
    $com mysql_fetch_assoc($result))
    {
    $cats[$com['CatID']][] = $com;

    echo 
    'CAT 1<br />';
    print_r($cats[1]);

    echo 
    'CAT 2<br />';
    print_r($cats[2]);

    echo 
    'CAT 3<br />';
    print_r($cats[3]); 
    Give that a try. I hope that's what you want.

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, I still don't think that's quite what I'm looking for. Sorry that I'm not so fluent in php, so I may not be wording everything correctly. But what I want is very simple. Let me try to explain again.

    This is my query. I already have this part coded on the page and it works returning all results from the community table. This query is named rsCommunity.
    Code:
    SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC
    This is where I need help:
    I need to loop through those results and return all names where CatID=1 and display it on my page without changing the query at all. I usually work in coldfusion and I know this would be very simple to do. Maybe it is more complex in php? From googling, maybe a do while loop is what I need? I'm not sure.

    thanks so much for responding, everyone on this forum is always so helpful!

  6. #6
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All I did was create an array that you can now use anywhere on the page.

    All of your results are being brought back in that query and stored under the array $cats. I haven't changed the query at all, so it will return all rows.

    Also, $cats just contains an array with the CatID as the key. Then, each rows is contained within that array.

    Maybe an example of how you want to display the results will help me understand what you want to do.

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I display just the Name column where CatID=1? I can likely figure out the rest from there.
    I guess I would display it from the array, not the query? I'm not sure how to do that.
    Sorry, I'm confused...

  8. #8
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC"
    ;
    $result mysql_query($query);
    while(
    $com mysql_fetch_assoc($result))
    {
    if(
    $com['CatID'] == '1') echo $com['Name'].'<br />';

    The problem with that, is that you cannot access it further down the page. You could put it all into another array:

    PHP Code:
    $coms = array();
    $query "SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC"
    ;
    $result mysql_query($query);
    while(
    $com mysql_fetch_assoc($result))
    {
    $coms[] = $com;

    // Your entire table is now located in $coms, accessible anywhere on the page.
    foreach($coms as $com)
    {
    if(
    $com['CatId'] == '1') echo $com['Name'].'<br />';


  9. #9
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really appreciate your help. But I still think that's not really what I'm looking for.
    The way I have it coded now, I have 6 separate queries. in each query I was specifying CatID=1 CatID=2, etc... Though it works, it just didn't seem like the most efficient way to do it. I was thinking I could query it once, then when I output it specify the catID and loop it to return all the results for that CatID. I was really returning more than just the name(but with an example of just the name I thought I could figure it out from there), it actually displays an image(image column is the image name) with the name in alt & title tags. then repeat that image for all results for that CatID.

    thank you so much for your help. I may just keep it how it is for now until I learn a little more PHP. It is working how I currently have it, I just know it can't be the best way to go about it.

    thanks again!

  10. #10
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you could post your HTML, that would help a lot. I'm sure it's pretty simple.

  11. #11
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is an example of how I display images with CatID=2.
    There are 5 other identical queries(for each CatID) on this page and 5 other identical ways that I output them. As I said before I was wanting to slim it down to one query if possible.

    Here's the Query:
    PHP Code:
    mysql_select_db($database_Mydb$Mydb);
    $query_rsCommunity2 "SELECT CommunityID, CatID, Rank, Name, Image FROM community WHERE CatID = 2";
    $rsCommunity2 mysql_query($query_rsCommunity2$Mydb) or die(mysql_error());
    $row_rsCommunity2 mysql_fetch_assoc($rsCommunity2);
    $totalRows_rsCommunity2 mysql_num_rows($rsCommunity2); 

    Here's where I display the images from the above query:
    PHP Code:
    <?php do { ?>
    <img src="community/<?php echo $row_rsCommunity2['Image']; ?>" width="482" title="<?php echo $row_rsCommunity2['Name']; ?>" alt="<?php echo $row_rsCommunity2['Name']; ?>" /><br />
    <?php } while ($row_rsCommunity2 mysql_fetch_assoc($rsCommunity2)); ?>
    Is there some way in the above loop to Choose which CatID you want to display? That would allow me to use only one query, which is what I'm hoping to do...

  12. #12
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My second solution would work for that. Try this:
    PHP Code:
    $cats = array();
    $query "SELECT CommunityID, CatID, Rank, Name, Image
    FROM community
    ORDER BY CatID, Rank ASC"
    ;
    $result mysql_query($query);
    while(
    $com mysql_fetch_assoc($result))
    {
    $cats[$com['CatID']][] = $com;

    Then, you could do something like this in your page:
    PHP Code:
    foreach($cats[1] as $cat)
    {
    echo 
    '<img src="community/'.$cat['Image'].'" width="482" title="'.$cat['Name'].'" alt="'.$cat['Name'].'" /><br />';

    Then, just change $cats[1] to $cats[2] in your next section to the desired CatID. I didn't test it, but it should work.

  13. #13
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that worked PERFECTLY. Thank you so much!
    One more quick question though.
    When I view my source code for this while looking at it in a browser it puts all the img tags on the same line. Which is just ugly to me. Is there a way to make each one be on it's own line in the source code?

  14. #14
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, this should do the trick:
    PHP Code:
    echo '<img src="community/'.$cat['Image'].'" width="482" title="'.$cat['Name'].'" alt="'.$cat['Name'].'" /><br />'."\n"

  15. #15
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This worked great. Thank you!!!

  16. #16
    SitePoint Addict telos's Avatar
    Join Date
    Mar 2005
    Location
    192.168.2.34
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You bet!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •