[Tutorial] How to fetch a result set from a MySQL database. The correct way.
#1

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:

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();
    }
}
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.

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;
}
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.
Reply
#2

Nvm, I was wrong
Reply
#3

err even i use for loop to loop though all IDs of particular stuff and fetch them from DB :P
but mm i use SQL Lite these days ;d
Reply
#4

Really the correct way.

Nice tutorial.
Reply
#5

Nice tutorial !
Reply
#6

You don't like the cache functions? As I see you didn't use it. As BlueG said it's much faster.
Nice one.
Reply
#7

Quote:
Originally Posted by Vince
View Post
[...] for the sake of simplicity I will not use the cache here (see AndreT's tutorial on how to use the cache).


(4 char)
Reply
#8

Great tutorial. I know I will be using this very soon.
Just for my interest, can you tell us how to do the same using the cache functions? Just a little example or snippet will help too.

Quote:
Originally Posted by Vince
View Post
pawn Code:
i++; // Now increment our index!
Don't want to correct you but this can be confusing for some people.
Reply
#9

Quote:
Originally Posted by ******
View Post
"SELECT *" is almost NEVER "The correct way." You should explicitly state which columns you want to avoid retrieving excess data.
You are correct as always, but most of the time you do want to retrieve everything, to be stored in an array.
Reply
#10

Quote:
Originally Posted by irinel1996
View Post
You don't like the cache functions? As I see you didn't use it. As BlueG said it's much faster.
Nice one.
This is definitely better way than querying the database separately for each house, yet
the cache functions of BlueG's plugin are even faster. Therefore I would recommend
anyone to use the cache functions.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)