- Joined
- Aug 8, 2004
- Messages
- 3,892
- Reaction score
- 20
Thought I'd share this piece of code that has been very, very usefull for me in the past:
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:
Now, using my code (which you can simply include by using require_once('db.inc.php') that becomes:
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:
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:
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!
PHP:
<?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:
PHP:
$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:
PHP:
$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:
PHP:
query("UPDATE Character SET level = '450' WHERE name = 'FragFrog'");
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:
PHP:
// 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!