MySQL Database PHP Class

Custom PHP Class for manipulating MySQL. Simple to follow, and to use.

<?php

class Database {
public function connect($host, $name, $pass) {
$connection = mysql_connect($host, $name, $pass) or die(mysql_error());
return $connection;
}
public function usedatabase($databasename) {
return mysql_select_db($databasename) or die(mysql_error()."method usedatabase");
}

public function selectdata($tablename, $fields) {
$sql = ("SELECT $fields FROM $tablename");
$query = mysql_query($sql) or die(mysql_error()."method selectdata 1");

if (!$query) {
return "Could not successfully run query ($sql) from DB: " . mysql_error()."method selectdata";
exit;
}

if (mysql_num_rows($query) == 0) {
return 0;
exit;
}
if(preg_match("/,/", $fields)) {
$fields = explode(",", $fields);
#print_r($fields);
}
while ($row = mysql_fetch_assoc($query)) {
if (is_array($fields)) {
foreach ($fields as $field) {
$rezultati[$field][] = $row[$field];
}
}
else {
$rezultati[$fields][] = $row[$fields];
}

}
mysql_free_result($query);
return $rezultati;
}

public function selectwhere($tablename, $fields, $where) {
$sql = ("SELECT $fields FROM $tablename $where");
$query = mysql_query($sql) or die(mysql_error()."select where 1");

if (!$query) {
return "Could not successfully run query ($sql) from DB: " . mysql_error()."select where 2";
exit;
}

if (mysql_num_rows($query) == 0) {
return 0;
exit;
}
if(preg_match("/,/", $fields)) {
$fields = explode(",", $fields);
#print_r($fields);
}
while ($row = mysql_fetch_assoc($query)) {
if (is_array($fields)) {
foreach ($fields as $field) {
$rezultati[$field] = $row[$field];
}
}
else {
$rezultati[$fields] = $row[$fields];
}

}
mysql_free_result($query);
return $rezultati;
}

public function selectallwhere ($tablename, $fields, $where) {
$sql = ("SELECT $fields FROM $tablename $where");
$query = mysql_query($sql) or die(mysql_error()."select where 1");

if (!$query) {
return "Could not successfully run query ($sql) from DB: " . mysql_error()."select where 2";
exit;
}

if (mysql_num_rows($query) == 0) {
return 0;
exit;
}
if(preg_match("/,/", $fields)) {
$fields = explode(",", $fields);
#print_r($fields);
}
while ($row = mysql_fetch_assoc($query)) {
if (is_array($fields)) {
foreach ($fields as $field) {
$rezultati[$field][] = $row[$field];
}
}
else {
$rezultati[$fields][] = $row[$fields];
}

}
mysql_free_result($query);
return $rezultati;
}

public function leftjoin($lefttable, $righttable, $matchedrow, $fields, $order) {
$sql = ("SELECT $fields FROM $lefttable LEFT JOIN $righttable ON $lefttable.$matchedrow = $righttable.$matchedrow $order");
$query = mysql_query($sql) or die(mysql_error()."method selectdata left join");

if (!$query) {
return "Could not successfully run query ($sql) from DB: " . mysql_error()."method selectdata";
exit;
}

if (mysql_num_rows($query) == 0) {
return 0;
exit;
}

if(preg_match("/,/", $fields)) {
$fields = explode(",", $fields);
#print_r($fields);
}
while ($row = mysql_fetch_assoc($query)) {
if (is_array($fields)) {
foreach ($fields as $field) {
$rezultati[$field][] = $row[$field];
}
}
else {
$rezultati[$fields][] = $row[$fields];
}

}


mysql_free_result($query);
return $rezultati;

}

public function insertdata($tablename, $fields, $values) {
return mysql_query(
"INSERT INTO $tablename ($fields) VALUES ($values)"
)
or die(mysql_error());
}
public function update($tablename, $field, $value, $where) {
return mysql_query("UPDATE $tablename SET $field = $value $where") or die(mysql_error());
}
public function newupdate($tablename, $values, $where) {
return mysql_query("UPDATE $tablename SET $values $where") or die(mysql_error());
}
public function delete($tablename, $where) {
return mysql_query("DELETE FROM $tablename $where");
}
}

#usage
$mysql = new Database();
$mysql->connect("localhost", "username", "password");
$mysql->usedatabase("test");

$selectdata = $mysql->selectdata("tablename", "tablefield1,tablefield2");
$selectallwhere = $mysql->selectallwhere("tablename", "tablefield1,tablefield2,tablefield3", "where status='1'");

#class outputs multidimenstional array so you can access values like this

print_r($selectdata['tablefield1']);
print_r($selectallwhere);

#try and see the difference

?>

What if you want all of the fields and you have like 20 of them? Using a wildcard would be easiest but there would be no comma in your $fields variable. Would that break things?

if by wildcard you mean about *, well, yeah, i thought that this code of mine covers that stuff, at least selectdata method, but i guess, now and there while i came further with this class for some reason i forgot about * wildcard.

i remember i solved it with preg_replace, but i am really really not sure were results disposed as they are now. anyway in 16kb of code, i never ever had to use wildcard * :(

I don't want to be all too critical in my very first post on this board, but still; there's some things I don't really understand looking at your code.

* First of all; why don't you use MySQLi? It supports prepared statements to begin with, and is OOP. It has numerous other advantages, to be read here: http://stackoverflow.com/questions/1171344/advantages-of-mysqli-over-mysql
* Why do you echo in a class (die(mysql_error())? Saving the error and returning it at some point would be way more convenient.
* Why would you want to kill your script? Not very convenient in a production environment. Returning the error back to the class/file that's asked to execute a query would be better, in my opinion. Then you grant yourself the possibility of logging the error and give the user a normal message back without the page being screwed up by die().
* Your if(!$query) will never ever do a thing, as the script has died before.
* In case you need to execute a slightly more complicated query, you still have to do it apart from this class, as it doesn't support subquery's (for example).
* Last but not least; why don't you sanitize your input? It's very - no, VERY - easy to destroy your entire database with the code you've written. (SELECT DELETE FROM x WHERE y = z... etc.)

Just my 2 cents. :)

i have a new approach to database class now, but i will consider your 2 cents, and see what i can do about them.

thanks

if you have any suggestions, please, feel free to post them :)

Take a look at the code provided in the book at http://www.wrox.com/WileyCDA/WroxTitle/Professional-PHP5.productCd-0764572822,descCd-DOWNLOAD.html

Unzip the downloaded file and open files class.Database.php and DB_test.php ch08. Not a complete one, as there's no support to use joins in queries other than SELECT, though you may get a different way of passing fields and their values.

Does it handle stored procedures? I can't imagine using inline sql everywhere in an application.

thank you for the posted code abduraooft. i can see already now, almost maybe a month after writing that class that it can be done quite a bit different. now for this CMS, of some sort, i'm using new database approach with less similar queries, and i do get some results. but database class for me, is still pretty hard coded so i'm satisfied even if all i need to do is add a mathod...
dunno, maybe i have to much time on my hand, since i did read a lot about classes, and stuff, but still, even if you do something bad, when you are using classes it's much much more simpler to change it to better code then by using procedures

xml + database + some php is new approach ;)