using JOIN in mysqli only return result from one table

Question

I'm currently trying to print the result of joining 2 tables using mysqli. but everytime i try to do it, it only return the result from one table while the table that it's joining doesn't return any result

below is the code that show the data into a table

    foreach($db->showdata() as $x) {
         
          if($x['role_id'] == 1 || $x['role_id'] == 2) continue;
          $id = $x['user_id'];
          echo "<tr>";
          echo "<td>" . $x['user_id'] . "</td>";
          echo "<td>" . $x['first_name'] ." ". $x['last_name']. "</td>";
          echo "<td>" . $x['nilai_tugas'] . "</td>";
          echo "<td>" . $x['nilai_uts'] . "</td>";
          echo "<td>" . $x['nilai_uas'] . "</td>";
          echo "</tr>";
                    }

while this one is the showdata() function it called

function showdata(){
$data = mysqli_query($this->db, "SELECT * FROM user LEFT OUTER JOIN grade ON(user.user_id = grade.user_id) IS NOT NULL;");
while($d = mysqli_fetch_array($data)){
  $hasil[] = $d;
}
return $hasil;
}

The structure of the table is

User: user_id, first_name, last_name and other column that's irrelevant for this part

Grade: user_id, nilai_tugas, nilai_uts, nilai_uas

when I run the code it return

Notice: Undefined index: nilai_tugas Notice: Undefined index: nilai_uts Notice: Undefined index: nilai_uas

which is from the grade table, but the data from the user table do go through and show up in the page. The page result

When I run the query in mysql it does return all the data I requested but it doesn't seem to return the data when in the function.

Thank you in advance.

Edit: I tried to move the select query into the same page and it work, it just don't work when I use return from function(which is mandatory).

Martijn van der Bruggen · Answer 1 · 2020-03-15T12:16:39.943

0

Can you try be more explicit in selecting your columns?

mysqli_query($this->db, "SELECT user.columnname, grade.columnname FROM user LEFT OUTER JOIN grade ON(user.user_id = grade.user_id) IS NOT NULL;");

And are you sure the column in users is called user_id? Because the result set has the columnname studentID.

edited Mar 15 '20 at 12:16

answered Mar 15 '20 at 11:07

Martijn van der Bruggen

121
2
11

I added grade but nothing change – Saunts Mar 15 '20 at 11:09
do you get a result if you leave of the IS NOT NULL part? – Martijn van der Bruggen Mar 15 '20 at 11:24
It still show the same result as before even if IS NOT NULL is there or not. – Saunts Mar 15 '20 at 11:27
SELECT users.*, grades.* FROM users LEFT JOIN grades ON users.id = grades.user_id ORDER BY user.user_name; I don't think you need the parenthesis after the ON clause and a left join instead of left outer join. See this example: https://www.w3schools.com/sql/sql_join_left.asp – Martijn van der Bruggen Mar 15 '20 at 11:32
tried that too, still doesn't work at all. I'm starting to lean towards moving the code to the PHP file that show the data directly instead of using a function even though it may be a security issue(and spaghetti) – Saunts Mar 15 '20 at 11:40
Interesting, i'm out of options too, sorry i can't help. Btw forget the outer join remark. Apparently that's perfectly fine: https://dev.mysql.com/doc/refman/5.7/en/join.html – Martijn van der Bruggen Mar 15 '20 at 11:46
Are you sure you column in the users table is called user_id and not studentID? Because your result set says studentID – Martijn van der Bruggen Mar 15 '20 at 12:06
@MartijnvanderBruggen It's not a result set, it's html. Also, if that column name was wrong, it would produce a notice just like the others. – El_Vanja Mar 15 '20 at 13:34

score 0 · Answer 2 · answered Mar 16 '20 at 05:33

0

I don't fully know what happened, so I tried reinstalling xampp and it finally start working, the problem might be located in mysql config file(not the first time this happen to me).

answered Mar 16 '20 at 05:33

Saunts

11
1

using JOIN in mysqli only return result from one table

2 Answers2