How do I avoid large numbers of queries with mysql_fetch_array?

[Vigilante]

I am writing a web application in which I repeatedly use

while ($whatever=mysql_fetch_array($query)) {
// copious amounts of queries being generated
}

to generate lists of things in my database. I'm having much success with this but the problem is that it uses a huge amount of queries.

...or at least I think it does. I have a function that I call which uses mysql_query and that function has a global variable that increments so all I really know is happening is this function being called for every item on every row of the array, which can amount to more then 700 queries on some pages. Since pages with this many queries take longer to load then everywhere else (about 1.100 sec instead of .100 sec) my sql server must think it's being attacked every time a page loads.

I've noticed that InvisionBoard manages to get many rows without increasing the queries it takes. Using my familiar while loops the number of queries increases with the number of rows. So, somehow, I need to select all the rows I need at once and render them in a loop. I took a peek at InvisionBoards code and found calls to mysql_fetch_array in their database module but didn't see it in any while loops as I've been doing.

Lastly: I'm not asking only for a code snippet, as I want to understand this problem as it seems like something I could apply to programming often and in many ways. So if someone would be so kind as to detail to logic of this, post a link to a tutorial or even suggest something that might work I would be extremely greatful.

[[deleted]]

mysql_query() is the only function that actually runs a query.

All the rest just reads data from a resultset that was sent to PHP when you called 'mysql_query()'

So you can call mysql_fetch_array() a milljion times, the database will never notice it at all. PHP fetches the arrays from it;s own memory banks that were filled during the execution of mysql_query().

What you will notice on the database is how many records your query returns.

If you do

SELECT * FROM table;

then you will get the entire table, and if there are 700 rows, then 700 records will have to be sent from the database to PHP. But, this happens when you run mysql_query(), not when you run mysql_fetch_array().

If you select 700 rows and only really need the first 10, you can tell SQL not to give more than than ten rows, using LIMIT. For mysql the syntax is: LIMIT offset,numberofrows

so it becomes:

SELECT *
FROM table
LIMIT 0,10;

Now the database will only send 10 rows, which is much faster than sending 700 rows.

[Vigilante]

So the problem is my query counter? Splendid! That means my code is some 700 times more efficient then I thought.

My project is a content management system and is running at http://www.arc-corp.net if you're curious.

I want to make absolutely sure though. You are telling me to do this

$this=mysql_query("SELECT lotsashit FROM spiffytable WHERE somethingorother");

while ($that=mysql_fetch_array($this)) {

}

... is only one query? I guess that's what you just said but it's almost too good to be true. This had been bothering me for a long time.

[[deleted]]

"... is only one query?"

Ofcourse it is, 🙂
There is only one mysql_query() command in there, and the script-flow causes it to be executed only once.

So, just to clarify the situation:

• mysql_query() sends a query to the database.
• the database executes the query (once) and sends the results back to PHP
• PHP stores the results in memory and makes the them accessible through mysql_fetch_array etc

As soon as mysql_query() finishes, the database is idle (well, for this script. There may ofcourse be other scripts running queries etc) and the resultset is stored locally in PHP. Whatever you do with the resultset has no effect on the database server anymore.

The only thing that does affect the database is how many queries you run (how many times you call mysql_query()), how complex those queries are, and how many results they return.

[searain]

I am using InterBase, InterBase 6.4 and before don't have the feature to select which rows to display. Then what can I do? I have to gether all the 700 rows?

Thanks for any helps.