Home / Questions / 75630
Accepted
lromeraj
Asked: 2020-06-04 09:02:26 +0800 CST 2020-06-04 09:02:26 +0800 CST

Select data from a table related to a user

  • 772

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 => mathematics 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 for the connection to the database.

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

The link I have between my tables is the following:

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

thanks for any help

php

1 Answers

  1. Best Answer

    What you have to use in your query is aJOIN

    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 that where it was failing you is when doing the join.

    • 3