SQL queries in PHP - the fast way

[FragFrog]

Thought I'd share this piece of code that has been very, very usefull for me in the past:

<?php
  function &getDB() {
    static $instance;
    if(!isset($instance)) {
      // connect to the DB
      $instance = mysql_connect("localhost", "sql_username", "sql_pass") or error("could not connect to the db");
      mysql_select_db("sql_database_name", $instance) or error("could not select db");
    }
    return $instance;
  }
  
  /**** 
    query takes as input a normal SQL query, uses an MySQL connection to execute it and then
    returns the result - either nothing or the data requested in either an associative array or
    an numbered associative 2D array.
   
    Any errors are noted trough the global $error variable, and the amount of queries run in total
    is stored in the global $queries variable.                                
   
    Note that the number of affected rows is only determined for queries where SELECT is in the
    first few letters of the query - everything else is either an update query or contains only
    a subquery with SELECT in it somewhere. This to prevent errors caused by mysql_fetch_assoc on
    update queries. 
  ****/
  function query($query) {
    global $error, $queries;
    $queries++;
    $instance = getDB();
    $res      = mysql_query($query, $instance);
    $affected = stristr(substr($query, 0, 8), "SELECT") ? mysql_num_rows($res) : false;
  
    if(mysql_error($instance))
      error(checkVar(nl2br(mysql_error($instance))));
      
    switch($affected) {
      case 0:
      case false:
        $result = false;
        break;
      default:
        $i = 0;
        while($result[$i++] = mysql_fetch_assoc($res)) {}
        array_pop($result);
        break;
    }
    return $result;
  }

function error($message) {
  global $error;
  $error .= $message."<br />";
  return true;
}

This saves you the trouble of having to type that basic stuff like mysql_connect, mysql_fetch_assoc, etc again and again and again.

For instance, if you want to select a few records from your character table, what you'd use to do is this:

$instance = mysql_connect("localhost", "sql_username", "sql_pass") or error("could not connect to the db");
mysql_select_db("sql_database_name", $instance) or error("could not select db");
$query = mysql_query("SELECT * FROM Character");
$result = mysql_fetch_assoc($query);
foreach($result as $character)
  echo $character['name'].$character['level']."<br>"; 

Now, using my code (which you can simply include by using require_once('db.inc.php');) that becomes:

$data = query("SELECT * FROM Character");
foreach($data as $character) 
  echo $character['name'].$character['level']."<br>"; 

Sure, if you use only a single SQL query on your entire website the effect is not that big, but everyone who uses SQL queries extensively will find this saves him a LOT of time in the end :smile:

Note that it is not only used to select queries:

query("UPDATE Character SET level = '450' WHERE name = 'FragFrog'"); 

This will work just as easily :smile:

Furthermore, this can also be used if you have an ODBC database instead of an MySQL database, you just have to modify it a bit:

  // For use with an ODBC connection:
function &ODBC() {
  static $instance;
  if(!isset($instance)) {
    // connect to the DB
    $instance = odbc_connect("sql_database_name", "sql_user", "sql_pass") or error("could not connect to the db");
  }
  return $instance;
}

function query($query) {
  global $error;
  $i = 0;
  $instance = ODBC();
  $execute  = odbc_exec($instance, $query);

  if(odbc_errormsg($instance))
    error(checkVar(nl2br(odbc_errormsg($instance))))

  if(odbc_num_rows($execute) == 0)
    $result = "";

  else {
    while($result[$i++] = odbc_fetch_array($execute)) {}
    array_pop($result);
  }
  return $result;
} 

I hope this helps someone :smile:

Footnote: this does NOT check your variables for SQL injection. I have written a databaseclass a while ago that does, but it is much more complicated and not finished yet. You will still have to check for SQL injection yourself!

[MentaL]

[Ytys Vynsan]

Nice :)