This is my first attempt at a join so please be nice. And yes I have been trouble shooting and have error reporting on etc. Okay so I have 2 tables
articles - id, category, title, mainc
comments - user_id, name, email, comment
I of course want to display the comments posted for the article it is associated with. So far all of my attempts return no errors just a blank screen. Here is my latest attempt.
// Retrieve the user's name and associated list items
$sql = "SELECT *
FROM comments
LEFT JOIN articles
USING (Id)
WHERE Id = user_id";
$stmt = $conn->prepare($sql); // Prepare the statement
if($stmt->execute(array($_POST['user_id'])))
{
// Loop through the returned results
while($row = $stmt->fetch()) {
// Save the user name
$name = $row['name'];
// Create an array of items
$items[] = $row['comment'];
}
$stmt->closeCursor(); // Free memory used in this query
// Output the user's name and identify what we're displaying
echo "$name List<br /><br />n";
// Loop through the items and output to the browser
foreach($items as $item) {
echo $item, "<br />";
}
}
You can try wrapping your code in a try/catch and see if you get any more errors - a blank screen can be frustrating as I have been there!
try {
// Retrieve the user's name and associated list items
$sql = "SELECT *
FROM comments
LEFT JOIN articles
USING (Id)
WHERE Id = user_id";
$stmt = $conn->prepare($sql); // Prepare the statement
if($stmt->execute(array($_POST['user_id'])))
{
// Loop through the returned results
while($row = $stmt->fetch()) {
// Save the user name
$name = $row['name'];
// Create an array of items
$items[] = $row['comment'];
}
$stmt->closeCursor(); // Free memory used in this query
// Output the user's name and identify what we're displaying
echo "$name List<br /><br />n";
// Loop through the items and output to the browser
foreach($items as $item) {
echo $item, "<br />";
}
}
}
// Any errors?
catch(PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
}
$sql = <<<EOT
SELECT *
FROM comments
LEFT JOIN articles
ON articles.id = comments.article_id # The comments will need a reference to the article
WHERE comments.user_id = :user_id # The : makes it a named parameter
EOT;
$stmt = $conn->prepare($sql);
$stmt->execute(array('user_id' => 42));
$rows = $stmt->fetchAll();
print_r($rows);
Consider running mysql and making sure you have the correct sql syntax.
As comments is your primary table in this query you’ll want to make the primary condition field from this table, in this case user_id and compare this to a post value. Your query didn’t define this placeholder value. I defined it as :user_id. We are then joining the articles table ON id = user_id from the comments table.
$sql = "SELECT
c.user_id,
c.name,
c.email,
c.comment,
a.id,
a.category,
a.title,
a.mainc
FROM comments AS c
LEFT JOIN articles AS a
ON a.id = c.user_id
WHERE c.user_id = :user_id";
$stmt = $conn->prepare($sql); // Prepare the statement
if($stmt->execute(array(':user_id' => $_POST['user_id'])))
{
EDIT: ahundiak you beat me to it.
Ya, I was looking for an article_id but didn’t see it listed as a table field but that would make more sense.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql = SELECT *
FROM comments
LEFT JOIN articles
ON articles.id = comments.articles_id' at line 1
It’s a start anyways. Why is comments.user_id the link to articles.id? Really seems like you need a article_id column in your articles table. Try
describe comments;
describe articles;
And pasting the results in here. If you have a users table then describe it as well.
And PDO is just a way to execute sql statements. You need to have the sql correct or you end up chasing your tail around.
okay so what I am trying to do is set up my db for an articles page that someone can post comments to and I need a way to track the comments for each article etc.
I have 2 tables right now
articles - article_id (primary auto), title, author, category, mainc
comments - user_id (primary auto), name, email, comment
(maybe I need a third) for users
this
$sql = "SELECT `comments`.*, `article`.`id`
FROM comments, article
WHERE comments.user_id = article.id";
returns a record for user_id 1 for article 1
But is user 1 posts to more articles I’m stuck
articles - article_id (primary auto), title, author, category, mainc
comments - comment_id (primary auto), article_id, user_id, comment
users - user_id (primary auto), name, email, any other personal field like login etc.
Then you can see that it would be simple to grab article as the primary table, then any comments based on article_id and then any user info based on the user_id from the comments table.
author could also be the id of the author so their name or other personal details can be grabbed from that ID.
okay so now in order to get the article id, article title, user id, name, email, comment how do I pull the info from all 3 tables? I am still trying to wrap my head around joins.
$sql = "SELECT user_id, article_id, title, author, comment, name, email
FROM users
LEFT JOIN comments
USING (user_id)
LEFT JOIN test
USING (article_id)
WHERE user_id = article_id";
$stmt = $conn->prepare($sql); // Prepare the statement
if($stmt->execute(array(':user_id' => $_POST['user_id'])))
{
// Loop through the returned results
while($row = $stmt->fetch()) {
// Save the user name
$user_id = $row['user_id'];
$article_id = $row['article_id'];
$title = $row['title'];
$author = $row['author'];
$name = $row['name'];
$email = $row['email'];
// Create an array of items
$items[] = $row['comment'];
}
$stmt->closeCursor(); // Free memory used in this query
// Output the user's name and identify what we're displaying
echo " User Id - $user_id <br />";
echo "Article - $article_id <br />";
echo "Title - $title<br />";
echo "Author - $author<br />";
echo "User - $name<br />";
echo "User Email - $email<br /><br />";
// Loop through the items and output to the browser
foreach($items as $item) {
echo $item, "<br />";
}
}
I am getting 2 records returned with everything displayed. but even if I put. http://www.xxxxxxx.com/test/join.php?user_id=5 I get the same data displayed and there should be nothing for that.