Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts

    How to Loop through Records to Display Data?

    Okay, I feel embarrassed asking this question, because it seems like it should NOT be so confusing, but here goes...


    I have joined together several tables in my database to get this resultant Data Set...
    Code:
    Dimension		SubSection
    ----------		-----------
    Business Structure	Sole Proprietorship
    Business Structure	Partnership
    Business Structure	LLC
    Business Structure	S-Corp
    Business Structure	C-Corp
    Store Type		Brick & Mortar
    Store Type		Online
    Store Type		Hybrid
    Offering		Products
    Offering		Services
    Offering		Full Service
    Accounting		Bookkeeping
    Accounting		Payroll
    Accounting		Taxes
    Seasonal		Tax Season

    On my website's "Section Landing Page", I am looking to take this data, and create a series of "Boxes" stacked on top of each other in the right margin like this...

    Code:
    Business Structure:
        Sole Proprietorship
        Partnership
        LLC
        S-Corp
        C-Corp
    Code:
    Store Type:
        Brick & Mortar
        Online
        Hybrid
    Code:
    Offering:
        Products
        Services
        Full Service
    Code:
    Seasonal:
        Tax Season

    What is the best way to use PHP to take the Data Set mentioned and get the desired Output above??


    (BTW, maybe joining all of those table together wasn't the best approach?! I just figured it would be better to have ONE QUERY, than a series of queries...)

    Hope all of this makes sense?!

    Sincerely,


    Debbie

  • #2
    New Coder
    Join Date
    May 2005
    Location
    New Zealand
    Posts
    76
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Compare dimension each loop and if different create new box

    Hi,

    I have had to do similar things in a recent project;

    The trick is to compare the dimension each iteration of the loop and if it is different than in the last loop, then create a new box and output the dimension as the title. If the dimension is the same as the previous loop then only the subSection is output.

    PHP Code:
    $result mysql_query("** your query here **");

    $previousDimension "";
    $first true;

    while (
    $row mysql_fetch_array($result))
    {
       if (
    $row['dimension'] != $previousDimension)
       {
          if (!
    $first)
             echo 
    "</div>";

          echo 
    "<div class='box'>";
          echo 
    "<strong>" $row['dimension'] . "</strong><br />";

          
    $previousDimension $row['dimension'];
          
    $first false;
       }

       echo 
    $row['subSection'] . "<br />";
    }

    echo 
    "</div>"
    Cheers,
    DouG.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    DougMck,

    Without ever seeing your response, here is what I did...

    PHP Code:
        $lastDimension NULL;

        while (
    mysqli_stmt_fetch($stmt3)){
            
    // Check for New Dimension.
            
    if ($dimensionName !== $lastDimension){
                
    // New Dimension
                
    if ($lastDimension){
                    
    // End the Last Dimension.
                    
    echo "</ul>
                            </div>"
    ;
                }

                
    // Start a New Dimension.
                
    echo "<div class='boxDimension'>
                        <h2>$dimensionName</h2>
                        <ul>"
    ;
            }
    //End of CHECK FOR NEW DIMENSION

            // Display Sub-Section.
            
    echo "<li>
                    <a href='/$sectionSlug/$subsectionSlug/'>$subsectionName</a>
                </li>"
    ;

            
    $lastDimension $dimensionName;
                            
        }
    //End of BUILD DIMENSIONS & SUBSECTIONS

        
    if ($lastDimension){
            
    // End Final Dimension.
            
    echo "    </ul>
                        </div>"
    ;
        } 

    Thoughts?


    Debbie

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I would prefer to keep it all within the loop myself instead of dealing with some data after the loop:
    PHP Code:
    $s 'Business Structure    Sole Proprietorship
    Business Structure    Partnership
    Business Structure    LLC
    Business Structure    S-Corp
    Business Structure    C-Corp
    Store Type        Brick & Mortar
    Store Type        Online
    Store Type        Hybrid
    Offering        Products
    Offering        Services
    Offering        Full Service
    Accounting        Bookkeeping
    Accounting        Payroll
    Accounting        Taxes
    Seasonal        Tax Season'
    ;
    $a explode(PHP_EOL$s);
    foreach (
    $a AS &$row)
    {
        
    $row explode("\t"$row);
        
    $row array_merge(array_filter($row));
    }
    reset($a);


    $lastDimension null;
    while (
    $row current($a))
    {
        
    next($a);
        list(
    $Dimension$SubSection) = $row;

        if (
    strcasecmp($lastDimension$Dimension) != 0)
        {
            if (
    $lastDimension != null)
            {
                
    printf('</ul></div>' PHP_EOL);
            }
            
    printf('<div class="boxDimension"><h2>%s</h2><ul>' PHP_EOL$Dimension);
            
    $lastDimension $Dimension;
        }
        
    printf('<li><a href="%s/%s/">%s</a></li>' PHP_EOL$sectionSlug$subsectionSlug$SubSection);

    I just took the table you had above there and created the multidimensional array for it. The iteration is almost the same except I used current instead, and then I extracted the variables to kinda simulate the bind you've used.
    Concept is pretty much the same, but its all in the loop.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Junsee
    Guest
    its sometimes snapper to have 4 queries
    I would highly recommend this over a join, but haven't seen enough to know what kind of data you're dealing with

    Personally I would have gone with 4 different queries each ending with the Dimension variable, as in:
    WHERE `Dimension` = 'Store Type'

    and sometimes being too smart, and trying to do it all in one big statement can end up being painful. I know, my college professor always said, a smart system admin is a lazy one! and he was bone idle, but really smart

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by Junsee View Post
    its sometimes snapper to have 4 queries
    I would highly recommend this over a join, but haven't seen enough to know what kind of data you're dealing with

    Personally I would have gone with 4 different queries each ending with the Dimension variable, as in:
    WHERE `Dimension` = 'Store Type'

    and sometimes being too smart, and trying to do it all in one big statement can end up being painful. I know, my college professor always said, a smart system admin is a lazy one! and he was bone idle, but really smart
    NEVER opt for 4 queries over a join if its not warranted. The offloaded work on the external resource for the SQL will be much slower than the extra memory it will take to have effectively duplicated the single field over x matching rows. In other words, you'll rarely see a situation where a join will cause degradation to performance over the use of multiple queries, and the multiple queries will definitely take more time and effort to execute over a join. That doesn't even mention the limitations that can be imposed on mysql such as max queries per hour.
    This same logic would be used in nested querying. First you would query the distinct, and then foreach of the distinct you would query the SubSection. Total queries for that would be 6 versus 1.

    Although I'm not sure where the prepared statement would fit in on this one. Pedant may be able to comment on that as a prepared statement should only need to send the dynamic data on the execute, and not the full query. So that could work; its not 4 separate queries so much as one prepared query with four distinct pieces of data. It would still require a separate query to get those distinct pieces of data mind you.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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