Select data from a table related to a user

Question

Select data from a table related to a user

Navigation

#1 by (3 votes)

0

I have these two tables:

users:
+-----+----------+------------+
| id  |  nombre  |  apellidos |
+-----+----------+------------+
|   1 | juan     | apellido1  |
|   2 | pepito   | apeliido2  |
+-----+----------+------------+

subjects:
+----+--------------+----------+---------+
| id |  asignatura  | profesor | user_id |
+----+--------------+----------+---------+
|  1 | literatura   | ana      |       1 |
|  2 | historia     | alberto  |       1 |
|  3 | matemáticas  | esther   |       2 |
|  4 | inglés       | sergio   |       2 |
+----+--------------+----------+---------+

Well, what I want is for PHP to return me in the case of juan = > literature and history and in the case of pepito = > Math and English .

What I have so far is this:

<?php

session_start();

require('gdb.php');

$user_id = $_SESSION["userid"];

$data = $conn->prepare('
        SELECT subjects.subject_name
        FROM users
        INNER JOIN subjects ON :userid = subjects.user_id
');

$data->execute(array(':userid' => $user_id));

$result = $data->fetchAll();

echo json_encode($result);
?>

The gdb.php file simply contains the data of the connection to the database.

Well, using that code returns me more than repeated values.

The link I have between my tables is as follows:

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| subjects   | user_id     | subjects_ibfk_1 | users                 | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+

Thanks for any help

sql php pdo

asked by lromeraj 03.06.2017 в 19:02

source

1 answer

Update a record if it does not exist or if Read a .txt file and match it to a string

score 3 · Accepted Answer

What you have to use in your query is a JOIN

It would be something like this:

$data = $conn->prepare('SELECT * FROM subjects AS SU 
INNER JOIN users AS US ON US.id = SU.user_id
WHERE US.id = :id');

$data->execute(array(':id' => $user_id));

I think where I was failing you is when doing the join.