Can the result of a MySQL query be saved in a SESSION for later
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.
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.
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?
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.
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.
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.
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?
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.