Inner join of two tables [UPDATED]

Question

tbl teacher

lrn
fname
lname
email
image
schedule

tbl user

lrn
fname
lname
email
image
account-type

$id=$_SESSION['id'];

$get_record_sched = mysqli_query ($db, "SELECT schedule FROM teacher INNER JOIN users ON teacher.lrn = users.lrn WHERE teacher.lrn = '$id' ");

I want to echo the exact schedule based on the id of the logged in account. But with this, query, there's no output...

`mysqli_error()` requires the connection as the parameter. Do you also fetch the results from this query? — Qirel, Mar 16 '19 at 15:34
Neither of your tables seem to contain ID column. Also, why joining at all if the SCHEDULE column is already present in the TEACHER table? — Demo, Mar 16 '19 at 15:38
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Mohammadreza Khatami, Mar 16 '19 at 15:39
here's my new query, i can now echo all the schedule but not the exact schedule for id $get_record_sched = mysqli_query ($db, "SELECT schedule FROM teacher INNER JOIN users ON teacher.lrn = users.lrn WHERE teacher.lrn = users.lrn"); — anne, Mar 16 '19 at 15:40
@anne you repeat the joining condition in the WHERE part which is not neccessary. Instead, you probably want to place the id provided by the user user there in form of a parameter: WHERE users.lrn = $idFromUser — Demo, Mar 16 '19 at 15:44

score 1 · Answer 1 · edited Mar 16 '19 at 16:01

1

You said you have $id from the session. Assuming that column lrn is the ID, then

Select t.schedule
From teacher t
Where t.lrn = $id

No need to join the table with the user table.

Note that parameter binding is advised.

$statement = $connection->prepare("Select t.schedule From teacher t Where t.lrn = ?");
$statement->bind_param("s", $id);

edited Mar 16 '19 at 16:01

Pinke Helga

answered Mar 16 '19 at 15:57

Demo

This answer breaks it down to the point! Further more prepared statement is used which generally should be common practice. – Pinke Helga Mar 16 '19 at 16:06

prasanth · Answer 2 · 2019-03-16T15:45:04.523

0

In you where condition was wrong.They don't have any param to validate condition.so change like this

"SELECT schedule FROM teacher INNER JOIN users  ON teacher.id = users.id WHERE 1  "

OR

use same where condition with join

"SELECT schedule FROM teacher INNER JOIN users  ON teacher.id = users.id AND teacher.schedule = users.id   WHERE 1  "

OR

if You have logged used id in session like

$id=$_SESSION['user_id'];

"SELECT schedule FROM teacher INNER JOIN users ON teacher.id = users.id WHERE teacher.schedule='$id' "

edited Mar 16 '19 at 15:45

answered Mar 16 '19 at 15:40

prasanth

you are right .if i formatted my code .They answer was go very log.Its not necessary now – prasanth Mar 16 '19 at 15:47
the last code is not workin, i have $id=$_SESSION['id']; and i tried what you said, but it's still not showing the exact schedule – anne Mar 16 '19 at 15:49
are you call `session_start()` on before `$id=$_SESSION['id']` – prasanth Mar 16 '19 at 15:50
session_start(); if(isset($_SESSION["id"])) { $id = $_SESSION["id"]; – anne Mar 16 '19 at 15:52
ok do one thing `echo "SELECT schedule FROM teacher INNER JOIN users ON teacher.id = users.id WHERE teacher.schedule='$id' "; exit();` copy paste to with phpmyadmin.And tell if have any error – prasanth Mar 16 '19 at 15:54
i got the exact account when i logged in but i can't echo the exact schedule for that account and the schedule column is on table teacher – anne Mar 16 '19 at 15:55
can you post the two table screen shot .And tell one example matching result? – prasanth Mar 16 '19 at 15:59
the two table have the same data except the schedule and account_type, i want want to echo the exact schedule when i logged in the account. – anne Mar 16 '19 at 16:12

2 Answers2