SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with logic flow

    I am querying a database for a list of part numbers and their respective categories. I'm attempting to run a while over the records and separate each group of part numbers into a div for their respective categories so I can then handle them in css.

    For example, the results may consist of

    Accessories-Ash_Trays
    AT1234
    AT2345
    Accessories-Coat_Hooks
    CH3456
    CH4567
    CH5678
    Accessories-Light_Switch
    LS6789

    So the output HTML I want:
    <div id="Accessories-Ash_Trays">
    AT1234
    AT2345
    </div>
    <div id="Accessories-Coat_Hooks">
    CH3456
    CH4567
    CH5678
    </div>
    <div id="Accessories-Light_Switch">
    LS6789
    </div>

    Here is the code I currently have:

    require("dbconnect.inc");
    // Clear/set the ShowPartList variable
    $ShowPartList = "";
    // Query the database for current list of products
    $ProductList = "SELECT tbl_catnav.category_set, tbl_products.part_number ";
    $ProductList .= "FROM tbl_products ";
    $ProductList .= "LEFT JOIN tbl_catnav ON tbl_products.cat_id = tbl_catnav.lastcatid ";
    $ProductList .= "ORDER BY tbl_catnav.category_set, tbl_products.part_number;";
    $GetPartList = mysql_query($ProductList);
    $NumParts = mysql_num_rows($GetPartList);
    $i=0;
    $LastCat = "";
    while($i < $NumParts)
    {
    $CurrentCat = str_replace(" ", "_",mysql_result($GetPartList,$i,"tbl_catnav.category_set"));
    // Test if active category has changed, if so post new category name
    if ($CurrentCat != $LastCat) {
    $ShowPartList .= "<div id=\"CatTitleDiv\">";
    $ShowPartList .= "<a href=\"javascript:unhide('" . $CurrentCat . "');\">" . $CurrentCat . "/a><br />";
    $ShowPartList .= "<div id=\"" . $CurrentCat . "\" class=\"hidden\">";
    }
    $ShowPartList .= "<a href=index.php?body=prodshow&prodnum=" . mysql_result($GetPartList,$i,"tbl_products.part_number") . ">" . mysql_result($GetPartList,$i,"tbl_products.part_number") . "</a><br />";


    if ($CurrentCat != $LastCat) {
    // Close both divs opened up for the new Category Section
    $ShowPartList .= "</div></div>";
    }
    $LastCat = $CurrentCat;
    $i++;
    }
    echo $ShowPartList;


    I know my problem lies in the if($CurrentCat != $LastCat) near the end of the code, I am just having trouble thinking logically through how to test for it. Basically I know I need to check if it is a new category, if it is then put in the opening DIV statements. Then populate the Product # (using a link so they can go straight to the product info page) then it needs to determine if the closing divs should be there yet, or if there is another part under that same category heading. I've thought of testing for that by using something like $i-- or $i++, but the first record (or last) throws an error then because there is no -1 record value and the last record can't do a ++.

    I'm thinking as I write this maybe catching the error from the first pass through, since I know I HAVE to have the opening div commands there. But it seems to me there should be a more elegant solution.

    Hopefully I've explained this clearly enough.

    Greg

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi GHicks,

    My suggestion is a little different direction and my code will not work 100% as I don't completely understand your desired result set, but this idea takes advantage of binding parameters and reusing the same prepared statement which is efficient. You use the database to get your result sets by individual queries based on the category array and then builds and array that groups these categories in their own arrays. You can then decide how you want to get at the $key and $value pieces of data.

    This uses a DbFactory class that I wrote. Copy it to it's own php file and then name it DbFactory.php.
    PHP Code:
    <?php 
    class DbFactory{
      private static 
    $factory;
      public static function 
    getFactory(){
          if (!
    self::$factory){
            
    self::$factory = new DbFactory();
            return 
    self::$factory;
          } else {
            throw new 
    exception('DbFactory could not return database instance.');
          }
      }
      private 
    $db;
      public function 
    getConnection(){
          if (!
    $db)
               
    $db = new PDO("mysql:host=localhost;dbname=your_db""your_user""your_password");
          return 
    $db;
      }
    }
    Then in you Main PHP page do something like:
    PHP Code:
    <?php 
    require_once('DbFactory.php');
    $o_Db DbFactory::getFactory()->getConnection();  //Get PDO instance

    # Prime the categories array
    $categories = array(
        
    'Accessories-Ash_Trays'
      
    'Accessories-Coat_Hooks'
      
    'Accessories-Light_Switch' 
      
    );
    $html '';
    # call select function passing the db instance and the category array
    $categories selectProductCategories($o_Db$categories );
    # loop through the multi-dimensional array and extract the formated html in a single variable to 
    # output to the browser when complete.
    foreach($categories as $category){
      foreach(
    $category as $key => $value){
        
    $html .= "<div id='CatTitleDiv'>$key</div><div id='description'>$value</div>";
      }
    }
    # echo formatted html;
    echo $html;

    function 
    selectProductCategories($o_Db, array $categories){
      
    $sql "
      SELECT
       tbl_catnav.category_set
       , tbl_products.part_number
      FROM
       tbl_products 
      LEFT JOIN tbl_catnav
        ON tbl_products.cat_id = tbl_catnav.lastcatid
      WHERE catnav.category_set = ':category_name'
      ORDER BY
        tbl_catnav.category_set
        , tbl_products.part_number;"
    ;

       
    $stmt $o_Db->prepare($sql);
       
    $cats = array();
       foreach(
    $cateories as $category){
         
    $stmt->bindParam(':category_name'$category):
         
    $stmt->execute();
         
    $rows $stmt->fetchAll(PDO_FETCH_ASSOC);
         
    $cats[$category] = $rows;    
       }
       return 
    $cats;
    }
    ?>
    I don't know if this works for you but it is an idea anyway.

    Regards,
    Steve
    ictus==""

  3. #3
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    GHicks, your problem is trying to mix logic with output, it's called spaghetti code.

    A better way to write this is separate the two parts: the part that pulls data out, and the part that generates the HTML.

    Try rewriting your code this way:
    PHP Code:
    $rs mysql_query("....");

    while (
    $row mysql_fetch_assoc($rs)) # this section can be broken down further, but this is the minimum. this prepares an array that is easy to use as well as modify in the future.
    {
        
    $accessory$row['category_set'] [] = $row;
    }


    foreach (
    $accessory as $cat=>$row# now this section is where the view logic goes, which simply deals with HTML and because it was prepared nicely ahead of time, the logic is incredibly straight forward.
    {
        echo 
    "<div id='$cat'>";
        foreach (
    $row as $k=>$v)
        {
            echo 
    $v['part_number'].'<br/>';
        }
        echo 
    "</div>";

    Hopefully you can see what I'm trying to illustrate and see how much cleaner everything is by separating the two.

  4. #4
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ServerStorm - not sure that will perform what I am trying to do, but I'm going to save your code and look it over when I finish this task to see if I can get a better grasp of it all. Thank you.

    wonshikee - OK, I'm still a little green when it comes to working with PHP (especially working with result sets, that's always been an issue for me). Forgive my ignorance but here are the questions your solutions raises in my head.

    1) Would I use the same query I already have constructed? It returns exactly the data I want with the only duplication being the same Category for a select group of products returned
    2) I'm attempting to use a small bit of javascript to show/hide the parts. Basically I want the user to just end up with a list of categories, clicking on them opens that category to show the respective products (without a page reload or new call to database).
    3) Your code just displays a row of first letters for each returned value. I don't totally understand all it is doing (especially the foreach commands, what does the => do?

    I think with some of those answers I can muddle through more of it.

    Thanks! Definitely gave me some things to reconsider my methods.

    Greg

  5. #5
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    1) Yes same query.

    2) Add on the javascript later, simply focus on getting the HTML looking right first.

    3) change

    echo $v['part_number'].'<br/>';

    to

    print_r($v); die;

    If you aren't sure what the resulting output represents, paste it here and I will show you.

  6. #6
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, when I changed that then I got the full returned line, not just the first character. With that in mind I then change that line to:

    echo $v.'<br/>;

    That then gave me the list (mostly) it only showed the LAST product for each category and no category listed more than a single product.

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Greg,

    As wonshikee has pointed out, your code mixed the model (database logice) with the view (the HTML template). It is best when thinking about this type of page to organize your roles for page functionality. To me it looks like you could do something similar to:


    1. getCategories(); Create a PHP function that queries the database for you categories and then returns an unformatted result set.
    2. (Optional) getData(); Create a function that calls your database query function and parses the result set by preparing an array that holds your data so it is 'easy to use' like wonshikee mentions. In the end, this function would return an associative array with the keys and data for you to use in your HTML template
    3. Finally create an HTML page that calls the getData(); function that returns an array then put the keys and data into your html template.


    This is a very basic separation but it does help you have your database quering, your formatting of the data, and the display of your data all in separate places. This will make your code cleaner and ultimately easier to work with.

    Regards,
    Steve
    ictus==""


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
  •