0

I've been trying to use PHP to display all the results from MySql NOTES table that match specific NAME in QUOTES table. Objective being I want to display all the COMMENTS listed under each specific NAME.

QUOTES table columns: ID / NAME / EMAIL / PRICE

NOTES table columns: ID / NAME / AGENT / DATE / COMMENTS

Here is the MySql code I'm using:

mysql_select_db($database_dbConnect, $dbConnect);
$query_rsquotes = "SELECT * FROM quotes ORDER BY `id` DESC";
$rsquotes = mysql_query($query_rsquotes, $dbConnect) or die(mysql_error());
$row_rsquotes = mysql_fetch_assoc($rsquotes);
$totalRows_rsquotes = mysql_num_rows($rsquotes);

mysql_select_db($database_dbConnect, $dbConnect);
$query_rsnotes = "SELECT quotes.*, notes.* FROM quotes INNER JOIN notes ON quotes.name = notes.name ORDER BY notes.date DESC";
$rsnotes = mysql_query($query_rsnotes, $dbConnect) or die(mysql_error());
$row_rsnotes = mysql_fetch_assoc($rsnotes);
$totalRows_rsnotes = mysql_num_rows($rsnotes);

Then to display results:

<?php do { ?>
    <?php echo $row_rsquotes['name']; ?>
    <?php echo $row_rsquotes['email']; ?>

<?php
    if ($row_rsquotes['name'] == $row_rsnotes['name']) {
        do {
            if ($row_rsquotes['name'] == $row_rsnotes['name']) {
                echo "<div class='agentNotes'><p><strong>DATE: </strong><span class='meta-date'>" . $row_rsnotes['date'] . "</span></p><p><strong>AGENT:</strong> " . $row_rsnotes['agent'] . "</p><p><strong>AGENT NOTES:</strong> " . $row_rsnotes['comments'] . "</p></div>";
             }
        } while ($row_rsnotes = mysql_fetch_assoc($rsnotes));
    }
?>

<?php } while ($row_rsquotes = mysql_fetch_assoc($rsquotes)); ?>

It loops through and displays the matching records for that particular NAME, but then when it gets to the next NAME, it does not display the matching COMMENTS. What am I doing wrong?

CAM308
  • 29
  • 9
  • 1
    Where do you set `$rsquotes`? – Barmar Jun 21 '17 at 23:46
  • 1
    Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly Jun 21 '17 at 23:49
  • You have one query but you're looping through two sets of results. Magical. – Matt Jun 21 '17 at 23:49
  • Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` – RiggsFolly Jun 21 '17 at 23:51
  • I updated question above to include the code I'm using for $rsquotes. – CAM308 Jun 22 '17 at 01:18
  • Note that mysql_fetch_* methods iterate through a result set once. Your code incorrectly assumes that $rsnotes is somehow reset for each iteration of $rsquotes. – reaanb Jun 22 '17 at 08:47
  • Thank you reaanb. That's exactly my problem. On the second loop through it's not picking up the next NAME. How would I reset $rsnotes, using mysql_data_seek($rsnotes,0) and where would I put that? – CAM308 Jun 23 '17 at 05:53

1 Answers1

0
query names_table containing Names
foreach(Names as Name){
    Query comment_table for comments that relates or maps Name
    foreach(comments as comment){
        display comment
     }
}
mw509
  • 1,957
  • 1
  • 19
  • 25
  • I'm not really sure what this is? Am I supposed to replace the words is blue with my table names? – CAM308 Jun 22 '17 at 01:24
  • This is pseudocode that suggests a different approach in which the relevant subset of comments is retrieved for each iteration through the set of names/quotes. It's a poor answer with no explanation, but it could guide you to a possible solution. – reaanb Jun 22 '17 at 08:52