Most efficient way to get data from the database to session

Question

What is the quickest way to get a large amount of data (think golf) and the most efficient (think performance) to get a large amount of data from a MySQL database to a session without having to continue doing what I already have:

$sql = "SELECT * FROM users WHERE username='" . mysql_escape_string($_POST['username']) . "' AND password='" . mysql_escape_string(md5($_POST['password'])) . "'";
$result = mysql_query($sql, $link) or die("There was an error while trying to get your information.\n<!--\n" . mysql_error($link) . "\n-->");
if(mysql_num_rows($result) < 1)
 {
    $_SESSION['username'] = $_POST['username'];
    redirect('index.php?p=signup');
 }
$_SESSION['id'] = mysql_result($result, '0', 'id');
$_SESSION['fName'] = mysql_result($result, '0', 'fName');
$_SESSION['lName'] = mysql_result($result, '0', 'lName');
...

And before anyone asks yes I do really need to 'SELECT

Edit: Yes, I am sanitizing the data, so that there can be no SQL injection, that is further up in the code.

Answer 1

OK, this doesn't answer your question, but doesn't your current code leave you open to SQL Injection?

I could be wrong, never worked in PHP, just saw the use of strings in the SQL and alarm bells started ringing!

Edit:

I am not trying to tamper with your post, I was correcting a spelling error, please do not roll back.

Answer 2

@ Rob Cooper - In this instance I have escaped and before this exact position in the code I have dome more sanitizing. BTW what edit did you make?

Answer 3

I am not sure what you mean by "large amounts of data", but it looks to me like you are only initializing data for one user? If so, I don't see any reason to optimize this unless you have hundreds of columns in your database with several megabytes of data in them.

Or, to put it differently, why do you need to optimize this? Are you having performance problems?

What you are doing now is the straight-forward approach, and I can't really see any reason to do it differently unless you have some specific problems with it.

Wrapping the user data in a user object might help some on the program structure though. Validating your input is probably also a good idea.

Answer 4

Try using json for example:

$_SESSION['data'] = json_encode(mysql_fetch_array($result));

Edit Later you then json_decode the $_SESSION['data'] variable and you got an array with all the data you need.

Clarification:

You can use json_encode and json_decode if you want to reduce the number of lines of code you write. In the example in the question, a line of code was needed to copy each column in the database to the SESSION array. Instead of doing it with 50-75 lines of code, you could do it with 1 by json_encoding the entire database record into a string. This string can be stored in the SESSION variable. Later, when the user visits another page, the SESSION variable is there with the entire json string. If you then want to know the first name, you can use the following code:

$fname = json_decode($_SESSION['data'])['fname'];

This method won't be faster than a line by line copy, but it will save coding and it will be more resistant to changes in your database or code.

BTW Does anyone else have trouble entering ] into markdown? I have to paste it in.

Answer 5

@Anders - there are something like 50-75 columns.

Answer 6

@Unkwntech Looks like you are correct, but following a Google, which led here looks like you may want to change to mysql_real_escape_string()

As for the edit, I corrected the spelling of efficient as well as removed the "what is the".. Since that's not really required since the topic says it all.

You can review the edit history (which nicely highlights the actual changes) by clicking the "edited a min ago" text at the bottom of your question.

Answer 7

Try using json for example:

$_SESSION['data'] = json_encode(mysql_fetch_array($result));

Is the implementation of that function faster than what he is already doing?

Does anyone else have trouble entering ] into markdown? I have to paste it in

Yes, it's bugged.

Answer 8

@Anders - there are something like 50-75 columns.

Again, unless this is actually causing performance problems in your application I would not bother with optimizing it. If, however, performance is a problem I would consider only getting some of the data initially and lazy-loading the other columns as they are needed.

Answer 9

It's not so much that it causing performance problems but that I would like the code to look a bit cleaner.

Answer 10

I came up with this and it appears to work.

while($row = mysql_fetch_assoc($result))
 {
    $_SESSION = array_merge_recursive($_SESSION, $row);
 }

Answer 11

If Unkwntech's suggestion does indeed work, I suggest you change your SELECT statement so that it doesn't grab everything, since your password column would be one of those fields.

As for whether or not you need to keep this stuff in the session, I say why not? If you're going to check the DB when the user logs in (I'm assuming this would be done then, no?) anyway, you might as well store fairly non-sensitive information (like name) in the session if you plan on using that information throughout the person's visit.