PHP, MySQL: Loading data through 3 tables

Question

I have these 3 tables:

fields

- id
- field

students

- id
- name
- birthday
- location
- ...

student_fields

- id
- student_id
- field_id

I would like to load from database all students according to a condition with all them fields from the table student_fields, but how to do that? Until now, I have a simple loading students:

//$s = "SELECT * FROM students JOIN WHERE name LIKE '%$search%'";
//$students = mysql_query($s);

EDIT: example: table fields contains:

id | field
1  | Citizenship
2  | Geography
3  | History
4  | Languages
5  | Literacy
6  | Music
7  | Numeracy

table student_fields for the respective students contains:

id | student_id | field_id
1  | 1          | 6
2  | 1          | 7 
3  | 1          | 3
4  | 2          | 7

The goal: for the student with ID 1 I wanna print out his fiels, in this case Music, Numeracy, History.

Thank you guys

Invalid query you have provided. Where is your real code? What are you trying to achieve? Need more clarification. — codingbiz, Jan 05 '13 at 12:56
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://stackoverflow.com/a/14110189/1723893). — NullPoiиteя, Jan 05 '13 at 12:57
This sounds like an interesting query but.. could you be more specific about what you really want to achieve? You can write down desired output. Fore ie, you want location to be part of a query (with name) too or similar? — Xfile, Jan 05 '13 at 12:58
I am trying to get all students based on the `LIKE` condition + I want to get skills of each student. Thanks for the note about `mysql_`, didn't know it, didn't work with PHP for a long time... — user984621, Jan 05 '13 at 13:00

score 0 · Answer 1 · answered Jan 05 '13 at 13:06

The Query You are Writing is invalid. To select from a Table (using a condition), the SQL should be like:

SELECT FROM students WHERE id > 10 AND age > 18

Also, I Would Recommend You To Move to PDO or Mysqli statements, because as of PHP 5.5, PHP is depcrecating the mysql functions.

echo_Me · Accepted Answer · 2013-01-05T14:05:14.870

0

your query doesnt meet your question and u didnt provide more information however you can try this query

   SELECT * FROM fields f 
   INNER JOIN student_fields sf
   ON sf.field_id = f.id 
   INNER JOIN students s
   ON  s.id = sf.student_id
   WHERE s.name LIKE '%$search%'

EDIT or you can use group_concat

like that

SELECT s.id ,s.name ,s.birthday,s.birthday ,s.location,group_concat(f.field) field 
FROM fields f 
INNER JOIN student_fields sf
ON sf.field_id = f.id 
INNER JOIN students s
ON  s.id = sf.student_id
WHERE s.name LIKE '%$search%'

DEMO SQL FIDDLE

edited Jan 05 '13 at 14:05

answered Jan 05 '13 at 13:14

echo_Me

37,078
5
58
78

Thank you for your response goodmood, but this is not unfortunately the desired output. Please, check out the OP, I tried to clarify the output. Thank you. – user984621 Jan 05 '13 at 13:32
so u dont need `LIKE '%$search%'` ?? – echo_Me Jan 05 '13 at 13:35
The clausule `LIKE '%$search%'` I need, because I wanna filter for example all users, which have `Music ` in their fields. Then I need print out all these users + all their other fields. – user984621 Jan 05 '13 at 13:39

PHP, MySQL: Loading data through 3 tables

2 Answers2