Separating Sql Queries From Presentation

[vchris 3]

I am working with php, mysql and the smarty template engine. Currently I have 1 template file and 1 php file. My queries are in the php page. I'd like to have all my queries in a separate query file. Basically 1 php file with all my queries for the whole site.I'm trying to understand how it would work. Some of those queries require user entered data to execute the query. Some queries require 3 variables, some require 1... Would I create a class or a function? A function for each of the queries?

[justsomeguy 1,135]

This is the database class I built, this uses vsprintf to replace values in a query.

<?phpclass tc_lms_database{  private $host = '';  private $user = '';  private $pass = '';  private $db = '';  private $conn = false;  public $sql_stmt = '';  public $params = false;  private $result = false;  private $rowset = false;  private $last_stmt = '';  /*  Constructor    Set the host, username, password, and database, then connect  */  function __construct($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;      $this->connect();    }  }  /*  connect    Connect to the database using the supplied information, trigger an error if there was a problem  */  function connect($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;    }    $this->conn = mysql_connect($this->host, $this->user, $this->pass) or trigger_error(mysql_error(), E_USER_ERROR);    if ($this->db != '')      mysql_select_db($this->db, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  reset    Reset the SQL statement and all parameters  */  function reset()  {    $this->sql_stmt = '';    $this->params = false;  }  /*  sql    Set the SQL statement  */  function sql($str)  {    $this->sql_stmt = $str;  }  /*  add_param    Add a parameter for the SQL statement, and optionally escape it.  Parameters should be added  in the same order that they appear in the SQL  */  function add_param($val, $escape = true)  {    if ($escape)      $val = "'" . $this->escape($val) . "'";    if (!$this->params)      $this->params = array();    $this->params[] = $val;  }  /*  exec  Execute a SQL statement and return the result.  If the argument is true, do not die on an error.  This returns a mysql result resource.  */  function exec($passthrough_errors = false)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    $this->last_stmt = $this->sql_stmt;    if ($passthrough_errors)      return mysql_query($this->sql_stmt);    else      return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /*  get_row  Get a single row from the given result set  */  function get_row($result)  {    return mysql_fetch_assoc($result);  }  /*  select  Execute a SELECT statement, with an optional limit and page number.  This returns the  results as an array of rows, not as a mysql result resource.  e.g.:  $db->select(10, 3); // get the 10 items on page 3  */  function select($limit = 0, $page = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error ('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)    {      $this->sql_stmt .= ' LIMIT ';      if ($page)        $this->sql_stmt .= (($page - 1) * $limit) . ',';      $this->sql_stmt .= $limit;    }    $this->last_stmt = $this->sql_stmt;    $this->result = mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);    $this->rowset = array();    while ($row = mysql_fetch_assoc($this->result))    {      // convert null to empty string      foreach ($row as $k => $v)      {        if (is_null($v))          $row[$k] = '';      }      $this->rowset[] = $row;    }    mysql_free_result($this->result);    return $this->rowset;  }  /*  delete    Execute a DELETE statement with an optional limit  */  function delete($limit = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)      $this->sql_stmt .= " LIMIT {$limit}";    $this->last_stmt = $this->sql_stmt;    return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /*  insert  Insert a record into the given table.  The $params variable should be an associative  array of column names and values.  e.g.;  $db->insert('table_name', array('id' => 10, 'username' => 'Joe'));  */  function insert($table, $params)  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $names = '';    $values = '';    foreach ($params as $k => $v)    {      $names .= "{$k},";      $values .= "'" . $this->escape($v) . "',";    }    $names = substr($names, 0, -1);    $values = substr($values, 0, -1);    $this->last_stmt = "INSERT INTO {$table} ({$names}) VALUES ({$values})";    return mysql_query("INSERT INTO {$table} ({$names}) VALUES ({$values})", $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  update  Update a record in the given table.  Similar to insert, but the third parameter is the expression  to use in the WHERE clause.  e.g.:  $db->update('table_name', array('username' => 'John'), 'id=10');  */  function update($table, $params, $where = '')  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $cols = '';    foreach ($params as $k => $v)      $cols .= "{$k}='" . $this->escape($v) . "',";    $cols = substr($cols, 0, -1);    if ($where != '')      $where = " WHERE {$where}";    $sql = "UPDATE {$table} SET {$cols} {$where}";    $this->last_stmt = $sql;    return mysql_query($sql, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  escape    Escape a string for use in a query  */  function escape($str)  {    /* if (get_magic_quotes_gpc())      $str = stripslashes($str); */    return mysql_real_escape_string($str, $this->conn);  }  /*  strip    Remove selected characters from a string  */  function strip($str)  {    $replace = array(      "'",      '"',      '-',      ';'    );    return str_replace($replace, '', $str);  }  /*  insert_id    Get the last autoassigned ID  */  function insert_id()  {    return mysql_insert_id($this->conn);  }  /*  num_rows    Get the number of rows produced by the given SQL statement  */  function num_rows($sql)  {    return mysql_num_rows(mysql_query($sql, $this->conn));  }  /*  check_db    Make sure the database has been connected to  */  function check_db()  {    if ($this->conn == false)      die('Database not initialized');  }  /*  error    Generate the last error message  */  function error()  {    return 'Database Error #' . mysql_errno($this->conn) . ': ' . mysql_error($this->conn) . '.  SQL Query: ' . $this->last_stmt;  }  /*  get_last_sql    Get the last SQL statement executed, after parameter replacement  */  function get_last_sql()  {    return $this->last_stmt;  }}?>

Take a look at the exec and select methods to see vsprintf being used to replace the parameters in the SQL statement. This is an example of how I would use this for a select:

$db->sql('SELECT id, question_text FROM test_questions WHERE cid=%d ORDER BY disp_order ASC');$db->add_param($cid, $escape=false); // numbers dont need to be escaped/quoted$response['data'] = $db->select();

If you don't pass false to add_param as the second argument, it will quote and escape the value (you don't need quotes in the SQL statement).

$db->sql('SELECT id FROM content WHERE code=%s');$db->add_param($code);$result = $db->select();

If I want to use num_rows to check how many rows there are in a result, then I use the escape method to make sure the string is safe:

if ($db->num_rows('SELECT id FROM content WHERE code=\'' . $db->escape($insert['code']) . '\'') > 0){  $response['errors']['reason'] = 'That content code is already taken.  Please use another.';  return;}

Notice how the first 2 SQL statements don't have any values in them, just placeholders for the values to get filled in. That sounds like what you're trying to do.

[vchris 3]

That's nice! I already have 4-5 functions for the DB. One for connecting, disconnecting, querying (select), updating, deleting and escaping. What I was referring to is the query itself. The SELECT * FROM [table]. I've heard I should be keeping that outside of the php file and have all these queries in 1 file.EDIT: I just read the last line in your post. I see what you did but then where do you get the query?

[justsomeguy 1,135]

My queries are all in the PHP code, I don't see a problem with having them there. If you want them to all be in the same file it will probably still be a PHP file, you would just define everything as variables or constants and then use the one you're looking for in the page. e.g.:

<?phpdefine('SELECT_ALL_USERS', 'SELECT * FROM users');define('SELECT_ONE_USER', 'SELECT * FROM users WHERE username=%s');?>

<?php$db->sql(SELECT_ALL_USERS);$users = $db->select();?>