Repeat record problem

[emediastudios]

Hi Everyone, could anyone tell me why this only shows one record.

There is at least four that will match the query.

 

case 'jayzquotes';
echo '<table border="0" cellspacing="0" cellpadding="2"><tr><td><h1>Jay-Z Quotes</h1></td></tr></table><br>';


 echo '<table width="600" border="0" cellspacing="0" cellpadding="2">';
  
  $result = mysql_query("SELECT * FROM quotes WHERE artist LIKE 'J%' and approved = 'y'")
or die(mysql_error()); 

$count = mysql_num_rows ($result );
while($row=mysql_fetch_array($result)){

  
$resultb = mysql_query("SELECT * FROM comments WHERE quoteid=".$row['quoteid']."")
or die(mysql_error()); 
while($link=mysql_fetch_array($resultb)){

$date = $row[date];
$user = $row[user];
$email = $row[email];
$artist = $row[artist];
$song = $row[song];
$quote = stripslashes($row[quote]);
$quoteid = $row[quoteid];
$votes = $row[votes];


echo '
<tr>
  <td width="424"><strong>Date Submitted:</strong><span class="red"> '.$date.'</span></td>
  <td width="156" align="right"><strong>Total Votes</strong> (<span class="red">'.$votes.'</span>)</td>
  </tr>
<tr>
  <td colspan="2"><strong>Quote submitted by:</strong><span class="red"> '.$user.' </span>- <span class="red"><a href="mailto:'.$email.'">'.$email.'</a> </span></td>
  </tr>
<tr>
  <td colspan="2"><strong>Artist:</strong> <span class="red">'.$artist.' </span></td>
  </tr>
<tr>
  <td colspan="2"><strong>Song name:</strong> <span class="red">'.$song.' </span></td>
  </tr>
<tr>
  <td colspan="2"><strong>Quote:</strong><hr></td>
  </tr>
<tr>
    <td colspan="2">'.$quote.'</td>
    </tr>
<tr>
  <td colspan="2"><table width="100%" border="0" cellspacing="0" cellpadding="5" class="quotefooter">
    <tr>
      <td><strong>(<span class="dark">'.$countb.'</span>) Comments: View / Edit Comments</strong></td>
      <td align="center"><strong><a href="index.php?action=editquote&quoteid='.$quoteid.'" target="_self">Edit Quote</a></strong></td>
      <td align="center"><strong><a href="index.php?action=deletequote&quoteid='.$quoteid.'">Delete Quote</a></strong></td>
        </tr>
      </table><br><hr></td>
  </tr>
';
	}
}
echo '</table>';
echo '<strong>Total Results</strong><span class="red"> '.$count.'</span>';

break;

[litebearer]

1. which query is only returning 1 result?

2. what is the value of $count?

 

[Pawn]

All of the output is in the second while loop. You'll get as many copies of that HTML as rows returned in $resultb.

[BlueSkyIS]

you do this, but never use $link?

 

while($link=mysql_fetch_array($resultb)){

[emediastudios]

The $link was going to be for displaying a count,

But anyway, it probably not the best way  to achieve what i am aiming for.

 

I'm doing it the hard way i am sure.

 

Basically, I have a quote table and a comment table.

both tables have a field called quoteid, i use this to match comments made to the quote.

 

When calling the comments, i want to show details from the quote table as well, such as song name, artist.

and when calling the quotes, i want to show the total amounts of comments made on it.

 

 

[rwwd]

case 'jayzquotes';

 

Invalid syntax, it should read:-

 

case 'jayzquotes':

//your code

break;

 

Rw

[litebearer]

I always like to be sure I get what I expect before 'dressing up' the results.

 

Try this and see what you get. If it provides the desired output, then 'massage' your code to make it look pretty.

$result = mysql_query("SELECT * FROM quotes WHERE artist LIKE 'J%' and approved = 'y'") or die(mysql_error()); 
while($row=mysql_fetch_array($result)){
$date = $row[date];
$user = $row[user];
$email = $row[email];
$artist = $row[artist];
$song = $row[song];
$quote = stripslashes($row[quote]);
$quoteid = $row[quoteid];
$votes = $row[votes];
$resultb = mysql_query("SELECT * FROM comments WHERE quoteid= '$quoteid'") or die(mysql_error()); 
while($link=mysql_fetch_array($resultb)){
	echo "date: " . $date . " Total Votes: " . $votes . " Submitted By: " .$user. " Email: . $email . "<br>";
	echo "Artist: " .$artist . "<br>";
	echo "Song name: " . $song . "<br>";
	echo "Quote: " . $quote . "<br>";
	echo "<hr>";
}
echo "<br><br>";
}

[rwwd]

  Quote

I always like to be sure I get what I expect before 'dressing up' the results.

 

Yup, couldn't agree more with that, when I use a while loop getting information from a DB, then within that loop I just place a print_r with the array var from the query, simple, yet effective!

 

Rw

[emediastudios]

:D Thanks guys