Multiple Queries - Relationship Question

[EternalSorrow]

I'm currently working on a (somewhat) complicated database output code, and I've run into a jam.  Here's the full code (minus connection):

 

<?php

if (!is_numeric($_GET["year"]) && !empty($_GET["year"]) && $_GET["year"]!="")
{
$year = $_GET["year"];
}

$query = "SELECT * FROM feudal WHERE `year`= '$year' AND `type` = 'Fanfiction' GROUP BY category ORDER BY category ASC";
$result = mysql_query( $query ) or die(mysql_error());

echo '<h1>'.$year.' Winners List</h1>
<ol class="listwinner">';

while ($row = mysql_fetch_array($result))
{
extract($row);

$select_cat = "SELECT * FROM feudal WHERE `category` = '$category' ORDER BY place, title ASC";
$select_category = mysql_query($select_cat) or die (mysql_error());

$cat = "";
while ($row2 = mysql_fetch_array($select_category)) {

$select_aut = "SELECT * FROM feudal WHERE `id` = '$id' ORDER BY place, title ASC ";
$select_author = mysql_query($select_aut) or die (mysql_error());

$aut = "";
while ($row3 = mysql_fetch_array($select_author)) {
   $aut .= "by $row3[author] & ";
}

$aut = substr($aut,0,-2);

if ($row2['place'] == "first") {
$placing = 'first';
}
else if ($row2['place'] == "second") {
$placing = 'second';
}
else if ($row2['place'] == "third") {
$placing = 'third';
}

$empty = '';
if (strlen($row2['url']) == 0) { 
$empty = "(story lost)"; 
}

   $cat .= "<li class=\"$placing\"><a href=\"$row2[url]\" target=\"_blank\">$row2[title]</a> $aut $empty $id</li>";
}

echo '<h3>'.$category.'</h3>
'.$cat.'';

}
?>
</ol>

 

The problem lies in the two sub-WHILE statements.  The output should be like this:

 

Category

title1 by author1

title2 by author2

titley3 by author3

 

However, what I'm getting is this:

 

Category

title1 by author1

title2 by author1

titley3 by author1

 

I believe it's because this part of the code:

 

$select_aut = "SELECT * FROM feudal WHERE `id` = '$id' ORDER BY place, title ASC ";
$select_author = mysql_query($select_aut) or die (mysql_error());

$aut = "";
while ($row3 = mysql_fetch_array($select_author)) {
   $aut .= "by $row3[author] & ";
}

$aut = substr($aut,0,-2);

 

is not connecting properly to the second query (here called $select_cat).  I assume instead the $select_aut query is capturing information from the very first query performed (here simply called $query) or merely querying the database on its own.  Apparently merely embedding the $select_aut into the WHILE statement of $select_cat doesn't mean a relationship will form.

 

So here's my question: how can I have the third query ($select_aut) fetch information retrieved through the second query ($select_cat) and thus (hopefully) retrieve the correct author information?

[PFMaBiSmAd]

One of the great points of using a database is you can normally just retrieve the rows you want in the order that you want them, then you simply iterate over the rows in the result set in your presentation code to format the information the way you want.

 

You can do all of your queries using a single query. Then, someone just asked and was shown how to output a new heading only when it changes value - http://www.phpfreaks.com/forums/index.php/topic,279704.msg1324897.html#msg1324897

 

Edit: Untested, but a single query that looks like it will retrieve the rows you want in the order that you want them -

$query = "SELECT * FROM feudal WHERE `year`= '$year' AND `type` = 'Fanfiction' ORDER BY category, place, title"

 

I'm not sure how your author information is related to how you want your output, but you should probably add it in the ORDER BY list between category and place.

[EternalSorrow]

That link is exactly what I was looking for, so I tried out the snippet and it did lessen the mess.  Unfortunately the results don't appear to be correct, and I'm not sure where in my code the mistake lies.

 

The output I'm looking for is this:

 

Category

title1 by author1

title2 by author2

titley3 by author3

 

but what the code is giving me is this:

 

Category

title1 by author1

 

Category

title2 by author2

 

Category

titley3 by author3

 

Here's the updated code:

<?php

if (!is_numeric($_GET["year"]) && !empty($_GET["year"]) && $_GET["year"]!="")
{
$year = $_GET["year"];
}

$query = "SELECT * FROM feudal WHERE `year`= '$year' AND `type` = 'Fanfiction' ORDER BY category, place, title";
$result = mysql_query( $query ) or die(mysql_error());

echo '<h1>'.$year.' Winners List</h1>
<ol class="listwinner">';

while ($row = mysql_fetch_assoc($result))
{
extract($row);

$category = '';
//Check if the last category output is the same
if ($category != $row['category']) {
            //Assign category to holding var
$category = $row['category'];
            //echo Category heading
echo '<h3>'.$category.'</h3>';
}

if ($place == "first") {
$placing = 'first';
}
else if ($place == "second") {
$placing = 'second';
}
else if ($place == "third") {
$placing = 'third';
}

$empty = '';
if (strlen($url) == 0) { 
$empty = "<b>(story lost)</b>"; 
}

$select_aut = "SELECT * FROM feudal WHERE `id` = '$id' ";
$select_author = mysql_query($select_aut) or die (mysql_error());

$aut = "";
while ($row2 = mysql_fetch_array($select_author)) {
   $aut .= "by $row2[author] & ";
}

$aut = substr($aut,0,-2);

echo '<li class="'.$placing.'"><a href="'.$url.'" target="_blank">'.$title.'</a> '.$aut.'</li>';

}
?>
</ol>

[premiso]

 $category = '';
while ($row = mysql_fetch_assoc($result))
{
extract($row);

// $category = ''; // needs to be outside the loop
//Check if the last category output is the same
if ($category != $row['category']) {
            //Assign category to holding var
$category = $row['category'];
            //echo Category heading
echo '<h3>'.$category.'</h3>';
}

 

Basically you were reset the $category variable each time the loop ran, that needs to be outside of the loop to work properly.

[EternalSorrow]

Basically you were reset the $category variable each time the loop ran, that needs to be outside of the loop to work properly.

 

Forgot to mention that change.  Apparently whenever I place the $category outside the WHILE, the categories won't appear at all.

[PFMaBiSmAd]

The problem is the extract(). It is overwriting the $category variable being used in the presentation code by the $category variable from the query. Since you are using the correct $row['category'] variable, there is no need for the extract() (and you should not really use extract() without specifying one of the options as it happens to overwrite existing variables by default.)

[EternalSorrow]

I see...so I have to exclude the $category field from the extraction (which I believe merely involves listing out all others) to avoid all rows from invoking their $category.

 

I did just that and found the code now works perfectly.  Thanks so much PFMaBiSmAd and premiso for your help!