...

View Full Version : Yet Another Database Class



CarlLawl
09-13-2010, 10:02 PM
Ok so below is my version of a DB Class.

CODES:

require_once('cls/mysql.php'); // YOU CAN USE INCLUDE IF YOU WISH
$db = new Mysql();

$db->dbSelect('DBNAME'); // IF NOT SET IN THE CLASS FILE (no need for the connect func)

$db->connect(); // USE THIS IF ALL PARAMS ARE FILLED OUT IN THE CLASS FILE

$db->query('type in your mysql query'); // THIS IS FOR INSERT, UPDATE, DELETE

$db->fetch('type your SELECT mysql query here'); // THIS IS IF YOU ARE FETCHING ROWS

$myVar = $db->getResults(); // WILL PUT YOUR QUERY RESULTS IN A VAR AS ASSOC ARRAY (select queries only)

$myVarTwo = $db->getRows(); // WILL OUTPUT HOW MANY ROWS AS A NUMERIC FIGURE (select queries only)

$db->getPre(); // THIS IS ECHO OUT YOUR QUERY RESULTS FOR DEBUGGING/ERROR CHECKING

So heres the class file I've put together


<?php

class Mysql {

// SETS THE CONNECTION DETAILS
private $db_host = 'localhost';
private $db_user = 'DBUSER';
private $db_pass = 'DBPASS';
private $db_name = 'DBNAME';

// THIS IS WHERE THE QUERY RESULT WILL GO
private $result = array();

// THIS IS WHERE NUM ROWS IS STORED
private $numResults;

// CONNECTS TO DB
public function connect() {

$conn = mysql_connect($this->db_host,$this->db_user,$this->db_pass);

if ($conn) {
$db = mysql_select_db($this->db_name, $conn);

if ($db) {
$this->active = true;
return true;
}
else {
return false;
}
}
else {
return false;
}
}

// SELECTS A DB IF NOT SPECIFIED ABOVE
public function dbSelect($dbSelect) {
$this->db_name = $dbSelect;
$this->connect();
}


// GET VAR, ESCAPES THE STRING, EXEC QUERY, SETS RESULT
public function query($query) {
$query = mysql_escape_string($query);
$q = mysql_query("$query");

if ($q) {
return true;
}
else {
return false;
}
}

// FETCHES QUERY ROWS FROM DB
public function fetch($query) {
$query = mysql_escape_string($query);
$q = mysql_query("$query");

if ($q) {
$this->numResults = mysql_num_rows($q);

if ($this->numResults != 0) {
while ($f = mysql_fetch_assoc($q)) {
$this->result[] = $f;
}
}
else {
$this->result = null;
}

return true;
}
else {
return false;
}
}

// SENDS THE RESULT BACK
public function getResults() {
return $this->result;
}

// ECHOS OUT THE QUERY ARRAY RESULTS IN A READABLE FORMAT
public function getPre() {
echo '<pre>';
print_r($this->result);
echo '</pre>';
}

// SENDS THE NUM ROWS BACK
public function numResults() {
return $this->numResults;
}

}
?>

It also works in arrays for example.


if ($db->query) { } // IF YOUR QUERY IS INCORRECT OR FAILS IT WOULD RETURN BACK FALSE CARRYING OUT AN ELSE STATEMENT IF NEED BE
else { }

idalatob
09-14-2010, 09:32 AM
Can i make a suggestion? Im sure i can :-D

Why dont you try changing your class so that it works something like this:



//syntax is a little complicated
//feel free to create your own!
$db->query(array(
"type"=>"select",
"attribs"=>array(
"table"=>'users',
"fields"=>array('name','surname','id_number','cellphone'),
"conditions"=>array("name"=>"='john'","surname"=>"='doe'"),
"limit"=>array(0,30),
"order"=>array("name"=>'d',surname=>'a')
)));
//then you get your class to format it like this:
SELECT name,surname,id_number,cellphone FROM users WHERE name='john' AND surname = 'doe' ORDER BY name ASC, surname DESC LIMIT 0,30;


That way, and this is just a suggestion, you can make your class cross-database-type compatible and its a nice challenge!

CarlLawl
09-14-2010, 11:44 AM
Something like this? (Ps I didnt have time to check it as I have work soon but it should be ok).


<?php

class Query {

private $result = '';
private $numResults = '';

public function query($data) {

switch($data[type]) {
case select:

$q = $data[type].' '.$data[attr][fields].' FROM '.$data[attr][table];

if ($data[attr][where] != '') {
$q .= 'WHERE '.$data[attr][where];
}

if ($data[attr][limit] != '') {
$q .= 'LIMIT '.$data[attr][limit];
}

if ($data[attr][order] != '') {
$q .= 'ORDER BY '.$data[attr][order];
}
$q = mysql_query("$q");

if ($q) {
$this->numResults = mysql_num_rows($q);

if ($this->numResults != 0) {
while ($f = mysql_fetch_assoc($q)) {
$this->result[] = $f;
}
}
else {
$this->result = null;
}

return true;
}
else {
return false;
}
break;

case update:

$q = $data[type].' '.$data[attr][table].' SET '.$data[attr][values];

if ($data[attr][where] != '') {
$q .= ' WHERE '.$data[attr][where];
}
$q = mysql_query("$q");

if ($q) {
return true;
}
else {
return false;
}
break;

case delete:

$q = $data[type].' FROM '.$data[attr][table].' WHERE '.$data[attr][where];
$q = mysql_query("$q");

if ($q) {
return true;
}
else {
return false;
}
break;

case insert:

$q = $data[type].' INTO '.$data[attr][table].' '.$data[attr][fields].' VALUES '.$data[attr][values];
$q = mysql_query("$q");

if ($q) {
return true;
}
else {
return false;
}

break;
}

}

public function getResults() {
return $this->result;
}

public function GetNumRow() {
return $this->numResults;

}

?>

Usage:


$dataSelect = array('type' => 'SELECT',
'attr' => array('fields' => '*',
'table' => 'MyTable',
'where' => 'id=3',
'limit' => '1',
'order' => 'id'
));

$dataUpdate = array('type' => 'UPDATE',
'attr' => array('table' => 'MyTable',
'values' => 'col1=value, col2=value',
'where' => 'col1=somevalue'
));

$dataDelete = array('type' => 'DELETE',
'attr' => array('table' => 'MyTable',
'where' => 'id=3'
));

$dataInsert = array('type' => 'INSERT',
'attr' => array('table' => 'MyTable',
'fields' => '(field1,field2,field3)',
'values' => '(value1,value2,value3)',
));

require_once('cls/query.php'); // YOU CAN USE INCLUDE IF YOU WISH
$q = new Mysql();

$q->query($dataSelect); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
$q->query($dataUpdate); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
$q->query($dataDelete); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION
$q->query($dataInsert); // DATA FROM ARRAY ABOVE GETS PASSED TO FUNCTION

$nrows = $q->getNumRow(); // IF YOU WERE COUNTING NUM ROWS

$r = $q->getResults(); // IF YOU WERE PULLING DATA FROM DB

apologise for the horrid array indents they looked fine in the editor



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum