Display data for logged in user with same id from different table

Question

I'm self-learning, so pardon my ignorance.

I have 2 SQL tables: user and product, both tables contain "user_id" fields.

I have successfully created a login system that uses email and password fields from the first table (user).

I want to show specific information from the "product" table to the logged-in user. This information should be identified from the user_id field. (user with user_id of 1 should see the product with user_id of 1

login page has:

<?php

session_start();

$message = "";
require_once('account/pdoconnect.php');

    if(isset($_POST["login"])) {
    if (empty($_POST["email"]) || empty($_POST["password"])) {
        $message = '<label>All fields are required</label>';
    }
    else {
        $query = "SELECT * FROM user WHERE email = :email AND password = :password";
        $statement = $pdotestconn->prepare($query);
        $statement->execute(
            array(
                'email' => $_POST['email'],
                'password' => $_POST['password']
    )
        );
        $count = $statement->rowCount();
        if($count > 0) {
            $_SESSION["email"] = $_POST["email"];
            header("location:account/index.php");
        }
        else {
            $message = '<label>Wrong Email or Password</label>';
        }
    }
}
?>

Index page has:

<?php session_start();
  if(!isset($_SESSION["email"]))
   {
    header("location:../login.php");
    exit;
   }
?>

<?php
 include("pdoconnect.php");

 $id = $_GET['user_id'];
 $result = $pdotestconn->prepare("SELECT * FROM product inner join user on 
 user.user_id = product.user_id");
 $result->execute(array($id));
 $row = $result->fetch(PDO::FETCH_ASSOC);

?>

Where I insert values with:

<?php
 echo $row['amount'];
?>

Problem: I get the same value in the first row (with user_id = 2) for every user logged in

Probably you need to include WHERE clause in your second SQL statement `SELECT * FROM product inner join user on user.user_id = product.user_id WHERE user.user_id = :id`. — Zhorov, Sep 09 '19 at 06:38
I would also suggest storing the user id in the session rather than fetching it from the request. (BTW you don't need to join to the user table if the user_id is in the product table). — Nigel Ren, Sep 09 '19 at 06:41
@NigelRen can you help me with the code correction to store session in user_id — DevChiJay, Sep 09 '19 at 06:56
Don't store plaintext passwords in database. Use `password_hash()`! — Dharman, Sep 09 '19 at 07:17
I saw this: `'password' => $_POST['password']` and got the chills => please read https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Salketer, Sep 09 '19 at 07:19

Ed Bangga · Answer 1 · 2019-09-09T06:59:51.580

0

You don't have any parameter on your query.

<?php
     include("pdoconnect.php");

     $id = $_GET['user_id'];
     $result = $pdotestconn->prepare("SELECT * FROM product inner join user on 
     user.user_id = product.user_id WHERE product.user_id =:id");   
     $result ->bindParam(':id', $id, PDO::PARAM_INT);          
     $result ->execute();
     $row = $result->fetch(PDO::FETCH_ASSOC);

?>

edited Sep 09 '19 at 06:59

answered Sep 09 '19 at 06:41

Ed Bangga

12,879
4
16
30

I can't seem to get any output with this correction, all outputs are empty @metal – DevChiJay Sep 09 '19 at 06:54

score 0 · Accepted Answer · answered Sep 09 '19 at 07:00

0

First it's probably best to store the user id in the session, so in your first source...

    if($count > 0) {
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        $_SESSION["email"] = $_POST["email"];
        $_SESSION["userID"] = $row['ID'];  // Make sure ID is the column name from the user table
        header("location:account/index.php");
    }

then to display the data, fetch the user ID from the session...

 $id = $_SESSION["userID"];
 $result = $pdotestconn->prepare("SELECT * FROM product
             WHERE product.user_id =:id");             
 $result->execute(['id'=> $id]);
 $row = $result->fetch(PDO::FETCH_ASSOC);

BTW you don't need to join to the user table if the user_id is in the product table

answered Sep 09 '19 at 07:00

Nigel Ren

56,122
11
43
55

there's an error on this line: $result->execute(['id' => $id]); it says : parse error, expecting `')'' – DevChiJay Sep 09 '19 at 07:17
Try `$result->execute(array('id' => $id));` (Not sure what your error is though) – Nigel Ren Sep 09 '19 at 07:22
the rows come out empty, Note: the name of my column in user is user_id not ID and I modified your first code to: $_SESSION["userID"] = $row['user_id']; – DevChiJay Sep 09 '19 at 07:36
Have you tried using `var_dump($row);` after the code above to check if the data is being retrieved. Also there is nothing in your code which displays data, so not sure what code you are using. – Nigel Ren Sep 09 '19 at 07:37
False would indicate there isn't any data to fetch. Check what `$id` has and make sure it is the users ID. – Nigel Ren Sep 09 '19 at 07:47
it's HTML, I'm getting frustrated here – DevChiJay Sep 09 '19 at 07:47
it worked, I'm so sorry, I didn't add the second line in your first code: $row = $statement->fetch(PDO::FETCH_ASSOC); thanks – DevChiJay Sep 09 '19 at 07:51

Display data for logged in user with same id from different table

2 Answers2