Hi all! I think this post will be kinda lengthy, but I need it to be to explain what's going on.
Every quarter I receive an Excel file with lots of rows regarding ordering information for some products. This excel file is formatted as such:
actual_id, product_family, s_line, product_line, s_category, product_category, s_series, product_series, detail_sort, wp_identifier, wp_description
So, the way it works is this:
The product_line column is the most general of fields to organize records by. Following that comes product_category, and then product_series. These are sorted by the "s_line", etc columns in ASC order. The detail_sort field is used to sort individual records themselves inside of product_line, product_category, and product_series.
Onto the issue I am having.
The code I wrote to grab the data from the database is, I believe, logical and perfectly sound.. however, when the code executes, I am not receiving all of the data.
Here's the code I am using:
<?
$body = '';
//first we query for all the Distinct entries in the product_line column, and setup the array to loop through
$plq = "SELECT DISTINCT product_line FROM ordering_info$cat WHERE wp_identifier LIKE '%$id%'";
$plr = mysql_query($plq) or die(mysql_errno().": ".mysql_error());
$pla = mysql_fetch_array($plr, MYSQL_NUM);
$planum = count($pla);
for($i = 0; $i < $planum; $i++) { //loop through the product_line array
$body .= "<tr>\n";
$body .= "<td colspan=\"2\" class=\"pline\">$pla[$i]</td>\n";
$body .= "</tr>\n";
//next, within the current index of $pla array, we need to get all the Distinct entries for product_category
$pcq = "SELECT DISTINCT product_category FROM ordering_info$cat WHERE wp_identifier LIKE '%$id%' AND product_line='$pla[$i]' GROUP BY product_category ORDER BY s_category ASC";
$pcr = mysql_query($pcq);
$pca = mysql_fetch_array($pcr, MYSQL_NUM);
$pcanum = count($pca);
for ($x = 0; $x <= $pcanum; $x++) { //loop through the product_category array
$body .= "<tr>\n";
$body .= "<td colspan=\"2\" class=\"pcat\">$pca[$x]</td>\n";
$body .= "</tr>\n";
//here's where we get the actual rows based on both the current product_line array index and the current product_category array index
$q = "SELECT actual_id, wp_description FROM ordering_info$cat WHERE wp_identifier LIKE '%$id%' AND product_line='$pla[$i]' AND product_category='$pca[$x]' ORDER BY detail_sort ASC";
$r = mysql_query($q);
while ($row = mysql_fetch_array($r)) { //loop through the records for the current product_line index and the current product_category index
$body .= "<tr>\n";
$body .= "<td class=\"code\"><a href=\"http://www.mrv.com/products/how_to_buy.php?prod=$row[actual_id]\">$row[actual_id]</a></td>\n";
$body .= "<td>$row[wp_description]</td>\n";
$body .= "</tr>\n";
}
}
}
echo $body;
?>
However, the code is not outputting properly for some reason. In phpMyAdmin, if I run the query for product_line to get all of the distinct ones, it returns all the distinct records just fine. But in PHP, when I output it to the browser, it's only returning one product_line, and when it does that, it actually duplicates the records that fall under that product_line, as shown in this screenshot:
http://www.kinsbane.net/temp/wtfmate.jpg
Now the particular table I'm working with, based on that screenshot should have the following DISTINCT product_lines:
Chassis Selection, Redundant Power Supplies, Accessories.
As you can see in the screenshot above, PHP is not outputting that at all, and in fact, it's duplicating the records it finds for anything in the "Chassis Selection" part.
I've been banging my head against the sharpest corner of my desk for the better part of this week trying to figure it out, to no avail.
Is there really something wrong with my code? Cause if so, I really cannot see it.
