Can the result of a MySQL query be saved in a SESSION?

PHP
1

Hello,

Can the result of a MySQL query be saved in a SESSION for later
retrieval?

Because I am getting an error message when I try to retrieve the results
of a MySQL query from a SESSION, I get this error message:
mysql_fetch_array(): supplied argument is not a valid MySQL

Because otherwise I would have to do this query twice, 1st when I am testing
to see if it returns any records and 2nd when I later actually need those records. So I think that there must be a way to save the results of a MySQL-query in sessions.

Thank you,

2

A query returns a “resource”. AFAIK, you can’t store a resource in a SESSION variable. You should be able to get the results from the resource using any of a number of ways, eg. fetch array, and put those into a SESSION variable. You may need to serialize them, and then unserialize them when you want to use them.

3

Hi,

So are you saying that result of MySQL-query cannot be saved in a SESSION?
That I need to loop through the MySQL-query and fetch each item
and store each item separately in a named SESSION? And then later
loop through these names SESSIONs and get each item one by one?

Regards,

4

Right. You can’t store a resource (such as a mysql connection, or a mysql resultset) in a session. You can store the rows, though.


$rs = mysql_query( 'query goes here' );

$arr_rows = array();
while( $row = mysql_fetch_array( $rs ) )
$arr_rows[] = $row;

$_SESSION[ 'arr_rows' ] = $arr_rows;
5

think of the mysql connection, can that be saved over a session? not exactly. but you can use persistent connections not that it will help the problem here.

the data is what you want to save here. to do that you’ll have to save that data as an array or whatever as a session variable.

6

Hi,

So my question is, and may be not easy to answer:

lets say that the Mysql-query returned 10,000 rows, is it better CPU wise, specially server side, to store these data in SESSIONS, one by one, or just re-issue the original MySQL SELECT?

Regards,

7

Never ever store that amount of data in a session!
You should re-check if you really need 10.000 rows at once or if it is possible to retrieve just a small subset.

8

If your query returns 10,000 rows and that is more than you need then there is something wrong with your query. If for example you want 100 rows per page you need to limit your query to 100 rows and set the start and end point so the DB returns the correct rows.

9

No need. Php does that automatically when it saves an array in a session.

10

HI,

I think you should work on your query and get the exact amount of data from the database and then store it in the session.in this way you can optimize your code and later on you can use this data in query again for some specific data.

NOddy

11

Curios, why do you want to do this, storing the result in a session, for performance reason?

12

if the data changes often youll have to fetch it either directly from the db or from a cache that is updated sufficiently often.

it’s strange that you would need such a large amount of data. u can limit that amount with the LIMIT keyword ie SELECT * FROM logs ORDER BY date DESC LIMIT 0,20 (i think the start is 0, and 20 the amount of records)

u could also use memcached. instead of saving this data in a session (as its likely there will be many sessions) just save it into the memcache and load it up before u work with it.

but again that many records seems strange

13

Probably not a good idea, you might use a setup where sessions are stored in the db.

14

Hello,

1st, Thanx for all your answers.

IN reply to some of the questions, I do not need to always get 100K records with this Select case. However, I guess my question is this:
assume I need to get certain amount of data from the MySQL DB, but for whatever reason find out that I cannot use them when I got them but instead need to use them later after something else has executed.

So the next question is: what is more CPU friendly, on server side, to put these data in a SESSION or just to pull them from the MySQL later again?
I mean I am sure SESSION retreival is faster than MySQL retrieval, right?
But is that the case if the data set is large too? Or at what point, at how much data, does the MySQL become faster (more CPU friendly) than SESSIONS?

Regards,

15

Why dont you make the query later on where you need that data exactly so there would not need to be storing it before hand? This sounds a little bit like a matter of organizing your code.

16

If the table hasn’t been updated MySQL will have the result cached anyway so it will be fast the second time anyway.

But… I think you are going to keep on and on asking until someone gives you the answer that you want to hear.

17

In my limited experience it is HTTP requests that are usually the “bottleneck”, not the PHP parser or database server. The only real answer to which is faster, PHP or MySQL, would need to be found by doing benchmark testing for the particulars in each individual case.

But I have a feeling a database call would be preferred over a SESSION.

If your database call is bogging out, it is most likely that optimizing the query is the answer, not using PHP to avoid running an inefficient query more than once.

18

these should be two different queries

the first one should simply return a count, not the entire result set, and yes, you can store a single number in a session variable

simple, innit

:cool:

19