PHP MySQL Read Information Depending on Field/Variable

[banfw001]

Hi,

I am trying to make a website that stores and prints information entered by users. The site consists of one database that contains two tables. The first table stores all of the users details such as their username, password and address. The second is store users prescription items in. I have the login system completely working, and it shows all of the users details stored in the table once they have logged in. 

Now, I need the site to do more. Once the user has logged in, it want it to display all prescription items belonging to that user. The table consists of several fields, one of which is the users username. This means, when the user creates a new prescription item, it is sotred in the table and in one of the row cell's, their username is stored. I then want to be able to search for all records by that user and display them.

 

I have managed to create some code which gets the information from the table and displays it on the users console page, however it gets information from all users, not just their account. For example, if someone had a username of 'fruit', I want it to search the prescription table for all records with the username 'fruit' and display them.

Here is my code so far:

    <?php

    include_once "base.php";

    $result = mysql_query("SELECT * FROM prescriptions") or die(mysql_error());

    $list = '';

    while ($row = mysql_fetch_array($result)) {

    $user_id = $row["user_id"];

    $drug = $row["drug"];

    $strength = $row["strength"];

    $quantity = $row["quantity"];

    $list .= '' . $drug . ' ' . $strength . ' ' . $quantity . '<br/>'; 

    }

    ?>

 

I would appreciate any help on this problem. Thanks in advance

[Ch0cu3r]

To search a table for specific records you can use a WHERE clause. Example code (assuming you are saving the users username to a session variable when they login.)

<?php
    session_start(); // start session
    include_once "base.php";

    $username = mysql_real_escape_string($_SESSION['username']); // get users username from session
    // only get prescriptions that matches the username
    $result = mysql_query("SELECT * FROM prescriptions WHERE username='$username'") or die(mysql_error());
    $list = '';
    // make sure query returned any records
    if(mysql_num_rows($result))
    {
        while ($row = mysql_fetch_assoc($result))
        {
            $user_id = $row["user_id"];
            $drug = $row["drug"];
            $strength = $row["strength"];
            $quantity = $row["quantity"];
            $list .= '' . $drug . ' ' . $strength . ' ' . $quantity . '<br/>'; 
        }
    }
    // no records returned
    else
    {
        echo "No prescriptions for $username";
    }
?>

Edited November 4, 2013 by Ch0cu3r

[banfw001]

 

To search a table for specific records you can use a WHERE clause. Example code (assuming you are saving the users username to a session variable when they login.)

<?php
    session_start(); // start session
    include_once "base.php";

    $username = mysql_real_escape_string($_SESSION['username']); // get users username from session
    // only get prescriptions that matches the username
    $result = mysql_query("SELECT * FROM prescriptions WHERE username='$username'") or die(mysql_error());
    $list = '';
    // make sure query returned any records
    if(mysql_num_rows($result))
    {
        while ($row = mysql_fetch_assoc($result))
        {
            $user_id = $row["user_id"];
            $drug = $row["drug"];
            $strength = $row["strength"];
            $quantity = $row["quantity"];
            $list .= '' . $drug . ' ' . $strength . ' ' . $quantity . '<br/>'; 
        }
    }
    // no records returned
    else
    {
        echo "No prescriptions for $username";
    }
?>

Thanks a lot for your fast response. This worked perfectly.