User Login, but pull info as well....

[EricTRocks]

Hello,

I just wrote this simple script to login/out users. I also need to pull in information such as their first name, last name, team, etc.....

need to know how to query the db and set the variables for the additional information. The additional information is in a few different tables called ateam1 and ateam2 but these other tables contain the same user / pass fields as the users one.

I was trying to figure out a join but don't know how to join them and then also set the variables.

login / query script

<?php

if (isset($_POST['submit']))
{

require_once ('db_connect.php');
mysql_select_db("eric_teams");


$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
$result = mysql_query($query, $dbc);

if (mysql_num_rows($result) > 0)
{ 
session_start();
$_SESSION['user'] = "$user";
$_SESSION['pass'] = "$pass";

header ("Location: index.php");

}

else if (mysql_num_rows($result) < 1);
{
echo "You are not a member!";
}
}
else

{
?>

<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input name="user" type="text" size="8"> | <input name="pass" type="password" size="8">  <input type="submit" name="submit" value="Submit" />
</form>


<?

}
?>

[kburger]

First of all, I wouldn't be storing the username and password in any other table than your users table.

Secondly, I'd use whatever the primary key is for the users table to be the primary key for the ateam1 and ateam2 tables. That's what makes the tables related.

For example, I don't know what your table structure looks like, but I'd suggest something along the lines of

CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
user VARCHAR( 20 ) NOT NULL ,
pass VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( id )
);

Then, each of your ateam1 and ateam2 tables could be something like

CREATE TABLE ateam1 (
id INT UNSIGNED NOT NULL ,
whateverfielda VARCHAR( 20 ) ,
whateverfieldb VARCHAR( 20 ) ,
PRIMARY KEY ( id )
);

In your code, check that the user is logged in like you currently do, plus get that primary key. I'm referring to it below as $id. Then to join the 2 tables and get your data do something like this

$query = "SELECT 
ateam1.whateverfielda, 
ateam1.whateverfieldb, 
ateam2.whateverfieldc, 
ateam2.whateverfieldd 
FROM ateam1, ateam2 
WHERE ateam1.id='$id' 
AND ateam1.id=ateam2.id";

$result = mysql_query($query) or die("Query failed: " . mysql_error());
$row = mysql_fetch_array($result);

Your values will be in $row['whateverfielda'], $row['whateverfieldb'], etc.

Hope that helps. Let me know if you've got questions.