Selecting from Multiple tables in same Query using PHP MySQL

Question

Kindly pardon me if i looks silly , I am newbie in PhP MySQL

Totally there are three tables

Student,Class,Parent

I am working on parent user now . As a parent i want to view some things as below

My Students studying

Their class

Student Table :

s_id   s_name    parent_id   s_class_id   

1       name1     40           20

Class Table :

c_id   c_name   

20      Ten

Parent Table :

p_id    p_name

40      Parent1

My 1st task as mentioned above "1. My Students studying " i done successfully with the following query

"SELECT * FROM student where parent_id='$update_id' "

then by displaying

$row['s_name']

For all who thinks where the $update id comes from , I already done it few codes above as follows

$update_id = $login_type['p_id'];

Where $login_type i am taking from session

Now i want to do my second task which is "2. Their class"

I don't want it to do as separate select query which i can do now, i want it to be in the same select above done . as i am just learning from scratch i am stuck over here . Kindly help me please mates.

I tried to explain the max as i can , if you still has any clarifications , kindly ask me .

SELECT s.*,c.c_name FROM student s INNER JOIN Class c ON(s.s_class_id = c.cid) where s.parent_id='$update_id' — sagi
– sagi, Commented Apr 6, 2016 at 10:39

Daniel Dudas · Accepted Answer · 2016-04-06 10:40:46Z

1

You should use join to get results from multiple tables. Your query should look similar to this:

SELECT * FROM student 
    JOIN class 
    ON class.c_id = student.s_class_id
    WHERE parent_id='$update_id'

You can read more about mysql join here: http://dev.mysql.com/doc/refman/5.7/en/join.html

edited Apr 6, 2016 at 10:40

answered Apr 6, 2016 at 10:38

Daniel Dudas

3,0123 gold badges29 silver badges41 bronze badges

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

4 Comments

sagi Over a year ago

Really? is this valid MySQL syntax?

Raja Gopal Over a year ago

@DanielDudas Thanks, This worked like a charm mate . By the way i am going through the join documentation you referred mate .

Raja Gopal Over a year ago

@sagi Is this not a valid MySQL syntax mate ? kindly help me please. I am asking because i am getting output perfectly with this syntax . But anyhow if you feel as this is not a valid syntax kindly update me with the fix in the syntax as i will learn properly. My intention is not the output but the proper syntax mate.

sagi Over a year ago

@RajaGopal He updated his question since, it wasn't formatted like this! You can press on the edited -x- mins ago to see edit history.

Adersh · Accepted Answer · 2016-04-06 10:48:24Z

1

SELECT * FROM student
INNER JOIN class ON class.c_id = student.s_class_id 
WHERE parent_id = '$update_id'

You can use mysql join query like this and can get the student's class name in

$row['c_name']

edited Apr 6, 2016 at 10:48

answered Apr 6, 2016 at 10:40

Adersh

5981 gold badge9 silver badges22 bronze badges

Comments

MarcM · Accepted Answer · 2016-04-06 11:01:02Z

1

In this single SELECT you can obtain all data from tables 'student' and 'class' related to the students with parent_id='$update_id' using JOIN.

SELECT * FROM student 
LEFT JOIN class ON s_class_id=c_id
WHERE parent_id='$update_id'

To access the data from, use same method you already use:

echo $row['s_name'];
echo $row['c_name'];
// ..and so on for other fields.

[Edited: added LEFT to join, so that you will get data from ALL students, including those not having any class assigned. With INNER JOIN (or JOIN 'only') you will miss rows from students with no class]

edited Apr 6, 2016 at 11:01

answered Apr 6, 2016 at 10:41

MarcM

2,2511 gold badge25 silver badges32 bronze badges

Collectives™ on Stack Overflow

Selecting from Multiple tables in same Query using PHP MySQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related