CODEWITHSUNDEEP
phpmysql
tuck307
tuck307

Reputation: 35

Storing MySQL query results in session or cookies to limit DB Calls

I have a ingredient mysql database. I let the user save to their profile home cabinet what ingredient per category they select. I have 10 categories, so I will be making 10 queries per profile. My query is setup like so:

select I.Iname, I.Ipic, I.IID, I.ICat 
from Ingredient I 
left outer join Cabinet C on I.IID = C.IId 
where Login.LoginID='{$_COOKIE['login']}' and I.ICategory ='fruit';

-- is ran 10 times since i have 10 different categories

--for each row returned it outputs divs under the specific category header.

Now my question is there a more optimized way of doing this as to storing their cabinet data in session objects, arrays or using a cookie? Or is running 10 of these queries not that much of a performance impact. What if someone is returning 100 rows for each category? Or should I just query all categories and loop through the result to place each row in its specific area.

cabinet table :

loginID | ingredientID 
----------------------
   1    |     3 
   1    |     4

Upvotes: 2

Views: 471

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157916

You have 2 possible choices:

  1. Leave everything as is. There is nothing wrong with SQL queries per se. A database is not something necessary slow and hulky. It is just a storage, as well as a session file (which can be in turn stored in the database, so, you'll end up in the same point but make things unnecessary complicated!). Databases intended to e queried! It is the only their destination! And they are smart enough even to cache your results.
    Note that storing mysql results in cookie is not an option at all, under any circumstances, it will slow your site for sure. (It seems it always needs to be clarified that there is nothing wrong with cookies as well. it is just not intended to cache data)

  2. Implement some Model in terms of MVC pattern and make all data requests abstract. So, you will be able to implement any caching techniques later, without altering other program code.

Also note, that no performance-wise question asked on the grounds of wild guess can make sense.
See, you were about to make even worse (using cookies), out of some grounndless assumption.
Always ask yourself about performance only if there is a certain problem to solve. Otherwise you will most likely just miss the point or even do things worse.

Update
After seeing your query I can say that there is no need to do 10 queries, - one is enough. Just order it by ingredient and then add divs dynamically in PHP. (If you have no idea how to do it, better create another question, to make things do not interfere)

And of course you have to escape your $_COOKIE['login'] if you don't do it already. 

$cookie = mysql_real_escape_string($_COOKIE['login']);

and use that $cookie variable in the query.

Also note that using just cookied login will make your site extremely weak in term of security - anyone will be able to login under someone else's account.

Upvotes: 4

Related Questions