Joins - wrong data

[iseriouslyneedhelp]

I've got this JOIN statement working except it's echoing the wrong id in my table. Do you see anything wrong with the code below? Here are my 2 tables

 

contacts Table

| id |

| fname |

| lname |

| 100 |

| Harry |

| Smith |

| 101 |

| Sally |

| Johnson |

 

contactsnotes Table

| id |

| contacts_id |

| leadnotes |

| timestamp |

| 1 |

| 100 |

| Hi! |

| Date |

| 2 |

| 101 |

| Bye! |

| Date |

 

 

function recentnotes ($conn) {

    $stmt = $conn->prepare("SELECT j.ID,n.leadnotes FROM contacts as j LEFT JOIN contactsnotes as n ON (j.ID = n.contacts_id) ORDER BY date DESC");
	$stmt->bind_result($ID,$leadnotes);
	$stmt->execute();
   		$stmt->fetch();

        echo'<li>'.$leadnotes.'</li>';

$stmt->close();
}

[haku]

I'm guessing that the wrong ID is echoing something along the lines of "what the hell are you doing, this is the wrong ID". Is that correct? If not, then you'll have to give us more info seeing as you didn't.

[boompa]

Go back to the manual page and re-read the "Note" under the "Description" section.

[iseriouslyneedhelp]

Go back to the manual page and re-read the "Note" under the "Description" section.

 

Thanks for pointing that out, I was playing around with the bind stmt and it doesn't seem to matter if it's before or after the execute stmt. It certainly doesn't solve my issue.

[iseriouslyneedhelp]

I'm guessing that the wrong ID is echoing something along the lines of "what the hell are you doing, this is the wrong ID". Is that correct? If not, then you'll have to give us more info seeing as you didn't.

 

Thanks, no. The wrong notes are being displayed from the contactsnotes table. What more information would you need, I showed you my stmt's and tables?

 

I tried defining ID and contacts_id and still shows the wrong notes, it shows the same notes for everyone ...

[PFMaBiSmAd]

$stmt->fetch(); only fetches one row from the result set. You would need to use a loop to iterate over all the rows in the result set. The mysqli_stmt_fetch documentation contains an example that shows how you can loop over all the rows in the result set - http://us.php.net/manual/en/mysqli-stmt.fetch.php

[iseriouslyneedhelp]

$stmt->fetch(); only fetches one row from the result set. You would need to use a loop to iterate over all the rows in the result set. The mysqli_stmt_fetch documentation contains an example that shows how you can loop over all the rows in the result set - http://us.php.net/manual/en/mysqli-stmt.fetch.php

 

Thanks. Yes, I was first trying to get it to work! Anyway, I solved my own problem. I wasn't putting a WHERE clause in the statment...duh

 

$stmt = $conn->prepare("SELECT j.ID,n.contacts_id,n.leadnotes,n.timestamp FROM contacts j JOIN contactsnotes n ON (j.ID = n.contacts_id ) WHERE (j.ID = $ID) ORDER BY timestamp DESC");

[haku]

I'm guessing that the wrong ID is echoing something along the lines of "what the hell are you doing, this is the wrong ID". Is that correct? If not, then you'll have to give us more info seeing as you didn't.

 

Thanks, no. The wrong notes are being displayed from the contactsnotes table. What more information would you need, I showed you my stmt's and tables?

 

I tried defining ID and contacts_id and still shows the wrong notes, it shows the same notes for everyone ...

 

For future reference "it's echoing the wrong id in my table" isn't very descriptive. It doesn't tell us what ID you were trying to show, and it doesn't tell us what ID was incorrectly being shown.