04.08.2012, 22:40
(
Last edited by Vince; 01/12/2012 at 09:40 AM.
)
Introduction
I'm writing this tutorial as a direct result of this post. Many people seem to have no idea how to correctly fetch a large result set with a variable amount of rows from a database.
For this tutorial, I'll be using BlueG's MySQL Plugin version R7. I will use threaded queries, but for the sake of simplicity I will not use the cache here (see AndreT's tutorial on how to use the cache). However, the method I am about to teach you is not limited to any one plugin or version.
The bad way
Often I see people doing something similar to:
This is wrong on so many levels. First of all, you don't know how many rows there are in the database so there is a pretty big chance that you'll be sending a lot of "empty" queries. Secondly, even though MySQL is built to handle large amount of queries it's still a very bad practice to use lots of queries if you can achieve the same result by using just a single query. Note that this example also still uses unthreaded queries.
The good way
So what is the good way? Here we will be using a while loop - as opposed to a for loop - for we don't know how many rows we will be fetching. We will also be using MySQL's internal row pointer, which many people seemingly have never heard about. The row pointer points - as the name suggests - to the row that is currently being processed in the result set. Now, rather than sending a bunch of queries and fetching a single row at a time, we send one query and fetch all rows as one big result set.
This is really all there is to it. No more useless formatting of queries and no more unneeded function calls. Store the result once, process all the rows, free the result once.
I'm writing this tutorial as a direct result of this post. Many people seem to have no idea how to correctly fetch a large result set with a variable amount of rows from a database.
For this tutorial, I'll be using BlueG's MySQL Plugin version R7. I will use threaded queries, but for the sake of simplicity I will not use the cache here (see AndreT's tutorial on how to use the cache). However, the method I am about to teach you is not limited to any one plugin or version.
The bad way
Often I see people doing something similar to:
pawn Code:
stock LoadHouses()
{
for(new i; i < MAX_HOUSES; i++)
{
new query[256];
format(query, sizeof(query), "SELECT * FROM houses WHERE id = %d", i);
mysql_query(query);
mysql_store_result();
mysql_fetch_row_format(result, "|");
// Loading functions go here
mysql_free_result();
}
}
The good way
So what is the good way? Here we will be using a while loop - as opposed to a for loop - for we don't know how many rows we will be fetching. We will also be using MySQL's internal row pointer, which many people seemingly have never heard about. The row pointer points - as the name suggests - to the row that is currently being processed in the result set. Now, rather than sending a bunch of queries and fetching a single row at a time, we send one query and fetch all rows as one big result set.
pawn Code:
stock LoadHouses()
{
mysql_function_query(1, "SELECT * FROM houses", false, "OnHousesLoaded", ""); // we won't pass any other variables to the callback
}
forward OnHousesLoaded();
public OnHousesLoaded()
{
/*
This is our callback for our query.
It will be called as soon as the result is processed by the MySQL server.
From here on out, you just fetch the result in the same way as you would
with an unthreaded query: store the result, fetch the data, free the result.
*/
new
idx,
result[256];
/*
The index (idx) is used as an array index, should you need one. It also
serves to count the amount of rows that were returned.
The string (result) will contain data of the currently selected row.
*/
mysql_store_result();
// Here we start the while loop to fetch the rows
while(mysql_fetch_row_format(result, "|"))
{
/*
Fetch the row that is currently being pointed to.
When this function is used, the row pointer is automatically advanced
to the next row. You don't have to do anything about it.
*/
/*
Here you would go and split your data using sscanf, explode, split ...
The same way like you normally would
*/
idx++; // Now increment our index!
}
mysql_free_result();
printf("[mysql] Succesful. Fetched %i rows from the database.", idx); //
return 1;
}
!
