Mysql Query - Can't figure out how to do this

[eluser]cip6791[/eluser]
I have two tables, the first is the place table where the general information is kept and the second is the wait table where users sign up (like a waiting list).

Code:

+----+---------+---------+--------+-----------+---------------+-------+-------+-------+-----------+-------+------+---------------------+
| id | name    | user    | userid | address   | desc          | phone | map   | image | website   | cat   | cats | date                |
+----+---------+---------+--------+-----------+---------------+-------+-------+-------+-----------+-------+------+---------------------+
| 18 | title 2 | ciprian |      1 | compay+2  | description 2 |     0 | map 2 | image | website 2 | place | 0    | 2012-02-01 14:32:03 |
| 17 | title 1 | ciprian |      1 | company+1 | description 1 |     0 | map 1 | image | website 1 | place | 0    | 2012-02-01 14:31:23 |
+----+---------+---------+--------+-----------+---------------+-------+-------+-------+-----------+-------+------+---------------------+

Code:

+----+--------+---------+---------------------+
| id | userid | place_id | date                |
+----+--------+---------+---------------------+
| 72 |      1 |      18 | 2012-02-01 14:36:08 |
+----+--------+---------+---------------------+

For now I m doing a SELECT * FROM place; and displaying the data on the home page. Something like tihs:

Code:

<? foreach($places as $place): ?>
<? echo $place->name; ?> <? echo $place->userid; ?> etc ...
|a href="#">Click this to insert your userid and $place->id into wait table|/a|
<? endforeach ?>

This is where I got lost. I would like to do something like:

Code:

<? if($current_user_id == $userid_from_wait_that_matches_place_id): ?>
<p>You already registered for this!</p>
&lt;? else: ?&gt;
|a href="#">Click this to insert your userid and $place->id into wait table|/a|
&lt;? endif; ?&gt;

Not sure if it's better to check for the user's id in the model that adds data to the wait table or to check in the model that grabs data for the home page. From what I've read, the second option would be better.
Any help is appreciated.

---EDIT---
This can be taken as a favorites option. When a user likes a post he/she clicks a link(call it Like), that post's id and the user's id is inserted into the "favorites" (in my case) wait table. What I want to do is display all posts from table place on the front page, and change or disable the "Like" link (if it has been clicked).

[eluser]meigwilym[/eluser]
Sounds like you have three tables, if you count your users table.

I think what you're asking for here is a join. You might try the following in your `place` model:

Code:

public function get_place_wait()
{
  $this->db->select('*')
           ->from('place AS p')
           ->join('wait AS w', 'p.id = w.place_id');

  return $this->db->get()->result();
}

You should probably think wbout where statements too, to limit the data a bit.

Mei

[eluser]cip6791[/eluser]
Hey,

Thank you for your reply. I've been working on this and this is what I managed to do:

Code:

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

But there is a problem in the view. Every time somebody gets on the waiting list for a certain post, the foreach returns more than it should. This is my first time working with joins, that's why this is happening . I tried to add a group by ... but it's still not coming out right.

[eluser]magnushung[/eluser]
I would recommend you just put it all in one table and have a column for activity (e.g. waitlist, drop, or active)

[eluser]cip6791[/eluser]
Yeah ... i don't think that would work. How will multiple users sign up for the same post?

[eluser]meigwilym[/eluser]
You're not limiting your query with a where. Do you just want the info for a particular user?

Get rid of the GROUP BY and try WHERE place.userid = '1'.

Mei

[eluser]cip6791[/eluser]
I want to grab all the entries from place which can be considered posts, like a blog. These go on the home page. I've created an option where users can sign up for each individual post. But I don't want them to sign up twice. So I want to hide the sign up button or maybe change the icon.

[eluser]meigwilym[/eluser]
So you want to get all the records from the `places` table, but minus the ones the user has already signed up with, i.e. his entries in the `wait` table?

Try:

Code:

$this->db->query("SELECT * FROM places WHERE id NOT IN (SELECT placesid FROM wait WHERE userid = '$userid')");

And just a tip, when writing JOIN statements, make sure that the order of the two fields being compared match the order of the tables, i.e.

Code:

$sql = "SELECT *
        FROM left
        JOIN right ON
        left.id = right.left_id";

I'm not sure what difference it makes, and can't cite any sources but it's highly recommended.

Mei

[eluser]cip6791[/eluser]
Thanks for the help. But I want to get all the records from place. Consider what I m trying to do to a Add to favorites system.

1. This is the first item ----- Add to favorites
2. This is the second item ----- Add to favorites
3. This is the third item ------ Remove from favorites <--- This item's id and the user that liked it is in wait.

[eluser]meigwilym[/eluser]
I've only just notice, but why do you have an `userid` column in both tables?