SQL Joins, select data from different tables

Question

SQL Joins, select data from different tables

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm having problems selecting data from different tables, I explain.

I have three tables and I occupy certain data of each of them:

usuarios AS u
u.id   u.usuario

proyecto_emp AS p
p.id_proyecto   p.us_id   p.rol

roles AS r
r.id   r.rol

Relationships are like this:

u.id = p.us_id
p.rol = r.id

I need to put the data u.usuario and r.rol within a table in html , that I already have it done but I put all the users and then I need to put as a parameter that only me of the users that are inside a specific project: $proyecto_id .

For that I want to place a WHERE p.id_proyecto = $proyecto_id but I do not know how to do it, I hope you have given me to understand and I appreciate the help.

Here is the code I'm trying to use:

$data = $conex->getAll("SELECT u.usuario, r.rol
    FROM Usuarios u WHERE 'id_proyecto = '$proyecto_id''
    LEFT JOIN proyecto_emp pe ON u.id = pe.us_id
    LEFT JOIN Roles r ON pe.rol = r.id");

PS: it's the first time I use Joins.

sql php join

asked by Paloma Alvarado 28.08.2017 в 23:53

source

2 answers

0

with this sql it will work for you

SELECT u.usuario, r.rol FROM Usuarios u LEFT JOIN proyecto_emp pe ON u.id = pe.us_id LEFT JOIN Roles r ON pe.rol = r.id WHERE pe.id_proyecto = $proyecto_id

Greetings.

answered by 29.08.2017 в 00:09

Save data in txt by going through a select How to refocus a Xamarin Forms label?

score 0 · Accepted Answer

When you use JOIN, also the WHERE goes to the end.

The query would be:

SELECT u.usuario, r.rol 
FROM Usuarios u 
LEFT JOIN proyecto_emp pe ON u.id = pe.us_id 
LEFT JOIN Roles r ON pe.rol = r.id 
WHERE pe.id_proyecto = $proyecto_id;

But, to avoid SQL Injection use prepared queries.

Be using name markers (see 1st example below):

SELECT u.usuario, r.rol 
FROM Usuarios u 
LEFT JOIN proyecto_emp pe ON u.id = pe.us_id 
LEFT JOIN Roles r ON pe.rol = r.id 
WHERE pe.id_proyecto = :id_proyecto;

Or using placeholders ? (see 2nd example below):

SELECT u.usuario, r.rol 
FROM Usuarios u 
LEFT JOIN proyecto_emp pe ON u.id = pe.us_id 
LEFT JOIN Roles r ON pe.rol = r.id 
WHERE pe.id_proyecto = ?;

And then you pass apart the value $proyecto_id .

Here are two working code examples that implement prepared queries:

1st example: using PDO
2nd example: using MySQLi