[SOLVED] Problems with checking Session IDs..

[galvin]

This has been driving me nuts for the past 30 minutes. I have a table called "orders" with just two columns (orderID and sessionID).  When visitors come to the page called intro.php for the first time, I want an orderID and sessionID to get inputted into the table.  I have done this without a problem.  But I want to have something in place where if the customer hits "refresh" while on intro.php, it will check to see if that sessionID was ALREADY put into the database, so that it doesn't create ANOTHER orderID for the same sessionID.

 

So in other words, the page intro.php will first check to see if the sessionID is already in the orders table. If it is, it does nothing.  If it is NOT, then it will do the insert (which I know how to do).

 

Is there a simple way to do this?  It seems so easy, but I'm having trouble.  Shouldn't I just be able to have something like...

 

 
(I started the session at the top of the page)

$sessionid = session_id();

query = "SELECT sessionID FROM orders WHERE 'sessionID' = $sessionid";

$checkorders = mysql_query($query, $connection);

if ($checkorders) {

//don't do anything because this session's SessionID is already in the table

} else {

//run my code to INSERT the sessionID into the table

}

 

This isn't working and I can't figure out where I've gone wrong. 

 

Any ideas?

[revraz]

If that is a cut and paste of your code, then you forgot the $ in front of query.

 

Also, you should check to see if any rows are returned after the query, that will tell you if there are any matching sessionIDs.

[galvin]

Ok, so I tried the following code, but it keeps inserting the same sessionID into the table over and over, which is just what I'm trying to avoid.  In other words, the code below keeps doing everything after " if (!$isitthereyet) " so my code thinks there is NEVER anything in the array, but if it runs the code the first time, it should see something in that array the next time.  Is something wrong with how I'm checking that array?

 

Maybe I have to use a different syntax than "if (!$isitthereyet)" ?

 

Any ideas?

 

 

$query = "SELECT sessionID FROM orders where 'sessionID' = '$sessionid'";

			$checkorders = mysql_query($query, $connection);

			if (!$checkorders) {
				die('Database could not be reached: Hit refresh to try again. If problem persists, please try back later' . mysql_error());
			}


							$isitthereyet = mysql_fetch_array($checkorders);

							if (!$isitthereyet) {

									///nothing was entered into the orders table yet for this visit, so let's do that now (must be their first visit to this page)

														$query = "INSERT INTO orders (
																	sessionID
																	) VALUES ('" . $sessionid . "')";

															$result = mysql_query($query, $connection);
															if ($result) {
																$message = "<p>The Session ID was successfully submitted into the DATABASE.  </p>";

															} else {
																$message = "The Session ID was NOT submitted.";
																$message .= "<br />" . mysql_error();

															}


							} else {

									//do nothing since this session's sessionID it must already be in the table

							}

[hobeau]

hi galvin,

 

here is the code I believe your looking for:

 

<?php
session_start();

$sessionid = session_id();
$connection = mysql_connect('host', 'username', 'password') or die(mysql_error());
mysql_select_db('database', $connection) or die(mysql_error());

//  Use the SQL function COUNT to count how many rows the query comes up with
$query = "SELECT COUNT(sessionID) FROM orders WHERE sessionID = '" . mysql_escape_string($sessionid) . "'";
$result = mysql_query($query, $connection) or die(mysql_error());
// Use the mysql_fetch_row function to get just a single row from the returned results
$count = mysql_fetch_row($result);

if (intval($count[0]) == 0) 
{ 
$query = "INSERT INTO orders (sessionID) VALUES ('" . mysql_escape_string($sessionid) . "')";
mysql_query($query, $connection) or die(mysql_error());

// mysql_affected_rows returns how many rows were affected from your mysql database
if (mysql_affected_rows($connection) > 0)
{
	$message = "<p>The Session ID was successfully submitted into the DATABASE.  </p>";                                                  
}
else 
{
	$message = "The Session ID was NOT submitted.";
	$message .= "<br />" . mysql_error();
} 
}                        
?>

 

A couple things to note. In your original code you have sessionID in single quotes in your SELECT statement. That will cause the query to return nothing. Secondly, you weren't escaping your $sessionid variable which could leave your application open to sql injection. Next we use the sql COUNT scalar function to return an integer value of how many rows are in the dataset returned from the query. Then we get this value by using the mysql_fetch_row to get the number of rows we found. Next we do a simple integer conversion (to ensure that we are definitely working with an integer type) and check to see if the value is 0. If it is, we know that this value does not exist in the table and we run our insert query (again,, escaping our variables as we concatenate them into the sql command) and insert the sessionid into the table. Then we use the mysql_affected_rows function (which 'asks' mysql how many rows were affected in the last sql command) and it will verify weather or not the session was inserted into the table.

 

 

Something to take note of. I'm not exactly sure why your inserting your session ID into a table, but you may want to check out something called session fixation (http://en.wikipedia.org/wiki/Session_fixation). Session fixation can be thwarted by using a function called session_regenerate_id() which regenerates the session ID every page the user visits. Using this can be one of the many ways to ensure that your web application is secure, however it may make it more difficult to do whatever it is your doing now as the session will be dynamic instead of dangerously static.

 

Last (but certainly not least) you may want to look into PDO (PHP Data Objects http://us2.php.net/manual/en/book.pdo.php). This is a very powerful database abstraction layer that is replacing the plethora of database drivers currently used to connect to mysql, mssql, postgres and others. PDO has more features than the mysql extension and provides a centralized API for connecting to many different DBMSs using the same code (so basically no dfference between connecting and querying between mysql and mssql).

Hope this helps!!!

[galvin]

That worked, hobeau, thanks! 

And I will definitely read up on the other items you mentioned.  Thanks for sharing your knowledge (I had no idea it was dangerous to submit SessionIDs into a database, so thanks for the heads up on that).

Thanks again!

 

[hobeau]

Hey no problem galvin,

 

Its actually not dangerous to insert session id's into a database per se,, the dangerous part is not regenerating your session id regularly. glad to help.