Minimising SQL Calls and using arrays

microbluechip

Hi All,

This one has me scratching my head.

At the moment I have an index page which contains multiple calls to mySQL with each returning different sets of data for elements such as picklists etc. As a result the index page takes between 6 to 12 seconds to display while these queries are being executed.

Is there a simple methodology or concept in PHP for returning all information that I require from my DB in just one SQL query and say dump the result into an array.

I have manged to get all of the desired information into a single array but it is then how I index or interrogate this array to retrieve only the elements that I require.

The desired effect of all this being that it is quicker to search and process an array in memory rather than using SQL and incurring disk access cost and other disk-based resources.

MicroBlueChip.

devinemke

If your page is taking that long to load then you should examine your code and see if you really need all of that data that you are pulling out. Make your queries as precise as possible, don't pull out more data than you need and then use PHP to pair it down. Use mySQL built in formatting functions. You should also index your mySQL columns correctly to speed up the queries. Finally, once you have all of the data, and are going to need it on subsequent page requests than you should really look into PHP sessions (http://www.php.net/manual/en/ref.session.php)

Shrike

humm, it's a bit hard to know what you mean... I take it you are using mysql_fetch_assoc($result); to get your data. That gives you the flexibility to take which elements you need. Also for the query you can

select t1.fieldlist, t2.fieldlist ... etc
from tablename1 as t1, tablename2 as t2 ... etc
where t1.id = "some id"
and t2.id_t1 = t1.id

or some join statement for a more advanced query to get your data from multiple tables

Post more info plz 🙂

microbluechip

Thanks Both,

I wondered if I was explaining myself correctly.

I have already got all the data that I need so there is no issue with retrieving data that is not required.

I am already using simple joins to link four tables together. However I now have one huge array which I now need to process and search through in order to populate a html select pciklist.

The trouble is I do not know the array positions where one set of data ends and the next set of data begins. The sub-arrays are using mysql_fetch_assoc to reference by field_name however the main outer array is storing by number i.e. 0, 1, 2, 3 etc

MicroBlueChip.

Shrike

Write the table name and the cell data to the value portion of the SQL hash as an array 🙂

$mydata['row'] = array($value, $tablename);

🙂

I have done something similar recently, however I benefited from a great DB design done by a guru so I don't ever need to get unnecessary information. Good DB design is the cornerstone of a good app 😉