Display one row from SQL database using PHP

Question

I have a db called 'members' and a table called 'users'. I want an id to be taken when a user signs up and then that id used to display just that one users details from the table rather than all rows in the table. This is my sign up code:

    <?php
    $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());
    }

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

    mysql_close($con)
    ?>

And this is my display table code:

    <?php

    // Create connection
    $conn = new mysqli("localhost", "root", "", "members");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT name, email, address, dob FROM users";
    $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> id-";
        // 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: 80; left: 560; z-index: 2; border-spacing: 0px;}</style> ";
    }
    $conn->close();
    ?>

Don't mix mysql_* functions with mysqli functions. Stick to mysqli. The mysql PHP extension is dead. — axiac
– axiac, Commented Jul 19, 2016 at 10:02

BeetleJuice · Accepted Answer · 2016-07-19 10:16:54Z

1

At the very beginning of each page, start a session:

session_start();

This will allow you to save and retrieve data about the user's browsing session across all requests between the browser and the server.

When you create a new account, capture the new ID. I assume here that your users table has a primary id.

$sql="INSERT INTO users ...";

if (!mysql_query($sql,$con)) die('Error: ' . mysql_error());

//Must have called session_start() already. This data
//will be accessible from other pages
$_SESSION['userID'] = mysql_insert_id(); 

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

On the other page (the one that displays the user's details), you retrieve the user's id and use it to fetch just that user's record:

session_start();
...
//TODO: make sure the user is signed in before you proceed!

//Retrieve the user's id from storage
$userID = $_SESSION['userID'];
...

//this assumes that the primary id column is named id.  Adjust if needed
$sql = "SELECT name, email, address, dob FROM users WHERE id=$userID"
...

The result of this query should have just 1 row. You can use it to populate your HTML

Additional notes:

As others have told you, consider moving away from mysql_ functions. They have been removed from the newest PHP so they're dead code.
Most definitely learn about prepared statements. It's terribly bad security to insert user-supplied values directly into your SQL queries (see your INSERT query). You're open to SQL injection, and your DB can be taken over easily.

edited Jul 19, 2016 at 10:16

answered Jul 19, 2016 at 10:10

BeetleJuice

41.1k24 gold badges120 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sam Hoinville Over a year ago

thanks @BeetleJuice, I think I am on the right track but can't quite figure it out. anyway i can directly contact you if you are happy to help me out with the finishing touches?

BeetleJuice Over a year ago

If you're having problems solving your original question (how to select and display just 1 row from the DB on a new page after a user signs up) then just ask for clarification here. If you have problems beyond that (eg: errors when converting mysql to mysqli or when trying to use prepared statements), open a new question. And you're welcome. If my answer leads you to a solution for the initial problem, be sure to select it. Happy coding.

Sam Hoinville Over a year ago

I've added all the code that you gave me and it is just displaying the 'else' content, it seems to be fetching something that doesn't exist. My 'users' table has a primary id, but it still isn't working

Bubai Das · Accepted Answer · 2016-07-19 10:06:38Z

0

is there any column name like ID in 'users' table? if not then create a column with name 'ID' at first of all columns and set to auto increment.

then run loop in your query and get id

answered Jul 19, 2016 at 10:06

Bubai Das

589 bronze badges

Collectives™ on Stack Overflow

Display one row from SQL database using PHP

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related