Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    27
    Plugin Contributions
    1

    Default Formatting SQL query results

    I'm working on developing a add on that will allow store owners to enter retail locations of where to buy their products through the admin.

    I have all of the admin files ready and tested. I also have the front end files working but would like to tweak the formatting of the query results....

    I would like to make the query once, then if the state equals something (say CA) then all stores in CA would be printed with a fieldset around the results. The printing should then move on the next state and print those results. I can get it working with a WHERE statement in the query, but I don't want to do 50 queries, just one.

    Here's what I have:
    Code:
    global $db;
    
     
     
    
    $row = 0;
    
    $stores = $db->Execute("SELECT stores_name, stores_address_1, stores_address_2, stores_city, stores_state, stores_zip, stores_phone, stores_url 
                            from " . TABLE_STORES . "
                            WHERE stores_state = 'ca'
                            order by stores_name" );
    
    if ($stores->RecordCount() > 0)                    
    {
    echo '<fieldset><legend>California</legend><table cellpadding="2" cellspacing="0" width="100%"><tr>';
    while (!$stores ->EOF) {
    
    $row++;
    
    echo '<td valign="top" width="33%">';
    
    echo $stores->fields['stores_name'] . '<br />';
    if ($stores->fields['stores_address_1']) {
    echo $stores->fields['stores_address_1'] . '<br />';
    }
    if ($stores->fields['stores_address_2']) {
    echo $stores->fields['stores_address_2'] . '<br />';
    }
    if ($stores->fields['stores_city']) {
    echo $stores->fields['stores_city'] . ', ';
    }
    if ($stores->fields['stores_state']) {
    echo $stores->fields['stores_state'] . ' ';
    }
    if ($stores->fields['stores_zip']) {
    echo $stores->fields['stores_zip'] . '<br />';
    }
    if ($stores->fields['stores_phone']) {
    echo $stores->fields['stores_phone'] . '<br /> ';
    }
    
    if ($stores->fields['stores_url']) {
    
    echo '<a href="http://' . $stores->fields['stores_url'] . '"target=_blank">'. $stores->fields['stores_url'] . '</a>';
    
    
    
    
    
    echo "<br /><br />\n";
    
    echo '</td>';
    
    }
    
    if ((($row / 3) == floor($row / 3))) {
    
    ?>
    
    
    
    </tr><tr>
    
    
    
    <?php
    
    }
    
    $stores->MoveNext();
    
    }
    
     
    
    
    
    echo '</tr></table></fieldset>';
    }
    I think I need to make the stores_state a variable based on the query but I can't get it to work. Any guidance would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    65,315
    Blog Entries
    7
    Plugin Contributions
    228

    Default Re: Formatting SQL query results

    1. Change your query to remove the WHERE clause and change your ORDER BY clause to be: stores_state ASC, stores_name

    2. Move your "echo" for the fieldset to inside the while loop, where indicated in the next item:

    3. Just before the $row++ add some new lines:
    Code:
    $display_fieldset_wrapper = false;
    if ($last_state != $stores->fields['stores_state']) {
      $last_state = $stores->fields['stores_state'];
      $display_fieldset_wrapper = true;
    }
    if ($display_fieldset_wrapper) {
      echo .......do your fieldset echo here
    }
    4. before the MoveNext() command, add another echo:
    Code:
    if ($display_fieldset_wrapper) {
      echo .......do your /fieldset echo here
    }
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 

Similar Threads

  1. v154 Help with a SQL Query for Query Builder
    By lindasdd in forum Managing Customers and Orders
    Replies: 2
    Last Post: 24 Mar 2016, 01:18 PM
  2. Replies: 4
    Last Post: 17 Oct 2013, 10:55 PM
  3. v151 SQL query setup. How do I TEST a query 'off-line'?
    By lewisasding in forum General Questions
    Replies: 3
    Last Post: 8 Mar 2013, 12:24 AM
  4. Replies: 2
    Last Post: 15 Aug 2011, 04:58 AM
  5. Formatting of links query
    By esugrue in forum General Questions
    Replies: 0
    Last Post: 6 Jan 2009, 03:18 PM

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
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR