Pizentios

Hey,

i made this to save some typing when connecting to a postgresql database. I havn't updated the code in a while, so there is probally some things that could be improved.

dbobj.php
[php]

///////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This file is an object that handles any and all db operations that you may //
// need. //
//Comments: If your going to change any of this code, please send a copy to: //
// mutherfucker@crcw.mb.ca //
// or //
// pizentios@gmail.com //
// Date Created: 04/20/04 //
// Date Finished: 04/21/04 //
///////////////////////////////////////////////////////////////////////////////////////////

Class dbobj {

//////////////////////////////////////////////////////////////////////////////////////////
//************************************Varibles******************************************//
//////////////////////////////////////////////////////////////////////////////////////////

var $user;
var $dbname;
var $pass;
var $connection;
/////////////////////////////////////////////////////////////////////////////////////////
//***********************************Properties****************************************//
/////////////////////////////////////////////////////////////////////////////////////////

function setUser($u)
{
//////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you set the user name that you are going to be //
// connecting as. //
// Pre: You need to send me the user name that you would like to set $user too. //
// Post: Returns nothing, but it does set the user var to the user name that you//
// wish. //
// Date Created: 04/20/04 //
// Date Finished: 04/20/04 //
//////////////////////////////////////////////////////////////////////////////////
$this->user=$u;
}

function getUser()
{
//////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you see what user you are connecting to the db as//
// Pre: The user var has to be set. //
// Post: Returns the user name. //
// Date Created: 04/20/04 //
// Date Finished: 04/20/04 //
//////////////////////////////////////////////////////////////////////////////////

return $this->user;
}

function setDBname($name)
{
//////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you set the database name that you want to //
// connect too. //
// Pre: You need to send a dbname. //
// Post: Sets the dbname var to what you send to me. //
// Date Created: 04/20/04 //
// Date Finished: 04/20/04 //
//////////////////////////////////////////////////////////////////////////////////
$this->dbname=$name;

}

function getDBname()
{
//////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you grab the dbname that you set. //
// Pre: Nothing needs to be sent, although it will return nothing if the dbname //
// hasn't been set. //
// Post: Returns the dbname var. //
// Date Created: 04/20/04 //
// Date Finished: 04/20/04 //
//////////////////////////////////////////////////////////////////////////////////
return $this->dbname;

}

function setPass($p)
{
//////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you set the password that you want to use to //
// connect to the db. //
// Pre: You need to send the password that you want to set to me. //
// Post: Returns nothing, however it does set the password to the pass var. //
// Date Created: 04/20/04 //
// Date Finished: 04/20/04 //
//////////////////////////////////////////////////////////////////////////////////
$this->pass=$p;

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////
//*********************************************Methods***************************************************//
///////////////////////////////////////////////////////////////////////////////////////////////////////////


function conn()
{
////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function makes the connection to the db that you have set up for //
// Pre: Nothing needs to be sent here, although everything needs to be set in //
// order for this function to complete it's task. //
// Post: Returns True if the connection is good. False if it is not. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
////////////////////////////////////////////////////////////////////////////////////

if ($this->pass <> "")
{
$constr = "dbname={$this->dbname} user={$this->user} pass={$this->pass}";
}
else
{
$constr = "dbname={$this->dbname} user={$this->user}";
}
$this->connection = pg_connect($constr);

if ($this->connection <> false)
{
return true;
}
else
{
return false;
}
}

function dbSelect($what, $from, $where)
{
/////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you do a select from the db that you just connected //
// too. //
// Pre: The connection has to be made, also you need to send at least the what //
// and from vars for this function to run. //
// Example: if you wanted to select all cats from the table called cats: //
// dbSelect("*", "cats", ""); //
// if you wanted to select all the cats from the table called cats //
// that are red: //
// dbSelect("*", "cats", "color=red"); //
// Post: Returns the record set. Returns false if it blew up. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
/////////////////////////////////////////////////////////////////////////////////////

if ($where == "")
{
$sql = "SELECT {$what} FROM {$from}";
}
else
{
$sql = "SELECT {$what} FROM {$from} WHERE {$where}";
}
$res = pg_query($sql);

if ($res <> false)
{
return $res;
}
else
{
return false;
}
}

function dbInsert($into, $colums, $values, $where)
{
///////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you do a insert into the selected db. //
// Pre: you need to have a connection and send the following items: //
// $into: the name of the table that you want to insert into. //
// $colums: The name of the colums that you want to insert the data into. Send a//
// "" if you don't need to spesifiy the colums that you want to insert //
// into. //
// $values: The actual values that you are wanting to insert. //
// $where: The where clause of a insert. Send a "" if you don't need to do a //
// where clause. //
// Exmaple: dbInsert("cats", "", "'brown', 'big', 'green'", "Size='small'"); //
// to insert into table cats the values brown, big, green where the //
// size is = small. //
// Post: Returns true if the insert was a sucess. False if it failed. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
///////////////////////////////////////////////////////////////////////////////////////

if ($colums <> "" AND $where <> "")
{
$sql = "INSERT INTO " . $into . " (" . $colums . ") VALUES (" . $values . ") WHERE " . $where . ";";
}
elseif ($colums <> "" AND $where == "")
{
$sql = "INSERT INTO " . $into . " (" . $colums . ") VALUES (" . $values . ");";
}
elseif ($colums == "" AND $where <> "")
{
$sql = "INSERT INTO " . $into . " VALUES (" . $values . ") WHERE " . $where . ";";
}
else
{
$sql = "INSERT INTO " . $into . " VALUES (" . $values . ");";
}
$res = pg_query($this->connection, $sql);

if ($res <> false)
{
return true;
}
else
{
return false;
}
}

function dbUpdate($table, $set, $where)
{
/////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you update a row of data. //
// Pre: You need to send the table that you want to update, the colums and the values //
// that you would like to set, and a where clause. If you don't want to use a //
// where clause just go like this "". //
// Post: Updates a row(s) of data. Returns true if it worked, false if it didn't. //
// Date Created: 05/06/04 //
// Date Finished: 05/06/04 //
/////////////////////////////////////////////////////////////////////////////////////////
if ($where <> "")
{
//then we are using a where clause.
$sql = "UPDATE " . $table . " SET " . $set . " WHERE " . $where;
}
else
{
//then we are not using the where clause.
$sql = "UPDATE " . $table . " SET " . $set;
}
$res = pg_query($this->connection, $sql);

if ($res <> false)
{
return true;
}
else
{
return false;
}
}


function dbDelete($table, $where)
{
//////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you delete row(s) out of a table. //
// Pre: you need to have the connection set up and you need to send the table that you //
// want to delete out of. WARNING: If you send a "" as where then it'll delete //
// everything out of the table. you need to send somthing in the where if you want //
// to prevent this, unless that's what your trying to do. //
// Post: Returns True if it worked, false if it didn't. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
//////////////////////////////////////////////////////////////////////////////////////////
if ($where <> "")
{
$sql = "DELETE FROM " . $table . " WHERE " . $where . ";";
}
else
{
$sql = "DELETE FROM " . $table . ";";
}

$res = pg_query($this->connection, $sql);

if ($res <> false)
{
return true;
}
else
{
return false;
}
}

function dbDrop($table)
{
////////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you drop a table. //
// Pre: You need a connection and you need to send the name of the table that you want to //
// delete...WARNING: this cannot be undone. //
// Post: Returns true if it worked, false if it didn't. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
////////////////////////////////////////////////////////////////////////////////////////////

$sql = "DROP TABLE ". $table . ";";

$res = pg_query($this->connection, $sql);

if ($res <> false)
{
return true;
}
else
{
return false;
}
}

function anysql($sql)
{
/////////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function lets you run any other sql that you might need to run. //
// Pre: You need a connection, and you need to send a FULL sql string. //
// Post: Returns the results if it worked. returns false if it didn't. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
/////////////////////////////////////////////////////////////////////////////////////////////
$res = pg_query($this->connection, $sql);

if ($res <> false)
{
return $res;
}
else
{
return false;
}
}

function connClose()
{
///////////////////////////////////////////////////////////////////////////////////////////////////
// Purpose: This function closes the conenction. //
// Pre: The connection has to been made. //
// Post: Returns true if it worked, false if it didn't. //
// Date Created: 04/21/04 //
// Date Finished: 04/21/04 //
///////////////////////////////////////////////////////////////////////////////////////////////////
$res = pg_close($this->connection);

if ($res <> false)
{
return true;
}
else
{
return false;
}
}
}

?>
[/php]

Here's an example on how to do a select with the object.

[php]
$db = new dbobj();
$db->setUser("postgres");
$db->setDBname("mydata");
$cats = array();
if($db->conn())
{
$res = $db->dbSelect("name", "cats", "color='blue'");
if ($res)
{
//loop through and dump the data into a array
for ($x=0; $x
{
$data = pg_fetch_array($res, $x);
$cats[$x] = $data["name"];
}
}
else
{
//query error message here.
}
}
else
{
//db error message here.
}
?>
[/php]

So that would return all the cats "name" that are of the color blue. Then you could loops through the cats array and output the names, or you could output them strait to the screen inside the for loop. i usally use pg_fetch_object() but for this example i desided to use pg_fetch_array(). Anyways, like a said before, this code probally could be cleaner since it was written a while ago.

__________________
Profanity is the one language that all programmers understand.

Check out my Blog <---updated Nov 30 2007!

big_k105

does this automaticly know the host is localhost? thats what im guessing but if say i was goin to run an application that uses this on a different server then the postgreSQL db then i would want to add a new var and 2 new functions called setHost and getHost thought i would double check to make sure

__________________
BIG K aka Kyle
Programming Forums
Kyle K Online

Please do not PM or email me programming questions. Post them in the forums instead.

Mad_guy

Actually I just thought I'd tell you, he wouldn't need seperate member functions for that. The connection could automatically set localhost as the default connection if it's not specified. By setting $host or whatever as localhost at the member functions' definition point.

Actually this inspired me to write a PHP MySQL intergration class, here's a section of how to do that (for some weird reason vBulletin puts that weird space inbetween 'localhost'):[php] function cMySQL($username,$userpass,$db=FALSE,$host='localhost')
{
$this->cntlink = mysql_connect($host,$username,$userpass);
if($db != FALSE)
{
$this->currentdb; = mysql_select_db($db,$this->cntlink);
}
}
function selectDB($database)
{
$this->currentdb = mysql_select_db($database,$this->cntlink);
}[/php] Actually the member function cMySQL is the class constructor initiating the link. If no IP is passed, it assumes localhost is where the DB server is. You can also use that constructor to automatically select a database if you want and use the member selectDB to switch to another DB.

An example of this in action would be something like:[php]<?
require("cMySQL.php");
$cnt1 = new cMySQL("user","pass","db1","123.4.5.9");
//continue with code...
?>[/php]
Or, alternitavely:[php]<?
require("cMySQL.php");
$cnt1 = new cMySQL("user","pass");
//This will make the script assume the DB server is on localhost
$cnt1->selectDB("stuff");
?>[/php]

Just thought I'd inform you that you can do that to save yourself from making member functions that are ultimately pointless.

I thank my friends for showing me that trick.

Infinite Recursion

mad_guy: by chance would your entire PHP MySQL intergration class be up for grabs somewhere?

__________________
http://jasonpowers.net

"There are a thousand hacking at the branches of evil to one who is striking at the root."

Mad_guy

Truthfully I don't think I have it anymore, reformatted totally screwed my docs over. But I was generally lazy with it, and it only had basic functions, so reinventing the code wouldn't be hard.


In fact I could probably whip up the entire framework I previously had in around 30 minutes.

Infinite Recursion

No worries man...

__________________
http://jasonpowers.net

"There are a thousand hacking at the branches of evil to one who is striking at the root."

Pizentios

PFO almost needs a section for posting finished tools like this, that's easier to search through than the forums.

__________________
Profanity is the one language that all programmers understand.

Check out my Blog <---updated Nov 30 2007!

Infinite Recursion

yeah... I'm working out a code archive on my site... trying to get a good design before I go live with it.

__________________
http://jasonpowers.net

"There are a thousand hacking at the branches of evil to one who is striking at the root."

Pizentios

that's cool

__________________
Profanity is the one language that all programmers understand.

Check out my Blog <---updated Nov 30 2007!

big_k105

now that its summer and i get more time i plan on working on a full CMS for pfo that will be built around vB and it will consist of a good code snippet section, a good tutorial section and a good article section(esp if kurifu wants to keep writing )

__________________
BIG K aka Kyle
Programming Forums
Kyle K Online

Please do not PM or email me programming questions. Post them in the forums instead.