Trouble with logic flow

GHicks · April 3, 2012, 12:31am

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 .= "&lt;/div&gt;&lt;/div&gt;";
    }
    $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

ServerStorm · April 3, 2012, 1:11am

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 
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 
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

wonshikee · April 3, 2012, 3:18am

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:


$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.

GHicks · April 3, 2012, 4:35am

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.

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
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).
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

wonshikee · April 3, 2012, 4:58am

Yes same query.
Add on the javascript later, simply focus on getting the HTML looking right first.
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.

GHicks · April 3, 2012, 5:05am

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.

ServerStorm · April 3, 2012, 2:00pm

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:

getCategories(); Create a PHP function that queries the database for you categories and then returns an unformatted result set.
(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
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