Page 1 of 1

Storing MySQL result set in session variables?

Posted: Fri Nov 24, 2006 4:04 pm
by csingsaas
Is it possible to store the results returned my a MySQL query in a session variable?

I have a script that acts as a search engine. I want to take the results of the search and display then on a different page.


Thank you in advance.

Re: Storing MySQL result set in session variables?

Posted: Fri Nov 24, 2006 4:05 pm
by volka
csingsaas wrote:Is it possible to store the results returned my a MySQL query in a session variable?
Yes, they are simply strings (and NULL).

Posted: Fri Nov 24, 2006 4:29 pm
by csingsaas
Maybe looking at my code would help. I think part of the problem is the way I am trying to access the various records in searchresults.php. I looks like I am working with an array when really I'm just trying to get that record number.

code for searchengine.php

Code: Select all

--------------------------------------------
session_start();

require_once('Connections/LeftysConnect.php');

require_once('Connections/LeftysConnect.php');
mysql_select_db($database_LeftysConnect, $LeftysConnect);

$query_RotatingAds = "SELECT direct_title, direct_text, direct_website FROM DIRECTORY_ADS WHERE direct_adtype = 'R' AND direct_adactive = 'Y' ORDER BY RAND()";
$HTTP_SESSION_VARS['RotatingAds'] = mysql_query($query_RotatingAds, $LeftysConnect) or die(mysql_error());
$HTTP_SESSION_VARS['row_RotatingAds'] = mysql_fetch_assoc($HTTP_SESSION_VARS['RotatingAds']);
$HTTP_SESSION_VARS['totalRows_RotatingAds'] = mysql_num_rows($HTTP_SESSION_VARS['RotatingAds']);

header("Location: searchresults.php");
--------------------------------------------
code for searchresults.php

Code: Select all

--------------------------------------------
session_start();

.....


<?php $ctr = 0; if($HTTP_SESSION_VARS['totalRows_RotatingAds'] > 0) { do{ ?>

<a href="<?php echo $HTTP_SESSION_VARS['RotatingAds']['direct_website']; ?>">

<?php echo stripslashes($HTTP_SESSION_VARS['row_RotatingAds']['direct_title']); ?>

<br>
<?php echo stripslashes(substr($HTTP_SESSION_VARS['row_RotatingAds']['direct_text'], 0, 100)); ?>

<br><br>

<?php $ctr++; } while($HTTP_SESSION_VARS['row_RotatingAds'] = mysql_fetch_assoc($HTTP_SESSION_VARS['RotatingAds'])); } ?>
--------------------------------------------

Posted: Fri Nov 24, 2006 4:51 pm
by volka
$HTTP_SESSION_VARS['RotatingAds'] = mysql_query($query_RotatingAds, $LeftysConnect) or die(mysql_error());
You're trying to store the result resource. That does not work.
http://de2.php.net/session wrote:Warning
Some types of data can not be serialized thus stored in sessions. It includes resource variables or objects with circular references (i.e. objects which passes a reference to itself to another object).

Posted: Fri Nov 24, 2006 5:08 pm
by aaronhall
You should be able to store it as such

Code: Select all

$HTTP_SESSION_VARS['RotatingAds'] = mysql_fetch_assoc(mysql_query($query_RotatingAds, $LeftysConnect) or die(mysql_error()));
Is there any reason you're not fetching a single row from the database for every page load, instead of storing a large result set in a session variable?

Posted: Sat Nov 25, 2006 12:54 am
by dibyendrah
Suppose, I'm retrieving 10000 or more rows from database and just to stop querying all the time to database, can we store the resultset somewhere so that without accessing a database we can display the records using that resultset. Is that possible ? And is the SQL query caching done in similar way ?

Posted: Sat Nov 25, 2006 6:36 am
by aaronhall
dibyendrah wrote:Suppose, I'm retrieving 10000 or more rows from database and just to stop querying all the time to database, can we store the resultset somewhere so that without accessing a database we can display the records using that resultset. Is that possible ? And is the SQL query caching done in similar way ?
If you aren't using all 10000 results on every page, you need to optimize your queries to fetch only the rows you need for any given page.

Posted: Sat Nov 25, 2006 9:32 am
by csingsaas
The reason I do not fetch the exact record I need for a given page is because I am creating a search page. There are 5 different criteria the user can specify, and the results are returned to them on a search results page. I want to only show 15 results per page, so if there are 500 results I will need 34 pages. I also want the results ordered randomly, but I do not want them to display on more than one page. So, if one user searches the database he will see one set of results. If another user performs the exact same search, he will get the exact same results - just ordered differently.

I am making a classified ad site if this helps to envision what I am doing.

Posted: Sat Nov 25, 2006 11:04 am
by dibyendrah
aaronhall wrote:
dibyendrah wrote:Suppose, I'm retrieving 10000 or more rows from database and just to stop querying all the time to database, can we store the resultset somewhere so that without accessing a database we can display the records using that resultset. Is that possible ? And is the SQL query caching done in similar way ?
If you aren't using all 10000 results on every page, you need to optimize your queries to fetch only the rows you need for any given page.
I'm just asking about the possibilities. I just want to apply SQL caching.