Cashing query results

[squidsk]

I have a query that potentially gets called for each post, how do I cache the results so that the same query is not run multiple times for the same user? Within a fixed period of time anyways.

[nerbert]

Not that I'm any good at it but you could study up on $_SESSION

http://www.w3schools.com/php/php_sessions.asp

http://php.net/manual/en/reserved.variables.session.php

[squidsk]

Sessions are not for caching db results, and would slow down the site more than the db queries running themselves as every http header would have tonnes of session information.

[kh99]

You could do something as simple as using a global variable array with the key being the userid and the value being whatever data you want to save (the query result or some other data you need from it). Then just check the array before you do a query, and if the data isn't there, do the query and save the results in the array.

ETA: ... but of cours ethat would just keep you from repeating the same query multiple times for one page request. Rereading what you asked, I guess you'd want something that would save the data for multiple page requests?

[squidsk]

Originally Posted by kh99

You could do something as simple as using a global variable array with the key being the userid and the value being whatever data you want to save (the query result or some other data you need from it). Then just check the array before you do a query, and if the data isn't there, do the query and save the results in the array.

ETA: ... but of cours ethat would just keep you from repeating the same query multiple times for one page request. Rereading what you asked, I guess you'd want something that would save the data for multiple page requests?

Yes, I'm not sure how to make use of the built in cache fucntions, similar to the forum cache, so that every query does not have to query the forum/forum permission tables.

[kh99]

Well, the forum cache uses the datastore. You can use it by calling function build_datastore() like:

$mydata = array("some data", "more data");
build_datastore('mydata', serialize($mydata), 1); // '1' means automatically unserialize when reading

Then to load it, create a plugin using hook init_startup and code like:

if (THIS_SCRIPT = 'somepage')
{
   $new_datastore_fetch[] = 'mydata';
}

and your data will be in $vbulletin->mydata. Of course you can use more script names in the if, or leave it out completely, depending on what pages use your data.

Using the datastore that way will actually add one query to each page if no other products are using it on the same page. Also, obviously you can't do a query on the data. If you really have something you want to store that could be different for each user, then you probably don't want to use the datastore (unless you don't have many uses and aren't worried about it scaling).

There's another cache system used by the CMS, but I don't know the details of how you could use it. And I don't think it would eliminate extra queries either (well, at least it will require one to use it).

If your only goal is to eliminate any extra queries, you might be able to add your data to an exitsing table, or create a new table, then add it on to an existing query, depending on exactly what you're doing.

[squidsk]

Originally Posted by kh99

If your only goal is to eliminate any extra queries, you might be able to add your data to an exitsing table, or create a new table, then add it on to an existing query, depending on exactly what you're doing.

That's exactly what I need to do. Now I just need to figure out how to change all the existing code to work through the showthread_query hook.

Edit: nvm, I'm not sure that will work because it will there is are potentially multiple pieces of information for each user, which likely means I can't use that query.

[nerbert]

Originally Posted by squidsk

Sessions are not for caching db results, and would slow down the site more than the db queries running themselves as every http header would have tonnes of session information.

What do headers have to do with it? Session data is stored on the server and is available to your php scripting just like any other data.