Part 2: Expanding the useless to usefull

Last time we went over the base DB connection and the use of re usable code, now we will expand the dbconnector.php to make it more useful to everyone. We left off by using the new dbconnector to create a link, now we add some code so we can make query’s and make the queries safer than just sending unchecked data to it.

Here is were we left off

function DbConnector(){
$settings = SystemComponent::getSettings();
$host = $settings['dbhost'];
$db = $settings['dbname'];
$user = $settings['dbusername'];
$pass = $settings['dbpassword'];
$this->link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
register_shutdown_function(array(&$this, 'close'));}
function close() {
mysql_close($this->link);
}

so we have our DB connection setup and the close function, now we will add the query and cleaner classes to to make this a more useful to real world applications
Now lets start by adding some query code so the class can query the DB, a very simple function called query will be added
function query($query) {
$this->theQuery = $query;
return mysql_query($query, $this->link);

}
remember that when in a class and your calling something within the class like a variable then you need to use ‘$this->$variable’ to call that variable, also when creating Methods, for a class is they act like functions and if you declare a varible in the parentheses that it requires that as input for the function/method, function query($query)
Now we can query the DB and have data returned…….but what about inputing data? you can use the same code with out modification, how would you call this code, the great thing is its a one liner $result = $conn->query('your query here'); now lets put a small script together
$conn = new DbConnector();
$result = $conn->query('select * from table');

pretty simple right? but it doesn’t display the info that you retrieved, well that’s for you to decide on how to display or parse the data, In the next few tutorials i will go over things like parsing data and dynamically displaying info depending on what is returned or where it was called from, now back to the rest of the code to expand.

There are 2 more functions in this class that i have added you can iether take em or leave em,
first is converting the result of the query into a workable array.
function fetchArray($result) {
return mysql_fetch_array($result);
}
or you can modify it to just return the array and pull the info right within the class
by
function fetchArray() {
return mysql_fetch_array($this->link);
}

noticed i just removed the $result and replaced it with $this->link which should have the stored return data, but remember you have to call a query first before you can change it into an array. the other function which helped me is one that returns the last row inserted into the DB
function row() {
$pkey = mysql_insert_id($this->link);
return $pkey;
}
and all this does is return the last inserted ID into the table which is very helpful when you need to check for double submit problems, it has other uses as well, just use your imagination.

Lets start another useful class that can be used with the DB connections codes, the whole purpose of this class is to be able to prevent SQL injection into your DB and we will call it libcleaner.php and its based on ‘mysql_real_escape_string()’ which if your security concious you should have at least heard of this built in function, it has been very useful recently when having to deal with user input to my DB
but first we have to create the class to use it in class cleaner{}
now we add our method function cleaner ($toclean) {}
now for the meat and potoes of it $cleaned = mysql_real_escape_string($toclean);
return $cleaned;

very simple right? and to call the code you would include the page include_once $_SERVER['DOCUMENT_ROOT'] . 'application/lib/libcleaner.php';
then add this line to where you need to clean a string, $clean = new cleaner();
$clean->cleaner($string);
or $clean = new cleaner($string); Both ways work its all on personal preference on which way you use it.
till next time when i will go over creating the DB query builder and some other useful snippets and start making an application to use this code in and i will also put up a place for you to download all the scripts and code that are used on these tutorials.