Matching table data to display results

Question

looking for help getting data from my tables.

So far I have 2 tables, one named: userlogin which contains log in information listed by 'user_id' my next table is called userinfo which at the moment just contains user name and address, again listed by 'user_id'

Currently I have a log in page, and a home page. The log in page uses the information from the userlogin table to verify that the user has an account and they have entered the correct information.

Now this is were it gets tricky, (for me at least) as on this homepage i'm trying to display the information from the userinfo table which relates to the user_id from the userlogin table.

Here is my loginpage code

<?php
// Use session variable on this page. This function must put on the top of page.
session_start();
$_SESSION['userName'] = 'Root';


////// Login Section.
$Login=$_POST['Login'];
if($Login){ // If clicked on Login button.
$username=$_POST['username'];
$password=$_POST['password']; // Encrypt password with md5() function.

// Connect database. 
//connect
        $con = mysql_connect("*****","*****","*****");
        if (!$con)
         {
        die('Could not connect: ' . mysql_error());
        }
        //datebase
        mysql_select_db("*****, $con);

// Check matching of username and password.
$result=mysql_query("select * from userlogin where username='$username' and password='$password'");
if(mysql_num_rows($result)!='0'){ // If match.
session_register("username"); // Craete session username.
header("location:home.php"); // Re-direct to main.php
exit;
}else{ // If not match.
$message="--- Incorrect Username or Password ---";
}

} // End Login authorize check.
?>

Here is the code for my homepage, which is were the information will be shown to the user, upon login.

    <?php
session_start();
if(isset($_SESSION['userName']))
?>

<?php
// Connect database. 
//connect
        $con = mysql_connect("****","*****","******");
        if (!$con)
         {
        die('Could not connect: ' . mysql_error());
        }
        //datebase
        mysql_select_db("*****", $con);
//select
        $user_id = $_GET['user_id'];
    $result = mysql_query("SELECT userinfo.user_id, userlogin.user_id 
                           FROM userinfo 
                           INNER JOIN userlogin 
                           ON userinfo.user_id=userlogin=user_id");

    while($row = mysql_fetch_array($result))

            { 

            echo $row['name'] . '</br>' ;
            echo $row['address'] . '</br>';

            }

?>

So far all i'm doing is displaying all the information from my userinfo table, and what I want is to limit this content to showing information that matches the user_id from the userlogin table to the user_id of the userinfo table and display the results.

[EDIT] I've edited my mysql in my homepage code, and now i'm displaying no results, and i'm still unsure how to make this work.

Thanks for the help in advance!

Also pls stop using mysql... And use PDO or MSQLI instead. – Naruto Jan 10 '14 at 15:10 — Naruto, Jan 10 '14 at 15:10
Man, you need to study inner join to join the table data – Lefsler Jan 10 '14 at 15:10 — Lefsler, Jan 10 '14 at 15:10
Sounds like you're looking for a `JOIN`. – gen_Eric Jan 10 '14 at 15:11 — gen_Eric, Jan 10 '14 at 15:11

Joran Den Houting · Answer 1 · 2014-01-10T15:37:06.543

0

$result = mysql_query("SELECT userinfo.name as name, userinfo.address as address, userlogin.username as username  FROM userinfo, userlogin WHERE id = 1");

As an example.

while($row = mysql_fetch_array($result))
{
    echo $row['name'];
    echo $row['username'];
    echo $row['address'];
}

edited Jan 10 '14 at 15:37

answered Jan 10 '14 at 15:11

Joran Den Houting

3,149
3
21
51

Thanks for the response but I'm still unsure how to display the data, i've edited my code in my original post – King Jan 10 '14 at 15:23
Edited my post, just use it as you where using it before – Joran Den Houting Jan 10 '14 at 15:37
I'm sorry, but still no luck, my homepage is just showing nothing :( – King Jan 10 '14 at 15:51

score 0 · Answer 2 · answered Jan 10 '14 at 15:12

0

you can JOIN these two tables on user_id, to get information from both tables.

answered Jan 10 '14 at 15:12

Muhammad Saud

356
3
13

score 0 · Answer 3 · edited May 23 '17 at 12:21

0

I would use the PHP PDO class for this.

http://uk1.php.net/pdo

As you're using the GET attribute, it could be easy for sql injection to occur if you don't use a prepared statement.

Take a look at How can I properly use a PDO object for a parameterized SELECT query

Use this as a guide, but use your ID column instead of name.

edited May 23 '17 at 12:21

Community

1
1

answered Jan 10 '14 at 15:13

tomsowerby

498
1
3
10

Matching table data to display results

3 Answers3