vchris 3 Posted March 17, 2009 Report Share Posted March 17, 2009 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? Quote Link to post Share on other sites
justsomeguy 1,135 Posted March 17, 2009 Report Share Posted March 17, 2009 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. Quote Link to post Share on other sites
vchris 3 Posted March 17, 2009 Author Report Share Posted March 17, 2009 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? Quote Link to post Share on other sites
justsomeguy 1,135 Posted March 17, 2009 Report Share Posted March 17, 2009 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();?> Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.