nesting queries

artist-ink

is it possible to nest queries?

i want this code to go through a database and select each distinct entry in a column of a table(column=Category, table=Link_Info). When it pull eachs one of these categories, I want it to run another query for each one of the distinct values in the column.

the code below works to pull each distinct value

<?php
include("XXXXX.php");

  $connection = mysql_connect($host,$user,$password)
       or die ("couldn't connect to server");
  $db = mysql_select_db($database,$connection)
       or die ("Couldn't select database");
  $query = "SELECT DISTINCT Category FROM Link_Info ORDER BY Category";
  $result = mysql_query($query)
       or die ("Couldn't execute query.");

  /* create form containing selection list */

  while ($row = mysql_fetch_array($result))
  {
     extract($row);
     echo "$Category<BR>\n";
  }
 ?>

and this code below will display the information that I want

<?php

include("XXXXX.php");

 $connection = mysql_connect($host, $user, $password)
  or die ("oops, can't reach server");
 $db = mysql_select_db($database, $connection)
  	or die ("oops, can't reach database");

/* Display results in a table: ArtsHumanities */  

  $query_ArtsHumanities = "SELECT * from Link_Info WHERE Category='Arts & Humanities' ORDER BY Link_Display";
  $result = mysql_query($query_ArtsHumanities)
       or die ("Couldn't execute query.");

  echo "<p class='categorytitle'>Arts &amp; Humanities<a name='Arts'></a></p>";
  echo "<table>";
    while ($row = mysql_fetch_array($result))
  {
     extract($row);
           echo "<tr> \n
           <td><a href='$Link_URL' target='blank'>$Link_Display</a></td></tr>\n
     <tr><td class='link_description'><ul><li>$Link_Description</li></td></tr></tr>\n";

 echo "<tr><td></td></tr>\n";
  }
  echo "</table><br>\n";

I tried to combine them into the code below, but i'm not getting any results returned. all that i am seein is a blank page(except for the html that is written outside of the php).

<?php
include("XXXXX.php");

  $connection = mysql_connect($host,$user,$password)
       or die ("couldn't connect to server");
  $db = mysql_select_db($database,$connection)
       or die ("Couldn't select database");
  $query = "SELECT DISTINCT Category FROM Link_Info ORDER BY Category";
  $result = mysql_query($query)
       or die ("Couldn't execute query.");

  /* create form containing selection list */
 while ($row = mysql_fetch_array($result))
  {
     $query_category = "SELECT * from Link_Info WHERE Category='$Category' ORDER BY Link_Display";
  $result = mysql_query($query_category)
       or die ("Couldn't execute query.");

  echo "<p class='categorytitle'>$Category<a name='Arts'></a></p>";
  echo "<table>";
    while ($row = mysql_fetch_array($result))
  {
     extract($row);
           echo "<tr> \n
           <td><a href='$Link_URL' target='blank'>$Link_Display</a></td></tr>\n
     <tr><td class='link_description'><ul><li>$Link_Description</li></td></tr></tr>\n";

 echo "<tr><td></td></tr>\n";
  }
  echo "</table><br>\n";
  }

?>

any ideas about why this isn't working, or better ways to do what i'm trying to do?

thanks for the help.

bpat1434

I believe this might be what you're looking for:

<?php
// Include external files
// Put connection info here too

$query = "SELECT * FROM Link_Info GROUP BY Category";
$result = mysql_query($query) or die("Unable to run initial query");
while($row = mysql_fetch_array($result)){
    echo "<p class='category_title'><a name=\"".$row['Category']."\"></a>".$row['Category']."</p>";
    echo "<table>";

$q2 = "SELECT * FROM Link_Info WHERE Category = '$row['Category'] ORDER BY id";
$rslt = mysql_query($q2);
while($row2 = mysql_fetch_array($rslt)){
    echo "<tr>\n";
    echo "<td><a href=\"".$row2['Link_URL']."\ target=\"_blank\">".$row2['Link_Display']."</a></td></tr>";
    echo "<tr><td class='link_description'><ul><li>".$row2['Link_Description']."</li></td></tr>";
    echo "<tr><td></td></tr>";
}
echo "</table>";
}

// Whatever else you need to display
?>

That will output something like:

Arts & Humanities
<a href="{link_url}">Link Display</a>
Link description......................................

Category #2
..............

Hope that helps.

~Brett

artist-ink

thanks for the input. i was able to get the results i wanted by doing th e following:

while ($row = mysql_fetch_array($result))
{
    $Category = $row['Category'];
$query_category = "SELECT * from Link_Info WHERE Category='$Category' ORDER BY Link_Display";
   $result2 = mysql_query($query_category)
      or die ("Couldn't execute query.");

  echo "<p class='categorytitle'>$Category<a name='$Category'></a></p>";
  echo "<table>";
   while ($row = mysql_fetch_array($result2))

lazzerous

ok dood...

you have $row handling the output from the first select statement

while looping through $row, you're assigning it different values from a different select statement

try using $row & $row2

bpat1434

But what he has is okay, because each time the main loop loops, it will give $Category a new value, based upon the other query. The second loop will use $row as needed.

Although your code works, it would be better to use $row & $row2 for simplicity and to stay away from errors.

~Brett

artist-ink

how's this:

/* create list of 'jump-to' links for each entered category */	   

	$query_jumplist = "SELECT DISTINCT Category FROM Link_Info ORDER BY Category";
  	$result_jumplist = mysql_query($query_jumplist)
       or die ("Couldn't execute jumplist query_jumplist");

echo "<p>";
while ($row = mysql_fetch_array($result_jumplist))
  	{
  	extract($row);	
	echo "<a href='#$Category'>$Category</a><BR>";   

	}

echo "</p>";

  /* create list of links & descriptions for each of the users database entries*/ 
$query_distinct_Cat = "SELECT DISTINCT Category FROM Link_Info ORDER BY Category";
  $result_distinct_Cat = mysql_query($query_distinct_Cat)
       or die ("Couldn't execute query_distinct_Cat.");

 while ($row_distinct_Cat = mysql_fetch_array($result_distinct_Cat))
  {
     $Category = $row_distinct_Cat['Category'];
	 $query_display_links = "SELECT * from Link_Info WHERE Category='$Category' ORDER BY Link_Display";
  	 $result_display_links = mysql_query($query_display_links)
       or die ("Couldn't execute query_display_links.");

echo "<p class='categorytitle'>$Category<a name='$Category'></a></p>";
echo "<table width='75%'>";
while ($row_display_links = mysql_fetch_array($result_display_links))
	{
 		extract($row_display_links);
       	echo "<tr> \n
       	<td><a href='$Link_URL' target='blank'>$Link_Display</a></td></tr>\n
	   	<tr><td class='link_description'><ul><li>$Link_Description</li></td></tr></tr>\n";

 		echo "<tr><td></td></tr>\n";
	 }
echo "</table><br>\n";
  }

?>

thanks for the help.