MYSQL PHP query to get data from one table to another

Question

I need to filter data based on one table, but i dont know the correct usage for the select from function in php mysql. The below code shows what i am trying to do, but I think you can use multiple SELECT.

$query_Student = "SELECT Project_Title, Project_Lecturer FROM projects WHERE Project_id = 
 (SELECT Proj_id FROM project_course WHERE Cour_id = (SELECT Course_id from courses WHERE     
Code = (SELECT Course FROM users WHERE Username = ".$_SESSION['MM_Username']."))"

Can anyone offer any assistance to what I need to write instead?

The output is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Proj_id FROM project_course WHERE Cour_id = (SELECT Course_id from course' at line 1" — Nathan Link Abiola
– Nathan Link Abiola, Commented May 20, 2012 at 18:30
@NathanLinkAbiola Ignore what Hamza said about "union", that's not what you need. — fancyPants
– fancyPants, Commented May 20, 2012 at 21:13

fancyPants · Accepted Answer · 2012-05-20 21:08:29Z

2

What you want to do is not exactly clear to me, but to make your query work you need to put single quotes around $_SESSION['MM_Username'], like gogowitsch already said.

Also another closing paranthese is missing. So your query should look like this:

$query_Student = "SELECT Project_Title, Project_Lecturer FROM projects WHERE Project_id = 
 (SELECT Proj_id FROM project_course WHERE Cour_id = (SELECT Course_id from courses WHERE     
Code = (SELECT Course FROM users WHERE Username = '".$_SESSION['MM_Username']."')))"

But...

That is not a very well written query.

I'm just guessing now, but maybe this is what you are looking for:

SELECT 
p.Project_Title, 
p.Project_Lecturer 
FROM projects p
INNER JOIN project_course pc ON p.Project_id = pc.Proj_id
INNER JOIN courses c ON pc.Cour_id = c.Course_id
INNER JOIN users u ON c.Code = u.Course
WHERE u.Username = 'yourUserName';

If not, show us the tables you are using (do a SHOW CREATE TABLE projects; and so on for every table you are using and post it here) and maybe what output you expect, then we can really help.

answered May 20, 2012 at 21:08

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Link Abiola Over a year ago

That seems to work, however i realise that i require the forename and surname of the user linked to that project in the users table aswell. Would i create a new join query or can i add it onto the existing one.

fancyPants Over a year ago

@NathanLinkAbiola Is the fore- and surname in the table users? If yes, just add it in the SELECT clause. Like this: SELECT p.Project_Title, p.Project_Lecturer, u.forename, u.surname FROM projects p ...

Gogowitsch · Accepted Answer · 2012-05-20 20:37:46Z

0

You might need to add quotes around the username. Can $_SESSION['MM_Username'] be a string?

answered May 20, 2012 at 20:37

Gogowitsch

1,2331 gold badge14 silver badges35 bronze badges

Collectives™ on Stack Overflow

MYSQL PHP query to get data from one table to another

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related