Loop that Compares Two Query Results

[bronzino]

Query 1:

$query_NavOpt = "SELECT * FROM Pages_NavOption WHERE Pages_NavOption.Style = '6' ORDER BY Pages_NavOption.`Display Order`";
$NavOpt = mysql_query($query_NavOpt, $DB) or die(mysql_error());
$row_NavOpt = mysql_fetch_assoc($NavOpt);
$totalRows_NavOpt = mysql_num_rows($NavOpt);

Query 2:

//balked at "SELECT	Min(`Key`) which is what I really want
$query_NavMin = "SELECT	`Key`, Field1 FROM Pages_NavOption WHERE Pages_NavOption.Style = '6' GROUP BY Field1 order by `Display Order`";
$NavMin = mysql_query($query_NavMin, $DB) or die(mysql_error());
$row_NavMin = mysql_fetch_assoc($NavMin);
$totalRows_NavMin = mysql_num_rows($NavMin);

I'd like to build a loop where Query 1 runs from start to finish, and the Query 1 ('Key') column is compared to all results from the Query 2 'Key' column for each iteration of the loop. In other words, what I want is this:

• Compare 1st row of Query1('Key') with every row in Query2('Key');
• Compare 2nd row of Query1('Key') with every row in Query2('Key');
• Compare 3rd row of Query1('Key') with every row in Query2('Key');
• etc until all rows in Query 1's results are exhausted.

When I say 'Compare,' what I means is that if Query1('Key') == Query2('Key'), then I'm going to echo 'Match.' This seems like it should be easy, and I found a sample here that seems like it should work, but when I adapted it, I got nothing as a result. Can someone point me in the right direction? Thank you in advance for any help.

[Derokorian]

First I would loop thru qry2 to make it into an array. Untested, but should be along the right track.

$query_NavMin = "SELECT    `Key`, Field1 FROM Pages_NavOption WHERE Pages_NavOption.Style = '6' GROUP BY Field1 order by `Display Order`"; 
$NavMin = mysql_query($query_NavMin, $DB) or die(mysql_error()); 
$totalRows_NavMin = mysql_num_rows($NavMin); 
$result2 = array();

while( $row = mysql_fetch_assoc($NavMin) ) {
   $result2[] = $row;
}

$query_NavOpt = "SELECT * FROM Pages_NavOption WHERE Pages_NavOption.Style = '6' ORDER BY Pages_NavOption.`Display Order`"; 
$NavOpt = mysql_query($query_NavOpt, $DB) or die(mysql_error()); 
$totalRows_NavOpt = mysql_num_rows($NavOpt); 

while( $row = mysql_fetch_assoc($NavOpt) ) {
   foreach( $row as $k => $v ) {
      foreach( $result2 as $row2 ) {
         if( $row2[$k] == $v ) {
            echo 'Match<br />';
         }
      }
   }
}

HTH

[NogDog]

Might I ask what the objective is of this? It may well be that you can accomplish it with one query and avoid PHP gyrations to do it. (As far as I can see, what you've described here is simply going to display "match" for every result row in query #1, which by itself seems pointless without any context as to why.)

[laserlight]

bronzino wrote:

I'd like to build a loop where Query 1 runs from start to finish, and the Query 1 ('Key') column is compared to all results from the Query 2 'Key' column for each iteration of the loop. In other words, what I want is this:

• Compare 1st row of Query1('Key') with every row in Query2('Key');
• Compare 2nd row of Query1('Key') with every row in Query2('Key');
• Compare 3rd row of Query1('Key') with every row in Query2('Key');
• etc until all rows in Query 1's results are exhausted.

When I say 'Compare,' what I means is that if Query1('Key') == Query2('Key'), then I'm going to echo 'Match.'

If the keys can be sorted, then this algorithm is naive. It would be more efficient to sort both the result sets (using ORDER BY in the SQL query) then make a single simultaneous pass over each result set.

bronzino wrote:

balked at "SELECT Min(Key) which is what I really want

So what do you really want? Clearly, there is an underlying reason for your question, and if you tell us this, we may be able to suggest a far more appropriate solution to your problem than the one that you came up with. Not that coming up with your own solution is bad, of course.

[bronzino]

Sure. I am trying to output the contents of a CSS drop-down menu. For the menu, I need to demarcate the first item in a given submenu group as "<ul>" and the last item as "</ul>". Like so:

<ul id="menu">
	<li><a href="#">Home</a></li>
	<li><a href="#">Horses</a>
	<ul>
		<li><a href="#">Ceramic</a></li>
        	<li><a href="#">Custom</a></li>
		<li><a href="#">Plastic</a>
		<ul>
			<li><a href="#">Arabian</a></li>
			<li><a href="#">Stock Horse</a></li>
			<li><a href="#">Other</a></li>
		</ul>
		<li><a href="#">Resin</a></li>
	</ul>
</ul>

The table data looks like the attached screenshot. Field2 tells me if it's a top level or submenu item, because top level items do not need "<ul>" tags. Field1 tells the system by number what particular submenu a given item belongs to (4 is one group, 5 is another, and there will be more). I have each item ordered by the Display Order field because the items shouldn't appear in alphabetical order.

Another member here showed me, using mysql_dataseek, how to locate the last row in each submenu group so I could plug in the closing "</ul>" tag. What I am trying to do now is to locate the first "4" or first "5" in each group so I can have it stick in the opening "<ul>" tag. If I had any idea how difficult this was going to be, I probably would have opted to just put the darn tags in the table manually.

Query2 identifies all the submenu rows first matching 4 or 5 in Field1, and then returns their 'Key" field values. By comparing it to Query1 which is the full contents of the CSS Menu table, I thought what I was going to get is something checking whether any given row was the "first" 4 or 5, dropping in the tag where needed, and then moving to the next row. Not so much.