PHP and mysql questions; retrieving information for different users.

[garrette]

Okay, my title may seem confusing but here is what I want:

I am trying to make it so that I can retrieve information, depending on which user is logged in. I have a page that when a user looks at it, the user see's his name. i have run into a problem, because I am unsure of how to retrieve more information from the database. I have this command

$_SESSION['username']=$dbusername; 

which will store their username within this session, and whenever I post

".$_SESSION['username']." 

it will show their username.

But I also want to be able to show user-specific emails, contact information, gender, age, and other types of information. So I have two tables.

The first table details his registration info and has his ID (autoincrement), his first name, last name, password, and his email.

The second table details his ID (autoincrement), first name, last name, email, aboutme, location, etc. etc.

I'm not really sure how to link this information, so that if user (with ID 1) edits. . . let's say his location, how to make sure it goes into the ID column and not any other column.

I am also unsure how to pull this information out specific to a certain ID. So that if user (ID1) is looking at his home page, it shows just his information and not anyone elses. I also want it so that the email from table 1, automatically goes into table 2 if that is at all possible. . .

I'm not sure whether or not this is easy, but I have been unable to find the information for what I want on the internet, so I was wondering if anyone was willing to help or point me in the right direction for what I am looking for! If additional information is needed please ask! Thanks!

Well I figured out a temporary way, and repeated it several times so I would remember it. Here it is, please tell me if this isn't the most efficient way. . .

$getName = mysql_query("SELECT lastname FROM users WHERE username=".$_SESSION['username']."");
 while ($row = mysql_fetch_assoc($getName))  //Checks their last name
  {
  $dbusername = $row['username'];
  $dblastname= $row['lastname'];
  } 

as of now, I have this repeated several times for each function. I am going to change "lastname" to the * so it takes all data, and then combine all the $db""=row['etc.']; but for now, I am happy.

Is this the best way? And should I also do this same function to store the info to my other table? As in use the "SELECT" and then put that info into the other table?

[misson]

I am trying to make it so that I can retrieve information, depending on which user is logged in. I have a page that when a user looks at it, the user see's his name. i have run into a problem, because I am unsure of how to retrieve more information from the database. I have this command

$_SESSION['username']=$dbusername; 

The username identifies the user, correct? In relational database terms, it's a candidate key for the "users" table, which means the username can uniquely identify each row in the table. That means the username is all you need to get any information about the user, whether in the "users" table or any other table that refers to the "users" table.

The session variable you use to fetch the account info from the database should store the user table's primary key rather than some candidate key. This is because any other table that refers to the user table should do so using the primary key, and if the session variable stores anything other than the primary key, it will require querying against the user table in addition to the other table. This doesn't mean you couldn't also store additional user information in the session; you could store all the data in the user's table for the logged-in user as session variables, though be careful to keep the data in the session and the database synchronized.

Since code is easier to follow, consider the following tables. Anything you don't understand in the statements should be looked up in the MySQL manual.

CREATE TABLE users (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(32) NOT NULL UNIQUE,
    `password` BINARY(32),
    `salt` BINARY(16),
    `first_name` VARCHAR(63) CHARACTER SET utf8,
    `last_name` VARCHAR(127) CHARACTER SET utf8,
    `email` VARCHAR(254)
) Engine=InnoDB;

CREATE TABLE messages (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `from` INT UNSIGNED NOT NULL,
    `to` INT UNSIGNED NOT NULL,
    `when` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `subject` VARCHAR(255) NOT NULL CHARACTER SET utf8,
    `body` TEXT NOT NULL,
    FOREIGN KEY (`from`) REFERENCES users (`id`),
    FOREIGN KEY (`to`) REFERENCES users (`id`)
) Engine=InnoDB;

You can easily see the primary keys (which are surrogate keys) and that users.username is a candidate key (note that it's declared UNIQUE). You can also see from the foreign keys that the messages table refers to the users table using users.id. If you store just the username in the session, to find all the messages to a user, you'd need to refer to both tables:

SELECT m.from, m.subject, m.when, m.body
  FROM messages AS m
    JOIN users AS u ON m.to=u.id
  WHERE u.username = :uname
;

Whereas if you use the user ID, you only need to query against one table.

SELECT from, subject, when, body
  FROM messages
  WHERE to = :uid
;

"dbusername" is a misleading name for a variable holding a user's name–it suggests that the value it holds is a database user name, meaning the user name of a database account, such as what your code uses to access the database, rather than the user name of an account on your site. "username" would be better.

But I also want to be able to show user-specific emails, contact information, gender, age, and other types of information.

If these are in other tables, the other tables should refer to the "users" table using a foreign key–a column that holds the primary key of the referenced table. In this case, these other tables should have a column that holds a user ID (such as the from and to columns in the example).

So I have two tables.

The first table details his registration info and has his ID (autoincrement), his first name, last name, password, and his email.

The second table details his ID (autoincrement), first name, last name, email, aboutme, location, etc. etc.

Sometimes redundancy is good. When it comes to RDBs, it's not. Any time you store information in more than one place, you have to worry about consistency. While there are some scenarios where you would design your tables to have certain redundancies (a process called
denormalization), you'd only do it when (a) the schema without the redundancy doesn't perform well enough and (b) the denormalized version performs better.

I'm not really sure how to link this information, so that if user (with ID 1) edits. . . let's say his location, how to make sure it goes into the ID column and not any other column.

I'm not sure what you mean by making the location "go into the ID column", since the ID column should store the ID, not a location.

I am also unsure how to pull this information out specific to a certain ID. So that if user (ID1) is looking at his home page, it shows just his information and not anyone elses.

All this information should be dependent on the primary key of the users table, so simply filter the rows in the query by the user ID of the currently logged in user.

UPDATE users 
  SET location=:location 
  WHERE id=:id

$getName = mysql_query("SELECT lastname FROM users WHERE username=".$_SESSION['username']."");
 while ($row = mysql_fetch_assoc($getName))  //Checks their last name
  {
  $dbusername = $row['username'];
  $dblastname= $row['lastname'];
  } 

Note that you don't need to use a while loop to fetch a row from a result. If there should only be one row in a result (such as in the sample code), just fetch it. Also, don't use the old mysql extension for new code.

try {
    $getNameQuery = $db->prepare('SELECT lastname FROM users WHERE username=:username');
    if ($getNameQuery->execute(array(':username' => $_SESSION['username']))) {
        if (($row = $getNameQuery->fetch()))
        // last name is in $row['lastname']
        ...
    }
} catch (PDOException $exc) {
    ...
} 

as of now, I have this repeated several times for each function. I am going to change "lastname" to the * so it takes all data, and then combine all the $db""=row['etc.']; but for now, I am happy.

Don't use SELECT * unless you're writing a DB administration program; select only the columns you need.

The code that interfaces with the datastore should be in a single module (consisting of multiple classes in a single hierarchy), a data access layer. This reduces the dependence of the rest of the code on the data storage implementation details. See 10 orm patterns: components of a object-relational mapper for an overview of some data access patterns.

It seems you could do with some study of the relational model. Try "Introduction to Databases and Relational Data Model" by Maurer and Scherbakov for an intro to relational calculus and relational algebra. There are better books out there, but none that I've seen online in so complete a form. A web search for "relational model" should turn up more information.

[garrette]

Thank you, very informative! I will be sure to look at an go over all that you've said! Thanks!