Choose database table for logged in user

Asked

Viewed 91 times

1

I intend to choose only 1 table of a database, depending on the user who is logged in to the website. I want this because I’m making an account management page for website users. On this page I have a calendar (fullcalendar) where I will fetch the events to a table.

What I want to do is a table for each user, and get one query make me the select of the right table to show that user’s events only. My current code is selecting all events from one table only:

<?php

 $json = array();

 $requete = "SELECT * FROM eventos ORDER BY id";

 try {
 $bdd = new PDO('mysql:host=localhost;dbname=fullcalendar', 'root', '');
 } catch(Exception $e) {
 exit('Impossivel conectar à base de dados.');
 }

 $resultado = $bdd->query($requete) or die(print_r($bdd->errorInfo()));

 echo json_encode($resultado->fetchAll(PDO::FETCH_ASSOC));

?>
php mysql

  • You do not need to make a table for each user. You can make a table with a double key user_id and eventos_id. And one table will do for everyone.

  • I don’t know if I noticed: the goal is to insert a variable in each event (user_id) to distinguish who owns this event? And the eventos_id variable would be for what?

  • It wouldn’t be anything I’ve seen that you already have one id in the events table.

  • And the code would look something like $requete = "SELECT * FROM events Where user_id = $_SESSION['user_id'] . "'"; ?

  • Yes, there you would fetch the events of that user.

  • hm, I have been trying but my session is created with the username and not user_id. In this case instead of using user_id to distinguish owners from events, I can use username

  • If it is unique you can. But be careful that searching by username can take longer. Ideally you should add the id in the session. Or create an index for the username column in the user table.

  • Do you refer to this type of Index? I’ve never used this ( MYSQL usage only for 1 month), what is it for? http://i.imgur.com/asWPz3r.png

  • That’s right. Being Mysql or another Database engine the idea is the same.

Show 4 more comments

1 answer

2

You can solve this by having two tables, one for users and one for events

User table

| id         | nome        | ...
|------------|-------------|-----
| 1          | joaquim     |         
| 2          | albano      |      
| ...        | ...         |

Event table

| id         | user_id     | evento             | ...
|------------|-------------|--------------------|----
| 1          | 1           | evento 1 do user 1 | 
| 2          | 1           | evento 2 do user 1 !  
| ...        | ...         | ...                |

You can do that with a foreign key user_id is foreign key to the id user’s.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.