mysql join problem

[cip6791]

Hello,

I want to create a favorites option for something I am working on. I have two tables. The main table where the posts are kept called "place".

+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL              | auto_increment | 
| name    | varchar(30)  | YES  |     | NULL              |                | 
| userid  | int(3)       | YES  |     | NULL              |                | 
| address | varchar(300) | YES  |     | NULL              |                | 
| desc    | varchar(550) | YES  |     | NULL              |                | 
| phone   | int(15)      | YES  |     | NULL              |                | 
| image   | varchar(50)  | YES  |     | NULL              |                | 
| website | varchar(100) | YES  |     | NULL              |                | 
| cat     | varchar(25)  | YES  |     | NULL              |                | 
| date    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                | 
+---------+--------------+------+-----+-------------------+----------------+

and the favorite table called "wait"

+---------+-----------+------+-----+-------------------+----------------+
| Field   | Type      | Null | Key | Default           | Extra          |
+---------+-----------+------+-----+-------------------+----------------+
| id      | int(11)   | NO   | PRI | NULL              | auto_increment | 
| userid  | int(11)   | YES  |     | NULL              |                | 
| place_id | int(11)   | YES  |     | NULL              |                | 
| date    | timestamp | NO   |     | CURRENT_TIMESTAMP |                | 
+---------+-----------+------+-----+-------------------+----------------+

I am using codeigniter and displaying the data on the home page like this.

<? foreach ($places as $place): ?>
      <? echo $place->title; ?>
      <a href="#" id="addfav">Add to favorites</a>  <----- This sends the user's id and place id to wait table. I want to display this if the current user hasn't clicked on it already. 
      <? endforeach; ?>
I tried something like this:

$query_str ="select place.id, place.name, place.desc, wait.userid, users.username 
from place left join wait on place.id=wait.chat_id 
left join users on place.userid=users.id";

I am also querying the users table to get the user who created the post. Not too familiar with joins so any help is appreciated. I only have two posts and two users at the moment and if both users favorite each post, the foreach displays four posts instead of two.

[rch1231]

Hello,

The first thing I can see is the wait table does not have a field called chat_id so the join will fail there.

[cip6791]

This is what was suggested and it works. Just in case anyone has the same problem.

create table users (id serial, name varchar(255) not null);
create table posts (id serial, user_id bigint unsigned not null, context text not null);
create table saved_posts (post_id bigint unsigned not null, user_id bigint unsigned not null, unique key (post_id,user_id));

select a.*,b.user_id from posts a left outer join saved_posts b on a.id=b.post_id and b.user_id=67;