Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generating more efficiant SQL

    When you want to fetch data from a SQL database it's common practice to do something like the following:

    PHP Code:

    function getRecord ($recordID)
    {
        
    $query 'SELECT * FROM myTable WHERE recordID = '.intval ($recordID).'; ';
        if (
    $result mysql_query ($query))
        {
            while (
    $row mysql_fetch_assoc ($result))
            {
                
    $return [] = $row;
            }
        }
        return (
    $return);

    So what happens if you need more than one record at once?

    PHP Code:
    for ($thisRec 1$thisRec <= 10$thisRec++)
    {
        
    $records [] = getRecord ($thisRec);

    So what's wrong with this? Well strictly speaking there's nothing wrong with it. but it's not an optimal solution. Most database systems are optimized to return datasets spanning multiple rows, and they have query planners that analyse a query and decide how best to execute it. For these reasons fetching 10 records with 10 queries is not an optimal solution. If possible you want to fetch them with a single query. So how would we go about doing this?

    PHP Code:
    function getRecordList ($recIDList)
    {
        if (
    is_array ($recIDList))
        {
            foreach (
    $recIDList as $thisRec)
            {
                if (!
    $recQueryList)
                    
    $recQueryList 'WHERE )';
                else
                    
    $recQueryList .= ' OR ';
                
    $recQueryList .= 'recordID = '.intval ($thisRec);
            }
            if (
    $recQueryList)
            {
                
    $recQueryList .= ')';
                
    $query 'SELECT * FROM myTable '.$recQueryList.'; ';
                if (
    $result mysql_query ($query))
                {
                    while (
    $row mysql_fetch_assoc ($result))
                    {
                        
    $return [] = $row;
                    }
                }
            }
        }
        return (
    $return);

    It's a bit more work to program but the payoff is you can now fetch an arbitrary number of records in a single query, which will put less strain on your database engine. I've used MySQL in this example but the same should hold true for most other major databases.

    If you find yourself fetching single records on a regular basis and find creating an array every time annoying then simply write a wrapper function.

    PHP Code:
    function getRecord ($recordID)
    {
        return (
    getRecordList (array ($recordID)));

    so you can easily fetch simple records with getRecord but also have getRecordList when you need to do a bulk select.
    Last edited by PassiveSmoking; 01-11-2007 at 03:56 PM. Reason: Code fix

  • #2
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    In MySQL you can just do something like this, which makes your function kind of inefficient.
    Code:
    SELECT * FROM myTable where recordID in (1,2,3,4,5,6,7,8,9,10);
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •