nested query

innovations

I would like to display records from two databases which are related by a primary and foriegn key. I've done this before in ColdFusion but now I am trying it with PHP.

The output should look something like this:

Category 1

subcategory
subcategory
subcategory
subcategory
subcategory
[/list=1]

Category 2
1. subcategory
2. subcategory
3. subcategory
4. subcategory
5. subcategory
  [/list=1]
  
  Category 3
  1. subcategory
  2. subcategory
  3. subcategory
  4. subcategory
  5. subcategory
    [/list=1]
    
    I would really appreciate any help!

kburger

table structures??

Not enough to go on here, but it would be something like

SELECT categories.category, subcategories.subcategory 
FROM categories, subcategories 
WHERE categories.category=subcategories.category 
ORDER BY categories.category;

innovations

my appologise :bemused:

I have two tables, Categories and Topics. Categories is the Top most level and Topics is the subcategory which contains the foriegn key.

tblCatagories:

cID == autoincrement (Primary Key)
cTitle == varchar 25 (category title or name)

tblTopics:

tID == autoincrement (Primary Key)
c_ID == integer (Foriegn Key)
tTitle == varchar 25 (topic title or name)

That's it!

kburger

SELECT tblCatagories.cTitle, tblTopics.tTitle 
FROM tblCatagories, tblTopics 
WHERE tblCatagories.cID=tblTopics.c_ID 
ORDER BY tblCatagories.cTitle, tblTopics.tTitle;

or condensed, since there are no duplicate field names

SELECT cTitle, tTitle 
FROM tblCatagories, tblTopics 
WHERE cID=c_ID 
ORDER BY cTitle, tTitle;

innovations

That is the same sql that I used but when I try to display the records from the database each record from the top category is displayed with all of the topics from the entire table nested within them. I hope that makes sense. I think the problem is in my php code rather than my sql.

Let me try it again and see what I get.

kburger

Yes, I understand what you're saying. Probably what you'll want to do is, like you said, use 2 nested queries:

(pseudocode)

select all categories {
   echo a category
   select all subcategories of that category {
      echo a subcategory
   }
}

innovations

I am trying to put that into code ~ I will report back shortly.

innovations

Dreamweaver won't let me nest two queries - I wish I was a more efficient coder in php :queasy:

kburger

Try this:

$query = "SELECT cTitle, cID FROM tblCatagories ORDER BY cTitle";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
while (list($cTitle, $id) = mysql_fetch_row($result)) {
	echo "$cTitle\n<ol>";
	$query2 = "SELECT tTitle FROM tblTopics WHERE c_ID='$id' ORDER BY tTitle";
	$result2 = mysql_query($query2) or die("Query failed: " . mysql_error());
	while (list($tTitle) = mysql_fetch_row($result2)) {
		echo "<li>$tTitle</li>\n";
	}
	echo "</ol>\n\n";
}

innovations

That works beautifully!

So you create a list out of the database records for the Categories table and assign the list the variable $id.

Then create another list, this time out of the Topics table, specifying that you want the records which match the value in the $id variable.

That kind of makes sense to me. There is another way of doing this in ColdFusion but I bet it could be done this way as well.

I also wish I new how to write class files and seperate the code from the html. Also, it would be nice to reuse this logic on several pages by passing the values for the query to the class method.

Maybe someday...

kburger

You can easily do that. If you want to reuse this code on multiple pages just put it into a function, say showCategories();

function showCategories() {
	$query = "SELECT cTitle, cID FROM tblCatagories ORDER BY cTitle";
	$result = mysql_query($query) or die("Query failed: " . mysql_error());
	while (list($cTitle, $id) = mysql_fetch_row($result)) {
		echo "$cTitle\n<ol>";
		$query2 = "SELECT tTitle FROM tblTopics WHERE c_ID='$id' ORDER BY tTitle";
		$result2 = mysql_query($query2) or die("Query failed: " . mysql_error());
		while (list($tTitle) = mysql_fetch_row($result2)) {
			echo "<li>$tTitle</li>\n";
		}
		echo "</ol>\n\n";
	}
}

