A guide to the database handler

Discussion in 'Commonly Asked Questions and Their Solutions' started by renlok, May 28, 2015.

  1. renlok

    renlok Administrator Staff Member

    Joined:
    Oct 20, 2008
    Messages:
    2,858
    Likes Received:
    330
    In version 1.2 and up (not yet released) we have replaced the outdated mysql_* commands with a PDO db handler class.
    Here is a bare bones guide to show you how it works
    The main functions
    $db->direct_query($query)
    This should be used when no variable data is being sent to the database
    examples
    PHP:
    $query "SELECT * FROM " $DBPrefix "filterwords";
    56    $db->direct_query($query);
    PHP:
    $query "SELECT COUNT(id) As COUNT FROM " $DBPrefix "users WHERE suspended = 0";
    $db->direct_query($query);
    $db->query($query, $params)
    This should always be used instead of direct_query if variable or user submitted data is sent to the database
    $params is an array in the form
    Code:
    $params = array(
    array(':that', 'that value', 'data type'),
    array(':here', 'here value', 'data type'),
    );
    
    where data type is the data type of the input data it can be one of the following:
    int, str, bool, float
    examples
    PHP:
    $query "SELECT COUNT(id) As COUNT FROM " $DBPrefix "auctions WHERE user = :user_id";
    $params = array();
    $params[] = array(':user_id'$id'int');
    $db->query($query$params);
    PHP:
    $query "UPDATE " $DBPrefix "feedbacks SET
              rate = :rate,
              feedback = :feedback
              WHERE id = :feedback_id"
    ;
    $params = array();
    $params[] = array(':rate'$_POST['aTPL_rate'], 'int');
    $params[] = array(':feedback'$_POST['TPL_feedback'], 'str');
    $params[] = array(':feedback_id'$id'int');
    $db->query($query$params);
    $db->numrows()
    Shows the number of rows returned from the query or direct_query that was last run
    example
    PHP:
    $query "SELECT id FROM " $DBPrefix "adminusers";
    $db->direct_query($query);
    if (
    $db->numrows() > 0)
    {
      ...
    }
    $db->result($column = NULL)
    Returns either a single row of data or a single column from a single row if $column is set. should only be used if you know only a single row will be returned
    examples
    PHP:
    $query "SELECT COUNT(id) As COUNT FROM " $DBPrefix "users WHERE suspended = 0";
    $db->direct_query($query);
    $USERS $db->result('COUNT'); // returns a single value
    PHP:
    $query "SELECT nick FROM " $DBPrefix "users WHERE id = :user_id";
    $params = array();
    $params[] = array(':user_id'$_POST['id'], 'int');
    $db->query($query$params);
    $user_data $db->result(); // returns an array
    $db->fetchall()
    Returns every row of data should be used if the data is going to be handled via a foreach loop
    PHP:
    $query "SELECT * FROM " $DBPrefix "faqscategories  ORDER BY category";
    $db->direct_query($query);
    $faq_cats $db->fetchall();
    foreach (
    $faq_cats as $row)
    {
    ...
    }
    $db->fetch()
    Returns the next row, should only be used in a while loop.
    PHP:
    $query "SELECT * FROM " $DBPrefix "faqscategories ORDER BY category";
    $db->direct_query($query);
    while (
    $row $db->fetch())
    {
    ...
    }


    Note: You cannot run a fetch loop inside a fetch loop to do this you must combine fetch and fetchall
    example
    PHP:
    $query "SELECT * FROM " $DBPrefix "faqscategories  ORDER BY category";
    $db->direct_query($query);
    $faq_cats $db->fetchall();
    foreach (
    $faq_cats as $outside_row)
    {
       
    $query "SELECT * FROM " $DBPrefix "faqscategories ORDER BY category";
       
    $db->direct_query($query);
       while (
    $inside_row $db->fetch())
       {
       ...
       }
    }
     
    Last edited: May 28, 2015
  2. nay27uk

    nay27uk Super Moderator Staff Member

    Joined:
    Nov 24, 2009
    Messages:
    5,569
    Likes Received:
    546
    Cheers renlok but in the following code
    PHP:
    $query "UPDATE " $DBPrefix "feedbacks SET
              rate = :rate,
              feedback = :feedback
              WHERE id = :feedback_id"
    ;
    $params = array();
    $params[] = array(':rate'$_POST['aTPL_rate'], 'int');
    $params[] = array(':feedback'$_POST['TPL_feedback'], 'str');
    $params[] = array(':feedback_id'$id'int');
    $db->query($query$params);
    What are the empty [ ] all about in the $params, and the empty ( ) in the $params array?

    sorry but as you know code goes strait over my head
     
    BabyDunk and david62311 like this.
  3. renlok

    renlok Administrator Staff Member

    Joined:
    Oct 20, 2008
    Messages:
    2,858
    Likes Received:
    330
    That is just how arrays work in php http://php.net/array
    PHP:
    $params = array();
    sets $params as being an empty array
    and
    PHP:
    $params[] = 'something';
    will set the next item in the array to equal 'something'
     
    BabyDunk and nay27uk like this.
  4. nay27uk

    nay27uk Super Moderator Staff Member

    Joined:
    Nov 24, 2009
    Messages:
    5,569
    Likes Received:
    546
    thanks pal that explained it fantastic
     

Share This Page