i have a big trouble using multiple queries inside each other. and i searched for it but could not find what i needed! so this is my problem. plz help me.
i have a table named "cat" and i get the titles by mysql and it's ok. i have an other table named works that has a field named "Cat_ID" this is the field that connect's the data of both tables. works table has another field named "Years" and it's the year of the production of artwork.
cat >> { ID , Title , ... } works >> { ID , Title , URL , Year , Cat_ID , ... }
i need to categorize the gallery of works by year, so i made a code like this:
<?php
$result = q("cat");
while($row = mysqli_fetch_array($result)) { ?>
<div>
<h2><a href="artwork.php?id=<?php echo $row['ID'] ?>"> <?php echo $row['Title']; ?></a></h2>
<span>
<?php
$sql = "SELECT DISTINCT Year FROM works WHERE Cat=".$row['Cat_ID'];
$yresult = mysql_query($sql);
while ($yrow = mysql_fetch_row($yresult))
{
//and print it as anchor here but it wont get into loop at all!
}
?>
</span>
</div>
<?php } ?>
the q() is a function that just connects to DB and run the query. this line works pretty well. but the it wont enter to the inner while at all! i checked and saw that the $yrow is totally empty(by EMPTY() of php)! what can i do about it? it seems that mysql can not execute the second query at all! the solution must be in two ways i think. 1. make mysql to execute the second query. 2. change the first query in the way that get's the years distinctly and read them by mysql_fetch_array and put the years as anchor via for each.
plz help me.