Whenever you want to show that list just put this into your HTML:

<?php showCategories(); ?>

Just make sure you put the actual function either into that file, or into a file that gets included on the page.

innovations

Perhaps I could do something like this:

$query = "SELECT cTitle, cID FROM tblCatagories ORDER BY cTitle";
$query2 = "SELECT tTitle FROM tblTopics WHERE c_ID='$id' ORDER BY tTitle"; 
<?php showCategories($query, $query2); ?>

And this would be my function:

function showCategories($query, $query2) {  

    $result = mysql_query($query) or die("Query failed: " . mysql_error()); 
    while (list($cTitle, $id) = mysql_fetch_row($result)) { 
        echo "$cTitle\n<ol>";
        $result2 = mysql_query($query2) or die("Query failed: " . mysql_error()); 
        while (list($tTitle) = mysql_fetch_row($result2)) { 
            echo "<li>$tTitle</li>\n"; 
        } 
        echo "</ol>\n\n"; 
    } 
}

The problem with this is the hard coded references to the table fields in the while loops and echo statements. Also, I would like to seperate the code so that the echo statements were removed somehow. For the code without the function I did as follows:

$query = "SELECT cTitle, cID FROM Categories ORDER BY cTitle"; 
$result = mysql_query($query) or die("Query failed: " . mysql_error()); 
while (list($cTitle, $id) = mysql_fetch_row($result)) { ?>
    <h2><? echo $cTitle ?></h2>
    <?
	$query2 = "SELECT tTitle FROM Topics WHERE c_ID='$id' ORDER BY tTitle"; 
    $result2 = mysql_query($query2) or die("Query failed: " . mysql_error()); 
    while (list($tTitle) = mysql_fetch_row($result2)) { ?>
        <a href="filesize.php"><? echo $tTitle ?></a><br />
	<?
    } 
}
?>

innovations

Problem: How do I pull other fields out of the Topics database to echo on the page in the second loop?

kburger

I'm assuming you mean the tblTopics table. Simply select them in your query and get their values.

$query2 = "SELECT tTitle, somefield FROM tblTopics WHERE c_ID='$id' ORDER BY tTitle";
$result2 = mysql_query($query2) or die("Query failed: " . mysql_error());
while (list($tTitle, $somefield) = mysql_fetch_row($result2)) {

or to select all fields

$query2 = "SELECT * FROM tblTopics WHERE c_ID='$id' ORDER BY tTitle";
$result2 = mysql_query($query2) or die("Query failed: " . mysql_error());
while ($row = mysql_fetch_array($result2)) {

then access them with $row['fieldname']

innovations

Very interesting... those While loops are very different! I am still trying to figure them out a bit. The good news is that it works. Here is my code if you want to see what I am doing:

<table width="75%" >
	<?
		/* Topic Query */
		$record = $_GET['rID'];
		mysql_select_db($database_conTest, $conTest);
		$query = "SELECT tTitle, tID FROM Topics WHERE cat_ID = '$record' ORDER BY tID"; 
		$result = mysql_query($query) or die("Query failed: " . mysql_error()); 
			while (list($tTitle, $topID) = mysql_fetch_row($result)) { ?>
		  <tr>
			<td><h2><? echo $tTitle ?></h2></td>
		  </tr>
		<? 
			/* Series Query */
			$query2 = "SELECT * FROM Series WHERE topic_ID='$topID' ORDER BY sTitle"; 
			$result2 = mysql_query($query2) or die("Query failed: " . mysql_error());
			while ($row = mysql_fetch_array($result2))  { ?>
		  <tr>
			<td><a href="series.php?rID=<? echo $row['sID']; ?>"><? echo $row['sTitle']; ?></a></td>
		  </tr>
		  <tr>
			<td>CD &gt; Cassette:<? echo $row['sAudPrice']; ?> DVD &gt; VHS: <? $row['sVidPrice']; ?></td>
		  </tr>
		 <? }
		} ?>
	</table>