sql query cannot read from another table (mysql table realations)

Question

My database is called: students

MY students table is: students with columns:

STUDENT_ID , STUDENT NAME, ETC.

My absences table is: absences with columns:

ABSENCE_ID, STUDENT_ID, ETC.

it should count how many absence records per student i have taking in consideration the students_id and to show the students_id in the table for example:

+------------+-----------+
| STUDENT ID | ABSENCES  |
+------------+-----------+
| 1          | 3         |
| 2          | 8         |
| 3          | 437       |
+------------+-----------+

NOTE: THE STUDENT_ID MUST BE READ FROM STUDENTS TABLE NOT FROM ABSENCES TABLE THIS IS THE PROBLEM!!!!

THESE ARE MY TWO QUERIES

$result = mysql_query("SELECT student_id, COUNT(*) AS count FROM absences GROUP BY student_id ORDER BY count DESC;");
$result2 = mysql_query("SELECT students.student_id, absences.student_id FROM students INNER JOIN absences ON students.student_id = absences.student_id");

The first query is working fine (It counts the records on table and tells me how many absences are)

The second query is not working, i want this query to work and to make ONE QUERY for both

My php code looks like this:

while($row = mysql_fetch_array($result))
{
    echo "<tr>";
    echo "<td><font size=\"4\" color=\"white\">" . $row['student_id'] . "</font></td>";
    echo "<td><font size=\"4\" color=\"white\">" . $row['count'] . "</font></td>";
    echo "</tr>";
}

The second query doesn't make sense to me. What do you want it to accomplish? The two ids it selects will always be the same... — Mark Miller, May 14 '14 at 01:18
the mysql_fetch_array that i have put there applies only for the first query. second query would be the same with result2 and row2 instead i posted it as an example only — Diell Abazi, May 14 '14 at 01:21
@DiellAbazi Not only would put the `GROUP BY`? Like this: `SELECT students.student_id, count(*) as ABSENCES FROM students s INNER JOIN absences a ON s.student_id = a.student_id GROUP BY a.student_id` — Lucas Henrique, May 14 '14 at 01:28
@LucasHenrique when i do something like that it says: `Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\at\retrieve.php on line 41` should i use foreach instead of while ??? — Diell Abazi, May 14 '14 at 01:34
@MarkM The point of second query is to read the students ID from another table and retrieve the absences of each student with their student_id — Diell Abazi, May 14 '14 at 01:35
@DiellAbazi See that values return `$row['student_id']` and `$row['ABSENCES']`. You can use while. — Lucas Henrique, May 14 '14 at 01:37

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

You can use this single query to accomplish your task:

SELECT 
    s.student_id,
    COUNT(a.student_id) as count
FROM students s
LEFT JOIN absences a ON a.student_id = s.student_id
GROUP BY a.student_id
ORDER BY count DESC

This will give you a list of all student IDs and the total absences for each. No need to run two queries. If you need additional data about the student, just add it to the list of fields under SELECT: s.student_name, s.student_age, etc...

See it in action here: SQL Fiddle

And, ya, don't use mysql_*

edited May 23 '17 at 12:05

Community

1
1

answered May 14 '14 at 01:56

Mark Miller

7,442
2
16
22

MARK YOU SAVED MY LIFE , IF I COULD GIVE 1MILLION REPUTATION TO U RIGHT NOW I WOULD DO IT!!!! THANKS MATE – Diell Abazi May 14 '14 at 02:18

score 0 · Answer 2 · answered May 14 '14 at 01:49

Whether to return many absence records per student using the second query.

$result2 = mysql_query("SELECT students.student_id, count(absences.student_id) as absences FROM students INNER JOIN absences ON students.student_id = absences.student_id GROUP BY absences.student_id");

while($row = mysql_fetch_array($result2))
{
    echo "<tr>";
    echo "<td><font size=\"4\" color=\"white\">" . $row['student_id'] . "</font></td>";
    echo "<td><font size=\"4\" color=\"white\">" . $row['absences'] . "</font></td>";
    echo "</tr>";
}

However, the first query works the same way without INNER JOIN. Would only be acceptable if using the second query returns a field that exists in students table, such as studant_name, for example.

mysql_* functions is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used.

@jxmallett Im getting an error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\at\retrieve.php on line 48
where line 48 is `while ($row = mysql_fetch_assoc($result)) {....` — Diell Abazi, May 14 '14 at 02:08
@@Lucas Henrique it is not showing any value in the table i dont know what the problem is — Diell Abazi, May 14 '14 at 02:11

jxmallett · Answer 3 · 2014-05-14T02:56:17.610

I don't think there is any way to (efficiently) get all the information in one query.

// This will get student IDs and their total number of absences
$result = mysql_query("SELECT student_id, COUNT(absence_id) AS total_absences
    FROM absences
    GROUP BY student_id
    ORDER BY count DESC;") or die(mysql_error());

//This will get the details of each student and each absence.
//Add which ever fields you want.
$result2 = mysql_query("SELECT students.student_id, absences.absence_id
    FROM students, absences
    WHERE students.student_id = absences.student_id") or die(mysql_error());

To merge the two:

$totalAbsences = array();
while ($row = mysql_fetch_assoc($result)) {
    $totalAbsences[$row['student_id']] = $row['total_absences'];
}

while ($row = mysql_fetch_assoc($result2)) {
    $totalAbsencesForThisStudent = $totalAbsences[$row['student_id']];
    //Make your table here
}

Side note: You should really look into using mysqli or PDO as straight mysql is depreciated as of PHP5.5.

sql query cannot read from another table (mysql table realations)

3 Answers3