Reduce number of queries on website

[biggieuk]

Hi all,

 

I have created an e-commerce website which includes a common.php file at the top of each page.

 

This file contains the link to my mysql database, a few common functions and a query that is used to retreive a list of product categories for my navigation menu.

 

I can't help but think there is a better way to do this so that the same query isn't run each time for each page. What would be the preferred solution here as i need to reduce loading times?

 

thanks for any help/tips with this.

 

Dan

[batch]

You could use define to tell when to execute the query and when not. Just before you include the common.php file on a page for which you want to execute the query, set:

 

define("DoQuery", true); // second argument is not used in this case, but required for define()
require_once('common.php'); // your include

 

In your common.php file you check whether "DoQuery" is defined and if so, you execute the query.

 

if(defined('DoQuery'))
{
// We were called from a page that requires us to execute the query, so do it!
} 

[biggieuk]

Hi,

 

Thanks for your reply. Not sure that i explained myself well enough.

 

The query needs to be run each time so that the menu can be populated, i was just hoping there was another way so that i didn't have to make a database call for every page?

 

Could i hold the values from the db in an array and store this in a Session variable? I coould then check if the variable exists on each page and populate from this. Is that possible?

 

Thanks again.

[Yesideez]

If the menu is stored in the DB then I can't see any other way unless you store the menu system inside an array defined in common.php

 

$arrMenu=array('index'=>'Home Page',
    'cart'=>'Shopping Cart',
    'account'=>'Your Account');
foreach ($arrMenu as $k => $v) {
  echo '<a href="'.$k.'.php">'.$v.'</a>';
}

 

That sort of thing.

[biggieuk]

Thanks mate,

 

Ill give that a go and see how it runs.

[PFMaBiSmAd]

as i need to reduce loading times?

 

Have you determined that the product category query is what is causing your loading time problem? Have you checked what the actual page generation time is? Perhaps you have a lot of large images that is causing the problem and the page generation time (which would include the time taken for the query) is acceptable but the time taken to send all the other content on the page is where the problem is.

 

If the query turns out to be the problem, you need to start by making sure that the table and the query are optimized. Posting the table definition and the query would allow someone to help determine if they can be optimized.

 

If the product categories are basically static (only change when the site administrator/owner adds or removes categories), you can create a cached version of the menu each time it is changed as an include file that is included on each page instead of performing the query on each page.

[biggieuk]

Thanks PFMaBiSmAd,

 

Could you please tell me a little more about caching the menu? So a query is run every time something changes and is stored in a included file?

 

thanks.

[PFMaBiSmAd]

If you posted your existing query and code that outputs the menu, it would help.

 

If we assume that your existing code is echoing the content that makes up the menu, you would need to change it so that it builds the output in a variable. Then you just write that variable to the file that you will be including where you want the menu to appear.