CODEWITHSUNDEEP
phpmysqlsession
syafiqq aziz
syafiqq aziz

Reputation: 94

How to fetch data from two tables based on session variable php?

Actually im trying to display data from 2 tables on database based on current session, basically, i had read many article and forum on how to solve this problem but i still facing them till now. so those are data and code that already i did:

<?php
include('db.php');

$sql="SELECT*
FROM users
INNER JOIN details ON users.id= details.id LIMIT 1 WHERE users.id ='$loggedin_id' " ;

$result=mysqli_query($db,$sql);
if (!$result) {
printf("Error: %s\n", mysqli_error($db));
exit();
}
?>

so here, i got this error: "Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE users.id = '16'' at line 3". so if i only use this query:

 $sql="SELECT* FROM users INNER JOIN details ON users.id= details.id"

it will display all data in my database, but i only need data from current session.

here is my session.php file:

<?php 
include('db.php');
session_start();
$user_check=$_SESSION['sess_username'];
$ses_sql=mysqli_query($db,"select username,id from users where username='$user_check' ");
$row=mysqli_fetch_array($ses_sql,MYSQLI_ASSOC);
$loggedin_session=$row['username'];
$loggedin_id=$row['id'];
$role = $_SESSION['sess_userrole'];
if(!isset($loggedin_session) && $role!="user"){
  header('Location: index.php?err=2');
}
?>

this is my tables detail: table users table details

hope someone can help me, im new with my sql and php. tq

Upvotes: 0

Views: 3088

Answers (3)

Christian Akpan
Christian Akpan

Reputation: 1

You can neglect the INNER JOIN and Use Procedural Steps as follows:

  • Step 1: Just Start Session in all your pages- session_start();
  • Step 2: In your Db create a column for Session e.g sessionCheck
  • Step 3: On the Sign-up page start session and use MySqli last insert function like:

$_session["anything"] = mysqli_insert_id(your db connection variable); then store this session Id to a variable (like - $sessionCheck = $_session["anything"]; Insert the value of this variable to all the column in your Database on the session column as you perform insert. Let the session column by int and unique.

  • Step 4: On the Display Page use control structure specifically Nested IF instead of INNER JOIN.
  • STEP 5: ALL your Insertion insert the value of $_sessionCheck into sessionCheck in all your tables except the sign-up page (because the session id is originally picked from sign-up page so you don't need to insert into sign up again)
  • Step6: All your Select should be tie to WHERE sessionCheck = $sessionCheck

This works perfectly well for me on my Procedural PHP 7 (MySqli) and MySQL 5

Upvotes: -1

Abdur Rahim
Abdur Rahim

Reputation: 40

I think your query should be like below: 

$sql = "SELECT * FROM users
INNER JOIN details ON users.id= details.id  WHERE users.id = $loggedin_id ";

Upvotes: -1

Ajay Korat
Ajay Korat

Reputation: 701

Change your query like that.

$sql="SELECT*
FROM users
INNER JOIN details ON users.id= details.id WHERE users.id ='$loggedin_id' LIMIT 1" ;

Upvotes: 7

Related Questions