$query_get_kws = mysql_query($sql_get_kws) or die(mysql_error());
How do I pass the query result that is in $query_get_kws to another page via SESSIONs so that I would not have to re-execute the underlying SQL statement?
as rajug suggests, put all the returned rows in your result set into an array and save the array as a session variable. that session var can then be used in other pages for the same session.
I’m not sure it will be faster, especially if you are returning a large number of rows.
this demo shows one way to output the rows in a result set in page 1 (index.php) to a session array and then output the contents of that session array in page 2 (formProcessor.php)
I have also included the sql to create the test table this demo uses.
Page 1 - index.php
<?php
session_start();
//----------------------------------------------------------------------------------------------------------------------
//connect to the database
$DBUserName = "xxxx"; //database user account name
$DBPassword = ""; //database user account password
$DBName = "xxxx"; //name of database
@$conn = mysql_connect("localhost", $DBUserName, $DBPassword) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />'); //connect to mysql
@$isDbSelected = mysql_select_db($DBName, $conn) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />'); //connect to the db
//----------------------------------------------------------------------------------------------------------------------
$query = 'select * from tblperson';
$rs = mysql_query($query,$conn);
$_SESSION['rows'] = array();
while($row=mysql_fetch_assoc($rs)) {
$_SESSION['rows'][] = $row;
}
echo 'This is page 1. Number of rows in session variable = '.count($_SESSION['rows']);
?>
<br /><a href="formProcessor.php">go to page 2</a>
Page 2 - formProcessor.php
<?php
session_start();
echo 'This is page 2. Number of rows in session variable = '.count($_SESSION['rows']).'<br />';
echo 'Contents of session variable<br />';
foreach($_SESSION['rows'] as $row) {
echo $row['fldPersonID'].' '.$row['fldFamilyName'].' '.$row['fldGivenName'].'<br />';
}
?>
sql to create test data table
CREATE TABLE `tblperson` (
`fldPersonID` int(11) NOT NULL AUTO_INCREMENT,
`fldFamilyName` varchar(20) DEFAULT NULL,
`fldGivenName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`fldPersonID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
/*Data for the table `tblperson` */
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Sui','Steven');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Mary');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Sam');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Malik','Moore');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','fred');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('rubble','barney');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','wilma');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','pebbles');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('smart','maxwell');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Sui','Steven');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Mary');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Sam');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Malik','Moore');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','fred');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('rubble','barney');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','wilma');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','pebbles');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('smart','maxwell');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Sui','Steven');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Mary');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Sam');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Malik','Moore');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','fred');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('rubble','barney');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','wilma');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','pebbles');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('smart','maxwell');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Sui','Steven');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Mary');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Student','Sam');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Malik','Moore');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','fred');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('rubble','barney');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','wilma');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('flinstone','pebbles');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('smart','maxwell');
insert into `tblperson`(`fldFamilyName`,`fldGivenName`) values ('Soo','Malinda');
While storing the data in the session, we have to consider how much data volume will be in the result. If the volume is too big then it may hamper the server consuming more space. So in that case it is better to store the prepared SQL query in the session and execute the query again in other pages too. Storing prepared query will consume less memory.
but when you run the prepared statement, aren’t all the returned rows still stored “somewhere” just like they are in a result set using mysql_query()?
so unless that “somewhere” is a much more efficient storage method than a result set or an array, I wouldn’t have thought there would be much difference in space requirements for the returned rows.
I mean the SQL statement that is prepared with all required clauses filled in already like (SELECT * FROM tblename WHERE field1=‘val1’ AND field2=‘val2’). Storing only SQL statement will consume less memory I think. But this is only possible if OP will be able to run/execute query in rest of the pages too.
I don’t use prepared statements but I would have thought that the amount of memory required to store a single line query whether in a prepared statement or as a session variable like
$_SESSION[‘query’] = ‘select * from tblWhatever’
would be very small and insignificant.
I think the OP was asking about transferring the actual rows returned by the query from one page to another without having to run the query again in any way on the 2nd page. the OP is trying to avoid running the query on every new page.
since a result set can’t be stored as a session variable, the only way I know then is to output the returned rows into a session array as per the demo code I posted earlier.
The same way I have already proposed in my very first post in this thread and I am still with the same way for small volume. But I am only concerning here if the volume of data is too big then have to consider to store in the session. Period.
Kalon, I am not disagreed with your idea. But what I mean to say is that we have to make a consideration to store big volume data (like hundreds/thousands of records) in the session variable. This is only my opinion.
BTW, using session is the idea of OP himself so you just mentioned to use session. But I proposed the array of data in the session variable. So I am the first to propose to use arrays in the session.
I suggested using session variables in post 3 and you did as well in post 5, but if you want to believe you were first then so be it It’s not important to me.
I gave a demo of my suggestion in post 3 in the demo code in post 10.
if volume of data is an issue then that issue would arise whether the returned rows were stored in a session variable or whether the query was run on every new page and stored in a result set because both are stored in memory.
say for example if a query returns 2000 rows (pick any number) then those 2000 rows have to be stored in either a result set by running the query on every page or running the query only once and storing those 2000 rows in a session array. the space required to store those rows is much the same afaik regardless of whether they are in a result set or session array. so if memory space is an issue, it will be an issue even if the query is run on every page because you will still have to store those 2000 rows in memory on every page.
How about using a caching technique like Memcache or APC? If the same query needs to be ran for all your visitors you will save a considerable amount of memory (because you only need to store it once for all visitors, not 1 time for each and every visitor seperately) and the caching system will automatically prune away results that are too old when newer cache items arrive; sessions don’t have such a mechanism so if the database results are really huge and a lot of visitors have them in their session you even run the chance to fill up the hard disk of the server with all those results, resulting in all kinds of havoc.
TL;DR Sessions really aren’t the place to cache temporary results IMHO.