Select from 3 tables MySQL

Question

I need to put info from my database into an html .

The problem is that my query doesn't work.

My tables are:

1) Students(id,name,email) 2) Lessons (id,title) 3) Marks (student_id,lesson_id,mark)

I have several questions: 1) What is wrong? 2) Do I need an extra field Marks.id to make it PRIMARY KEY, or I need to group Marks.student_id and Marks.lesson_id into PRIMARY KEY?

I need my table to look like this

+-------+--------+--------+---------------+
| S.name| Math   |Biology |    email      |
+-------+--------+--------+--------+------+
| John  |  A     |  B     | john@smith.com| 
+-------+--------+--------+---------------+
| Emma  |  B     |  B     | emma@stone.com|
+-------+--------+--------+---------------+

This is my query

$strSQL = SELECT Students.name,Students.email,Lessons.title, Marks.mark 
  FROM Marks 
  LEFT JOIN Students ON Students.id = Marks.student_id 
  LEFT JOIN Lessons ON Lessons.id = Marks.lesson_id

This is php code

$result = mysql_query($strSQL);
while($row = mysql_fetch_array($result)){
   echo "<tr>
         <td>".$row['Students.name']."</td>
         <td>".$row['Marks.mark']."</td>
         <td>".$row['Students.email']."</td>
        </tr>";             
}

In what sense doesn't it work? Have you checked for errors? Please note the `mysql_*` functions are deprecated and will be removed in a future PHP release. — Jim, Jun 27 '14 at 14:44
It should be Lessons, not Lesson. According to your query. Here: $row['Lesson.id'] should be $row['Lessons.id']. You also need to add id in your select since you're trying to retrieve the information in your php code. — Exn, Jun 27 '14 at 14:44
you should get a error if you [enable error_reporting and display errors](http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) and posted real code, otherwise you should double-check your result with `print_r($row);`. — pce, Jun 27 '14 at 14:47
Browser sends me no errors, also I've added Lessons.id and Students.id into the query. — igrrik, Jun 27 '14 at 15:11

score 0 · Accepted Answer · edited Jun 27 '14 at 15:27

0

$query = "

 SELECT s.name
      , s.email
      , l.id
      , l.title
      , m.mark 
   FROM Marks m
   JOIN Students s
     ON s.id = m.student_id 
   JOIN Lessons l
     ON l.id = m.lesson_id;
 ";

 $result = mysql_query($query) or die(mysql_error()); // for testing only

 while($row = mysql_fetch_assoc($result)){
    echo "<tr>
          <td>".$row['name']."</td>
          <td>".$row['email']."</td>
          <td>".$row['id']."</td>              
          <td>".$row['title']."</td>
          <td>".$row['mark']."</td>
         </tr>";             
 }

Note: Now might be a good time to stop using a deprecated API.

edited Jun 27 '14 at 15:27

igrrik

467
1
6
16

answered Jun 27 '14 at 15:11

Strawberry

33,750
13
40
57

Failed to load resource: the server responded with a status of 500 (Internal Server Error) – igrrik Jun 27 '14 at 15:17
Oh! I've made a little change and it works: I wrote this " ".$row['name']." ... " Thank you very much! – igrrik Jun 27 '14 at 15:20
I'm sorry, can you help me with one more thing. I've made a mistake with my table: I don't want to have several rows for one person, so in my example instead of having two rows for John, I need one where will be Math and Biology and marks for these lessons. – igrrik Jun 27 '14 at 15:50
Add an addendum to your question showing the desired output. Also, I guess you can use curly brackets instead of that stop/start stuff (".) - so, `{$row['name']}` – Strawberry Jun 27 '14 at 16:02
I've changed a table view, now you can see how it should look. – igrrik Jun 27 '14 at 16:11
Do you know in advance how many different lessons there will be in the result? – Strawberry Jun 27 '14 at 16:14
Yes,it will be fixed value. I do not need it in the query – igrrik Jun 27 '14 at 17:55

Select from 3 tables MySQL

1 Answers1