Please help!! PHP is not producing expected results with the code I am using

[Kinsbane]

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.

[BlueSkyIS]

i suggest checking for error here:

 

$pcr = mysql_query($pcq);

 

using

 

$pcr = mysql_query($pcq) or die(mysql_error()." from $pcq");

 

[elite_prodigy]

What are you trying to do with this line? Concatenate?

 

<?php
		$plq = "SELECT DISTINCT product_line FROM ordering_info$cat WHERE wp_identifier LIKE '%$id%'";
?>

 

Maybe write it like this:

 

<?php
		$plq = "SELECT DISTINCT product_line FROM ordering_info".$cat." WHERE wp_identifier LIKE '%$id%'";
?>

 

So maybe:

 

<?php
		$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;

?>

[GingerRobot]

I would recommend echoing out the actual query that is being performed, e.g:

 

$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";
echo '<br />Query: '.$q.'<br />';

 

You can then check that the right values are being passed into your query (which seems unlikely if, as you say, everything works ok when you put the values in manually and execute through phpMyAdmin)

 

[Kinsbane]

I would recommend echoing out the actual query that is being performed, e.g:

 

$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";
echo '<br />Query: '.$q.'<br />';

 

You can then check that the right values are being passed into your query (which seems unlikely if, as you say, everything works ok when you put the values in manually and execute through phpMyAdmin)

 

 

Hi Ginger and everyone, thanks for the replies!

 

Adding a die() command to the $pcr result var did not result in any errors. I am still getting output, however, it's incorrect.

 

I did what GingerRobot suggested and echo'd the $query , and here's what's happening. I also decided to echo the $planum and $pcanum values, and they both say only "1", but I am not sure why because there's more than one DISTINCT product_line in the dataset, the value of $planum should be 3 - is it because of how I'm returning the array?

 

Like I'm using mysql_fetch_array() - but that's only an array of the first record in the result set, correct? Isn't there a way to get all the records of a result set into an array? Like if I use mysql_fetch_????

 

 

[GingerRobot]

Like I'm using mysql_fetch_array() - but that's only an array of the first record in the result set, correct? Isn't there a way to get all the records of a result set into an array? Like if I use mysql_fetch_????

 

Thats right, yes. You'll need to loop through the results and put them in an array first. For example, you'll need to replace this:

 

<?php
$pla = mysql_fetch_array($plr, MYSQL_NUM);

$planum = count($pla);
?>

 

With:

<?php
$product_lines = array();
while($row = mysql_fetch_row($plr){
$product_lines[] = $row[0];
}
$planum = count($product_lines);
//you'll then use $product_lines instead of $pla in the rest of your script.
?>

 

Looks like you need to do a similar thing on the second query too.

 

I do wonder if you might be able to make your life easier with some more advanced queries - you might find that if you head over to the mysql forum and post up your table structure and what you're trying to achieve, someone can help you perform that more easily(i.e. less queries)

[Kinsbane]

It's not working, none of it is working.

 

I tried what GingerRobot suggested, I tried just swapping the for() loops into while() loops and I am not getting anything desireable.

 

I don't know what's going on but it seems as though I've done everything proper and the data's all there but I just can't get it to work. :'(

[Kinsbane]

Okay I brought it down to basics to try and figure out what's going on.

 

I tried grabbing just a product_line, by using DISTINCT, and only 1 of them worked - even though the wp_identifier matched up, and so did the query.

 

So now I have no idea what's going on and I can't tell if its a PHP issue, an issue with the data in the table, or what.

 

This is just not working the way I would've liked. :'(

[Kinsbane]

Update!!! I almost have it working!

 

This is the code I ended up with, based off what GingerRobot suggested:

<?
		$body = '';
		/*$q = "SELECT * FROM ordering_info$cat WHERE wp_identifier LIKE '%$id%' ORDER BY detail_sort ASC";
		$r = mysql_query($q) or die(mysql_errno().": ".mysql_error());
		while ($row = mysql_fetch_array($r)) {
			echo "WP: ".$row['actual_id']." — DESC: ".$row['wp_description']." — PL: ".$row['product_line']." — PC: ".$row['product_category']."<br />";
		}

		*/
		//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%' GROUP BY product_line ORDER BY s_line ASC";
		$plr = mysql_query($plq) or die(mysql_errno().": ".mysql_error());

		$plines = array();
		$pcats = array();
		while ($pla = mysql_fetch_array($plr)) {
			//print_r($pla);
			$plines[] = $pla[0];
			//print_r($plines);
		}



		$plnum = count($plines);
		//echo $plnum;


		for ($L = 0; $L < $plnum; $L++) {
		//while ($plrow = mysql_fetch_array($plr)) { //loop through the product_line array

			$body .= "<tr>\n";
			$body .= "<td colspan=\"2\" class=\"pline\">$plines[$L]</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='$plines[$L]' ORDER BY s_category ASC";
			$pcr = mysql_query($pcq) or die(mysql_errno().": ".mysql_error());

			while($pca = mysql_fetch_array($pcr)) {
				//print_r($pca);
				$pcats[] = $pca[0];
				//print_r($pcats);
			}
			$pcnum = count($pcats);

			//echo $pcnum;

			for ($C = 0; $C < $pcnum; $C++) {


			//while ($pcrow = mysql_fetch_array($pcr)) { //loop through the product_category array

				$body .= "<tr>\n";
				$body .= "<td colspan=\"2\" class=\"pcat\">$pcats[$C]</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='$plines[$L]' AND product_category='$pcats[$C]' ORDER BY detail_sort ASC";
				echo "<br>Query: ".$q."<br><br>";
				$r = mysql_query($q) or die(mysql_errno().": ".mysql_error());
				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;

		?>

 

The only remaining issue is, is that the code is now outputting the number of product_categories based on the current count of the product_line loop - which isn't good.

Here's an example:

 

I had thought of maybe sticking an if/else into the product_category loop to check and see if there are any valid things to return, and if so, return them, otherwise don't return anything...

[Kinsbane]

bump

[Kinsbane]

I've lost all hope for this project unless someone can offer further assistance.