SQL multiple tables grouping students

Question

Trying to group students based on these which groups they are in. I'm trying to dispaly the result in some sort of object/JSON so that I could apply some sorting algorithims in JS later. Not sure how to add the WHERE statement or Join statement?

$link = mysqli_init();
$success = mysqli_real_connect( $link, $host, $user, $password, $db, $port);
$query = mysqli_query($link, "SELECT s_id, group_id, name FROM Group, Student");

    $table = array(); 
    while($row = mysqli_fetch_assoc($query)) 
    { 
    $table = $row;
     echo json_encode($table);
     }


I want result something like 
`
[{'Group': 1, 'name': 'john', 'ID': 101},
{'Group': 1, 'name': 'mike', 'ID': 103},
{'Group': 2, 'name': 'alice', 'ID': 102}, 
{'Group': 2, 'name': 'rachel', 'ID': 104},]`







the "Student" table

    s_id     class_id   name
    101         1         john
    103         1         mike
    102         1         alice
    104         1         rachel

the "Group" table


    class_id   s_id    group_id
    1          101         1
    1          102         2
    1          103         1
    1          104         2

Careful; `GROUP` is a mysql reserved word and you didn't escape it. — Funk Forty Niner, Nov 03 '18 at 23:22
Adding to above comment, simply dont use `Group` as a table/alias name etc. Use something else. Avoid any reserved keyword in MySQL. Check the full list here: https://dev.mysql.com/doc/refman/8.0/en/keywords.html — Madhur Bhaiya, Nov 04 '18 at 05:21

score 0 · Accepted Answer · edited Nov 04 '18 at 05:19

0

Change your SQL with:

SELECT G.group_id AS `Group`, 
       S.name, 
       S.s_id AS ID
FROM Student S
JOIN `Group` G ON S.s_id = G.s_id
ORDER BY G.group_id, S.s_id

It is an INNER JOIN between the 2 tables on the Student id column, and we choose the columns that we want to show, and order the result as we want.

edited Nov 04 '18 at 05:19

Madhur Bhaiya

28,155
10
49
57

answered Nov 03 '18 at 23:35

Ermac

1,181
1
8
12

I get this error : Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given – encrypt Nov 04 '18 at 00:02
Hi, that means that there is a syntax error in the SQL, could you call the code `echo "error message: " . mysqli_error($success);` after you call the `mysqli_query` function? – Ermac Nov 04 '18 at 00:11
Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given i – encrypt Nov 04 '18 at 00:16
Sorry not used to mysqli, I think `mysqli_error($link)` rather, this is for viewing the error returned by Mysql after executing the query with `mysqli_query`. – Ermac Nov 04 '18 at 00:22
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 ''Student' S, `Group` G WHERE S.s_id = G.s_id ORDER BY G.group_id, S.' – encrypt Nov 04 '18 at 00:31
Don't know where is the error, it actually execute in my Mysql installation, try to execute the request directly in Mysql. – Ermac Nov 04 '18 at 01:12
1

Note that it became unfashionable to write queries this way circa 1992 – Strawberry Nov 04 '18 at 01:45
Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 04 '18 at 04:56
@MadhurBhaiya so how would i re-write this sql statement – encrypt Nov 04 '18 at 05:16

SQL multiple tables grouping students

1 Answers1