Sorting a mySQL recordset with PHP (or maybe still with mySQL?)

[Kinsbane]

Hi folks!

So here is my dilemma.

I have a table that has 4 fields which are used for categorizing and sorting parts. There is a 7th field which acts as an identifier so it knows which rows to pull when the identifier is queried. The previous 4 fields are organized as such: there are two groups of 2, with one being the "title", or "header", and the other being the one to determine sorting within that group.

The groups are as follows:
Field1, aka, "Line", with a field for sorting
Field3, aka, "Category", with a field for sorting

Think of it like this:
A "Line" of parts would be similar to saying "Fruit"
The "Category(ies)" within would be like, "Apples", "Oranges", "Strawberries", etc.

Here's an example:

So, I need to know how to do this in PHP (or if it can be done in mySQL, and I'm just a dumbass).

Also, all of these must remain in one table since they come from one excel file. I'm hoping I can get shown the right way!

Thanks!

[Piranha]

First, you should normalize the table. A simple spelling error would cause trouble. That it comes from an excel file is no reason not to do the job right.

Second, have you tried something like this:

$sql = "select rows from table where something order by field2 asc, field4 asc";
$result = mysql_query($sql);

[Kinsbane]

Piranha wrote:

First, you should normalize the table. A simple spelling error would cause trouble. That it comes from an excel file is no reason not to do the job right.

Second, have you tried something like this:

$sql = "select rows from table where something order by field2 asc, field4 asc";
$result = mysql_query($sql);

Hi Pranha,

Thanks for the reply! I will be normalizing the table later on once it's ready but right now I'm just trying to get correct output for the data.

The query you supplied works great, I'm kinda sitting here wondering why that didn't come to mind first, but there are additional steps needed which I'm not quite sure of.

What I need is to find all records whose Line is Fruit, and whos Category is Apples, and place them under one header for each.. like the image above displays. I am just unsure of how to do that with PHP, or if I even need PHP at all.

The problem I see with all of this is that the Category and Line fields are not always static, they could change depending on how wide the product line goes. Man I hope I'm making sense 🙁

[Kinsbane]

So I figured out a possible way of doing this.

First step was to query for all records with a specific ID :

$q = "SELECT product_line FROM ordering_info2 WHERE wp_identifier='$id' GROUP BY product_line ORDER BY s_line ASC";
			$r = mysql_query($q) or die(mysql_errno().": ".mysql_error());
			while ($row = mysql_fetch_array($r)) {
				$body .= "<tr>";
				$body .= "<td colspan=\"2\">$row[product_line]</td>";
				$body .= "</tr>";

Then, I would loop through those results and query them for all records pertaining to the current ID and the current product_line in the loop:

				$q2 = "SELECT product_category FROM ordering_info2 WHERE product_line='$row[product_line]' AND wp_identifier='$id' GROUP BY product_category ORDER BY s_category ASC";
				$r2 = mysql_query($q2) or die(mysql_errno().": ".mysql_error());
				echo mysql_num_rows($r2);
while ($row2 = mysql_fetch_array($r)) {
					print_r($row2);
					$body .= "<tr>";
					$body .= "<td colspan=\"2\">$row2[product_category]</td>";
					$body .= "</tr>";

Now I want to actually output the specific technical data contained within the product_category and product_line we are currently in in the loop:

				$q3 = "SELECT actual_id, wp_description FROM ordering_info2 WHERE product_line='$row[product_line]' AND product_category='$row2[product_category]' AND wp_identifier='$id' ORDER BY detail_sort ASC";
				$r3 = mysql_query($q3) or die(mysql_errno().": ".mysql_error());
				echo mysql_num_rows($r3);
				while ($row3 = mysql_fetch_array($r3)) {
					print_r($row3);
					$body .= "<tr>";
					$body .= "<td class=\"code\"><a href=\"http://www.mrv.com/products/how_to_buy.php?prod=$row3[actual_id]\">$row3[actual_id]</a></td>";
					$body .= "<td>".nl2br($row3[wp_description])."</td>";
					$body .= "</tr>";
				}
			}	
		}
		echo $body;

However, the script is only outputting the stuff in the while loop for the product_line query - it prints out the header and then nothing else. And I receive no errors when I use die(mysql_error()).

I sure hope I'm on the right track. 🙁

Here's the full code:

$q = "SELECT product_line FROM ordering_info2 WHERE wp_identifier='$id' GROUP BY product_line ORDER BY s_line ASC";
			$r = mysql_query($q) or die(mysql_errno().": ".mysql_error());
			while ($row = mysql_fetch_array($r)) {
				$body .= "<tr>";
				$body .= "<td colspan=\"2\">$row[product_line]</td>";
				$body .= "</tr>";
				$q2 = "SELECT product_category FROM ordering_info2 WHERE product_line='$row[product_line]' AND wp_identifier='$id' GROUP BY product_category ORDER BY s_category ASC";
				$r2 = mysql_query($q2) or die(mysql_errno().": ".mysql_error());
				echo mysql_num_rows($r2);
				while ($row2 = mysql_fetch_array($r)) {
					print_r($row2);
					$body .= "<tr>";
					$body .= "<td colspan=\"2\">$row2[product_category]</td>";
					$body .= "</tr>";
					$q3 = "SELECT actual_id, wp_description FROM ordering_info2 WHERE product_line='$row[product_line]' AND product_category='$row2[product_category]' AND wp_identifier='$id' ORDER BY detail_sort ASC";
					$r3 = mysql_query($q3) or die(mysql_errno().": ".mysql_error());
					echo mysql_num_rows($r3);
					while ($row3 = mysql_fetch_array($r3)) {
						print_r($row3);
						$body .= "<tr>";
						$body .= "<td class=\"code\"><a href=\"http://www.mrv.com/products/how_to_buy.php?prod=$row3[actual_id]\">$row3[actual_id]</a></td>";
						$body .= "<td>".nl2br($row3[wp_description])."</td>";
						$body .= "</tr>";
					}
				}


		}
		echo $body;

[Piranha]

Kinsbane wrote:

Thanks for the reply! I will be normalizing the table later on once it's ready but right now I'm just trying to get correct output for the data.

Why do you want to do the job twice? Just do it once, and do it correct that time. Normally I won't help people out that don't take my advice, but I'm actually going to give you the right idea.

I see where you are going with your idea. Unfortunatley it is a terrible idea, you get way to many requests to the database. Let's say that you have 10 categories and 10 subcategories in each. With your idea you would then have to do 1 query to get the categories, 1 query for each category to get the subcategories and 1 query per subcategory to get the actual information. That is 111 queries!!! And every query take a little time.

Instead use only one query. Something like this (only the idea, you have to do the work):

$sql = "select categoryid, name from table order by categoryid";
$result = mysql_query($sql);
$catid = 0; // Sets a variable to use to see if it is a new category or not
while ($row = mysql_fetch_row($result)) { // Write this in a way that loops the results
   if ($row['categoryid'] == $catid) { // If you already have the categoryid
      echo $row['name'] . "<br/>";
   } else { // If you don't already have the categoryid you want to output that as well
      $catid = $row['categoryid']; // Set $catid to compare in the next loop
      echo "<br/><b>" . $row['categoryid'] . "</b><br/>" . $row['name'] . "<br/>";
   }
}