Combining sql queries in PHP

Question

Hello currently working with this code

   $qry_display = "
SELECT 
    student_id,
    fname,
    sex,
    lname,
    mname,
    level,
    photo,
    birth_date,
    birth_place,
    address,
    father,
    father_occupation,
    father_phone,
    father_company,
    father_degree,
    mother,
    mother_occupation,
    mother_phone,
    mother_company,
    mother_degree,
    adviser_id 
from 
    tbl_enroll 
where 
    student_id='$id' 
    AND level='$lvl'

";
    $sql_display = mysql_query($qry_display) or die (mysql_error());

The above code fetches most data from tbl_enroll. Now I want to acquire some data on tbl_er. tbl_enroll and tbl_er are connected with the primary key of student_id , also connect once to tbl_section. tbl_er and tbl_section are connected with the foreign key of section_id.

So far I was thinking of doing multiple sql queries and use one mysql_query trigger but it wont work because the trigger wont work with three sql queries.

Two things: 1. if student_id is the pk in all 3 tables, you can use join statements (assuming, of course, that the data you want from table_er relates to data from those students you've already got from the enroll table). 2. I hope you're protecting against SQL injection... — ChrisW
– ChrisW, Commented Sep 5, 2012 at 11:46
Yes tbl_er relates to tbl_enroll, Yes i will be using mysql_real_escape_string(). once i learn mysqli ill move on to prepared statements. @LaGrandMere my bad edited. — Anton
– Anton, Commented Sep 5, 2012 at 11:50
Ok then, many answers fill your wishes amongst all of them, add level = '$lvl' in mine if you use it :) — LaGrandMere
– LaGrandMere, Commented Sep 5, 2012 at 11:52

Mahmoud Gamal · Accepted Answer · 2012-09-05 11:47:28Z

8

Just JOIN the three tables:

SELECT t1.*, t2.*, t3.*
from tbl_enroll t1
JOIN tbl_el t2 ON t1.student_id = t2.student_id
JOIN tbl_section t3 ON t2.section_id = t3.section_id
where student_id='$id' AND a.level='$lvl'

answered Sep 5, 2012 at 11:47

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Comments

Jens · Accepted Answer · 2012-09-05 11:44:43Z

6

You should join the tables. This isn't about php but about sql.

answered Sep 5, 2012 at 11:44

Jens

2,0751 gold badge14 silver badges30 bronze badges

Comments

Suresh kumar · Accepted Answer · 2012-09-05 11:49:56Z

2

you can try by following systax

select tb1.filds,tb2.fields ... from table1 as tb1, table2 as tb2 .. where tb1.id = tb2.id and tb2.id  = tb3.id ...

In where condition have to check correctly.

else you can use Join Query .. .Join Query is better on

answered Sep 5, 2012 at 11:49

Suresh kumar

2,1121 gold badge19 silver badges28 bronze badges

Comments

LaGrandMere · Accepted Answer · 2012-09-05 11:53:46Z

2

   SELECT te.XXX,tr.YYYY,ts.ZZZ, ..... 
   from tbl_enroll te 
   inner join tbl_er tr on tr.student_id = te.student_id
   inner join tbl_section ts on ts.section_id = te.section_id
   where student_id='$id' and level='$lvl';

You select any field from te, tr or ts at the beginning of your query. Don't forget to prefix with te, tr or ts the fields so that you don't have ambiguous references when executing the query ;)

edited Sep 5, 2012 at 11:53

answered Sep 5, 2012 at 11:47

LaGrandMere

10.4k1 gold badge36 silver badges42 bronze badges

Comments

Basith · Accepted Answer · 2012-09-05 11:47:10Z

1

Combining two queries..

SELECT t1.* FROM tbl_er as t1, tbl_enroll as t2
WHERE t1.student_id= t2.student_id ORDER BY id DESC

You have to define the student id in both tables..

answered Sep 5, 2012 at 11:47

Basith

1,0757 silver badges22 bronze badges

Collectives™ on Stack Overflow

Combining sql queries in PHP

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related