Display specific data from SQL database

Question

I have a database called 'members' with a table called 'users'. The users table has an 'id' column that auto-increments every time a new user is added. I want to fetch the id of the logged in user and display that singular row of data. This is my login code:

    <?php
    session_start();
    // Connect to server and select databse.
    mysql_connect("localhost", "root", "")or die("cannot connect"); 
    mysql_select_db("members")or die("cannot select DB");
    // username and password sent from form 
    $prepreemail=$_POST['email']; 
    $prepremypassword=$_POST['mypassword']; 
    $preemail = stripslashes($prepreemail);
    $premypassword = stripslashes($prepremypassword);
    $email = mysql_real_escape_string($preemail);
    $mypassword = mysql_real_escape_string($premypassword);
    $sql="SELECT * FROM users WHERE email='$email' and password='$mypassword'";
    $result=mysql_query($sql);


    // Mysql_num_row is counting table row
    $count=mysql_num_rows($result);
    // If result matched $myusername and $mypassword, table row must be 1 row
    if($count==1){
    // Register $myusername, $mypassword and redirect to file "login_success.php"
    header ("location:home.html");
    }
    else 
    {
    header ("location:sign_in.php");
    }
    ?>

This is my sign up code:

    <?php
    session_start();
    $con = mysql_connect("localhost","root","");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }

    mysql_select_db("members", $con);

    $sql="INSERT INTO users (name, email, address, dob, password)
    VALUES
    ('$_POST[name]','$_POST[email]','$_POST[address]','$_POST[dob]','$_POST[mypassword]')";

    if (!mysql_query($sql,$con)) die('Error: ' . mysql_error());
    $_SESSION['userID'] = "SELECT id from users";

    {
        header ("Location: sign_in.php");
        exit;
    }

    mysql_close($con)
    ?>

And this is my display user details code:

    <?php
    session_start();
    // Create connection
    $conn = new mysqli("localhost", "root", "", "members");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $userID = $_SESSION['userID'];
    $sql = "SELECT name, email, address, dob FROM users WHERE `users`.`id`=$userID";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        echo "<table><tr><th>Name</th><th>Email</th><th>Address</th><th>DOB</th></tr>";
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td><td>".$row["address"]."</td><td>".$row["dob"]."</td></tr>";
        }
        echo "</table>";
    } else {
            echo "<p>No Details</p><style>p{color: #336699; font-size: 20pt; font-family: Gulim; font-weight: bold; align: center; padding: 10px; position: absolute; top: 250; left: 600; z-index: 2; border-spacing: 0px;}</style> ";
    }
    $conn->close();
    ?>

For some reason it is not working, someone please help!

the signup chunk on the insert is classic 2nd level sql injection — Drew, Jul 21 '16 at 05:07
the `$_SESSION['userID'] = "SELECT id from users"; ` ... huh ? — Drew, Jul 21 '16 at 05:08
Please do yourself (and your customer) a favor and throw out this very outdated code. `mysql_*` functions have been deprecated since 2012 and are currently not even a part of PHP anymore. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) Your code is also very prone to SQL injection attacks. Using stripslashes is not a proper way to sanitize input. Also see [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Oldskool, Jul 21 '16 at 07:59

score 0 · Answer 1 · edited Jul 21 '16 at 08:01

Actually your code has several mistakes... and not clear your questions... I think some changes needs for your questions and code.Please set session variable while fetching information from database.

<?php
session_start();
// Connect to server and select databse.
mysql_connect("localhost", "root", "")or die("cannot connect"); 
mysql_select_db("members")or die("cannot select DB");
// username and password sent from form 
$prepreemail=$_POST['email']; 
$prepremypassword=$_POST['mypassword']; 
$preemail = stripslashes($prepreemail);
$premypassword = stripslashes($prepremypassword);
$email = mysql_real_escape_string($preemail);
$mypassword = mysql_real_escape_string($premypassword);
$sql="SELECT * FROM users WHERE email='$email' and password='$mypassword'";
$result=mysql_query($sql);
 $count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
    $row=mysql_fetch_array($result);
    $_SESSION['id']=$row['id'];
    if($_SESSION['id'])
    {
header ("location:home.php");
    }
}
else 
{
header ("location:sign_in.php");
}
?>

In this logged person's id is stored in $_SESSION['id']. You can get this value in your PHP page home.php as below

<?php
session_start();
$logid=$_SESSION['id'];
echo $logid;

Please make some changes in your display user details code as below.

<?php
session_start();
// Create connection
 $conn = new mysqli("localhost", "root", "", "members");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$userID = $_SESSION['id'];
$sql = "SELECT name, email, address, dob FROM users WHERE `users`.`id`=$userID";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>Name</th><th>Email</th><th>Address</th><th>DOB</th></tr>";
    // output data of each row
while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td><td>".$row["address"]."</td><td>".$row["dob"]."</td></tr>";
    }

 echo "</table>";
} else {
 echo "<p>No Details</p><style>p{color: #336699; font-size: 20pt; font-family: Gulim; font-weight: bold; align: center; padding: 10px; position: absolute; top: 250; left: 600; z-index: 2; border-spacing: 0px;}</style> ";
}
$conn->close();

Please mark and up-vote the answer if it worked for you.Thanks — Aswathy, Aug 24 '16 at 07:39

Display specific data from SQL database

1 Answers1