Need a bit of help with getting data from multiple mysql tables

[man5]

It would really help to have an outside view to what I am trying to do.

 

I have 1 users table. 1 posts table and 1 favorites table.  Basically what I am trying to do is give a user an option to add other users' posts to their favorites folder.

 

Say these are the options for each table in mysql.

 

users::

- id

- username

 

posts::

- post_id

- user_id

- title

- post

 

favorites::

- favorite_id

- user_id

- post_id

 

Now I know I can use joins to join these tables together. I have tried them for other things and they work.  It's just that with this particular case, I am having some issues getting the query right.

 

This is my query. Please point out what's wrong with it and how to do it correctly.

$stmt = $dbh->prepare("SELECT users.*, posts.*, favorites.* FROM posts
   LEFT JOIN users ON records.user_id = users.id
   LEFT JOIN favorites ON posts.user_id = favorites.id
   WHERE posts.user_id = {$userid} ORDER BY posted DESC LIMIT 4");

ps. $userid is definied outside of this query, so don't worry about it.

[Yohanne]

Hi,

 

you post is only for querying and joining table but you asking to add something from post? 

[man5]

Hi,

 

you post is only for querying and joining table but you asking to add something from post? 

Nope, I know how to add and retrive posts from mysql table.  It's just that I am having hard time getting data from multiple joined tables.

 

Say I have post#1 inserted in favorites table.  I can retrive the post by using user_id or post_id, how ever it won't give me the actual post which consists of title and post desc. 

 

I am assuming foreign keys are created(unless defined as primary key) automatically when creating table in mysql. And it seems like foreign keys don't work on Myisam table, only Innodb.  However, my posts table is Myisam. Main reason is because of FULL-TEXT search.

Edited April 2, 2014 by man5

[Yohanne]

SELECT users.id,

       user.username,

            posts.post_id,

            posts.user_id,

            posts.title,

            posts.post,

                  favorites.favorite_id,

                  favorites.user_id,

                  favorites.post_id 

FROM users INNER JOIN posts ON users.id = posts.user_id INNER JOIN favorites ON posts.post_id = favorites.post_id ORDER BY date limit 4

[man5]

SELECT users.id,
       user.username,
            posts.post_id,
            posts.user_id,
            posts.title,
            posts.post,
                  favorites.favorite_id,
                  favorites.user_id,
                  favorites.post_id 
FROM users INNER JOIN posts ON users.id = posts.user_id INNER JOIN favorites ON posts.post_id = favorites.post_id ORDER BY date limit 4

 

 

That's great. It works.  The only thing is that it returns all the rows from the favorites table. I am trying to work out the WHERE clause.

[man5]

Alright, it works now 100%.  Here is the updated code.

SELECT users.id,
       user.username,
            posts.post_id,
            posts.user_id,
            posts.title,
            posts.post,
                  favorites.favorite_id,
                  favorites.user_id,
                  favorites.post_id 
FROM users INNER JOIN posts ON users.id = posts.user_id INNER JOIN favorites ON posts.post_id = favorites.post_id 
WHERE favorites.user_id = {$myuserid} ORDER BY date limit 4

//remember, $myuserid is the user id of the user currently logged in. 

Edited April 2, 2014 by man5

[Yohanne]

thats good