CODEWITHSUNDEEP
phpmysqlcodeigniter
Ciprian
Ciprian

Reputation: 3226

Trying to query two tables and I can't figure out how

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)

+---------+--------------+------+-----+-------------------+----------------+
| 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 |                | 
+---------+--------------+------+-----+-------------------+----------------+


+----------+-----------+------+-----+-------------------+----------------+
| 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 |                | 
+----------+-----------+------+-----+-------------------+----------------+

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

<? 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:

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

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. Or should I use two separate queries ?

Upvotes: 1

Views: 135

Answers (3)

uzsolt
uzsolt

Reputation: 6027

I think your database design is wrong: you should create seperate users table with user-specific data (name, image,...) plus an user_id. And an another table with "general" information (as you said): name, desc, map, etc. And in this table doesn't use user-specific information only user_id.

And if your database isn't too large you can use a select tag with valid user_ids so you don't need validation.

EDIT if you want to know what are the user_ids which isn't in wait table, use similar query:

SELECT user.userid
FROM user
LEFT JOIN wait ON user.userid=wait.userid
WHERE ISNULL(wait.place_id)

These userid can put into a select-list.

Upvotes: 1

Ramses
Ramses

Reputation: 996

You could use a query like this one:

select * 
from wait_table 
left join general_info_table on wait_table.user_id = general_info_table.user_id
where wait_table.user_id = 1;

This way, IF the user_id is in the wait_table it would return you the info on the client... if it doesn't exists in the table, well, should return null.

I would filter out which table fields i really need from the query, though.

Upvotes: 0

Kurt Du Bois
Kurt Du Bois

Reputation: 7665

Please read up on joins in select queries. Looks like you need to use a left outer join between your master table and your temporary table: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php.

Upvotes: 1

Related Questions