Can this be done with a better query? (building HTML Table)

[Kinsbane]

Posted this in the PHP forum and someone suggested I check here to see if

maybe this can be solved with a better MySQL query.

 

Thanks in advance for any assistance!!

 

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.

[fenway]

Yikes... sounds like you should be joining all three in a single sql statement...

[Kinsbane]

Yikes... sounds like you should be joining all three in a single sql statement...

 

Hi fenway... I've never used mySQL joins in a query before... the products this info is tied to is in a separate table, so there's two tables altogether.

 

Can you offer further assistance? Thanks!

[fenway]

Post your table structure, and a sample of the output you want to see.

[Kinsbane]

Post your table structure, and a sample of the output you want to see.

 

Thank you fenway! Here's the table structure:

 

CREATE TABLE `ordering_info_test` (
  `actual_id` varchar(255) NOT NULL default '',
  `s_family` varchar(255) NOT NULL default '',
  `product_family` varchar(255) NOT NULL default '',
  `s_line` varchar(10) NOT NULL default '',
  `product_line` varchar(255) NOT NULL default '',
  `s_category` varchar(10) NOT NULL default '',
  `product_category` varchar(255) NOT NULL default '',
  `s_series` varchar(10) NOT NULL default '',
  `product_series` varchar(255) NOT NULL default '',
  `detail_sort` varchar(10) NOT NULL default '',
  `wp_identifier` varchar(255) NOT NULL default '',
  `wp_description` longtext NOT NULL,
  `actualid_link` varchar(255) NOT NULL default ''
) TYPE=MyISAM;

 

Brief description of how I need it to output:

We aren't worried about the product family part of things yet - that's for the backend stuff for internal folks to use. We are, however, interested very much in the product_line and the product_category field. Everything is derived from the "actualid_link" field.. this matches up with the current product a user is looking at, so when they look at ordering info, they get ALL the parts they need for this particular product. So, what we need is to show all records within a product_category "A" and product_category "B" within a product_line "A", as well as all records within a product_category "C", "D", and "E" within a product_line "B". These need to be sortable in ASC by the s_line, s_category, and detail_sort fields. I need to be able to show the headings (product_line) and the subheading (product_category) within the current heading (product_line), similar to this:

 

Product Line A (heading)

---- Product Category A (subheading)

-------- Part number A (actual_id) | Part description (wp_description)

-------- Part number B (actual_id) | Part description (wp_description)

---- Product Category B (subheading)

-------- Part number C (actual_id) | Part description (wp_description)

-------- Part number D (actual_id) | Part description (wp_description)

 

Product Line B (heading)

---- Product Category C (subheading)

-------- Part number E (actual_id) | Part description (wp_description)

-------- Part number F (actual_id) | Part description (wp_description)

---- Product Category D (subheading)

-------- Part number G (actual_id) | Part description (wp_description)

-------- Part number H (actual_id) | Part description (wp_description)

---- Product Category E (subheading)

-------- Part number I (actual_id) | Part description (wp_description)

-------- Part number J (actual_id) | Part description (wp_description)

 

 

Hopefully that explains it well enough! I always know what it is I wanna say in my head, but when I try to explain it sometimes falls short

 

Thanks again fenway for any assistance you offer!

[Kinsbane]

bump

 

I'm really at a loss as to how to get this going and I don't have the first clue on what kind of query I need

 

I had thought loops would take care of it but I guess not please help

[fenway]

So you have all of the lines, categories, parts, etc. in a single table?