Board index   FAQ   Search  
Register  Login
Board index PHP Your Scripts

Simple mySQL Database Controller

This is where you share YOUR scripts with others

Moderators: macek, egami, gesf

Simple mySQL Database Controller

Postby xeniar » Thu Feb 02, 2012 3:15 pm

Code: Select all
<?php
/*-------------------------------------------
mySQL Database Interface v2.1
Coded by: Jon Cox (virusx117@gmail.com)
This code can be used by anyone,
all I ask is that you leave this here.

This class does all the major junctions required by a SQL database (SELECT, DELETE, INSERT, UPDATE,
DROP TABLE, TRUNCATE). To use this classes functions, simply create a new database class like so:
$db_interface = new database;
Then use the "->" operator to call the nested functions like so: $db_interface->select();

Note there are multiple functions that all use the mySQL SELECT function, however, they are very
different in what way they get their data, please read the comments in the first line of each
function to learn more about it.
--------------------------------------------*/
if(!class_exists(database)){
   class database {
      private $dbhost = "localhost";//Change this to your servers root address
      private $dbuser = "root";//Change this to your database username
      private $dbpass = "";//Change this to the password associated with the username you supplied.
      private $dbname = "";//Change this to the name of your database

      //=====================================================================================================
      //DO NOT EDIT BEYOND THIS POINT!                                         DO NOT EDIT BEYOND THIS POINT!
      //=====================================================================================================

      public function __construct() {
         /*Set up DB connection info once Database Class is made*/
         $this->dbconn = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass) or die(MYSQL_ERROR());
         mysql_select_db($this->dbname,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function select($table,$column,$value){
         /*Selects all columns from each row returned*/
         $sql = "SELECT * FROM `$table` WHERE `$column` = \"$value\"";
         $q = mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
         if(mysql_num_rows($q) == 0){return false;}
         else{return mysql_fetch_array($q,MYSQL_ASSOC);}
      }
      public function selectAll($table){
         /*Returns the entire table as a multidimensional array*/
         $sql = "SELECT * FROM `$table`";
         $q = mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
         if(mysql_num_rows($q) == 0){return false;}
         else{
            for($x=0;$x != mysql_num_rows($q);$x++){
               $result[] = mysql_fetch_array($q,MYSQL_ASSOC);}
            }
         return $result;
      }
      public function selectCols($table,$col){
         /*If desired columns is greater then 1, result will return a multi-dimensional array
         with each element in the first array is a column name, with it having an array holding the data
         E.G., selectCols("table",array("column1","column2")) will return something like:
         $return = array(
         "column1"=>array("0"=>"Data1","1"=>"Data2")
         "column1"=>array("0"=>"Data1","1"=>"Data2"));

         Single columns will only rturn a single dimensional array like so:
         $return = array("0"=>"Data1","1"=>"Data2");

         */
         end($col);$endcol=current($col);reset($col);
         foreach($col as $var){
            $cols .= "`$var`";
            if($var != $endcol){ $cols.= ",";}
         }
         $sql = "SELECT $cols FROM `$table`";
         $q = mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
         if(mysql_num_rows($q) == 0){return false;}
         else{
            if(count($col) == 1){
               while($r = mysql_fetch_array($q,MYSQL_ASSOC)){
                  $result[] = current($r);
               }
            } else {
               while($r = mysql_fetch_array($q,MYSQL_ASSOC)){
                  foreach($col as $var){
                     $result[$var][] = $r[$var];
                  }
               }
            }

         }
         return $result;
      }
      public function delete($table,$column,$value){
         /*Removes an entire row of data where $column is equal to $value*/
         $sql = "DELETE FROM `$table` WHERE `$column` = \"$value\"";
         mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function insert($table,$data){
         /*Adds in a new row of data using the $data array as input information*/
         end($data);$endkey = key($data);reset($data);
            foreach($data as $key => $value){
               $columns[] = "`$key`";
               $values[] = "'$value'";
            }
            $columns = implode(",",$columns);
            $values = implode(",",$values);

         $sql = "INSERT INTO `$table` ($columns) VALUES ($values)";
         mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function update($table,$data,$column,$value){
         /*Updates an existing row of data with new data from the $data array where $column = $value*/
         end($data);   $endkey = key($data); reset($data);
            foreach($data as $key => $val){
               $pairs .= "`$key` = \"$val\"";
               if($key != $endkey){$pairs .= ", ";}
            }
         $sql = "UPDATE `$table` SET $pairs WHERE `$column` = \"$value\"";
         mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function dropTable($table){
         /*Completely removes an entire data table and all data inside it*/
         $sql = "DROP TABLE `$table`";
         mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function truncate($table){
         /*Completely empties a data table of all data inside it, but the table itself stays intact.*/
         $sql = "TRUNCATE `$table`";
         mysql_query($sql,$this->dbconn) or die(MYSQL_ERROR());
      }
      public function duplicateRow($table,$column,$value){
         /*This will locate the desired row(s) and make duplicate entries of them.
         Note that if you have a Unique column specified in your table then this
         function will NOT work! Columns with auto_increment will behave as
         expected.*/
         $original = $this->select($table,$column,$value);
         foreach($original as $key => $val){
            $data2[$key] = $val;
         }
         $this->insert($table,$data2);
      }
   }
}



Hello all,

I made this script a few months ago because I was tired of constantly writing out full functions multiple times in one script to do anything through mySQL DB. The above code is a Class Definition that contains multiple DB interface functions. They are as follows:

(NOTE: That script is only a Class Definition. You still need to instatiate it into an Object)

select($table,$column,$value)
This function will return an array populated with an entire row of data from $table where $column equals value.

selectAll($table)
This function will return a multidimensional array containing all the data in $table

selectCols($table,$col)
This function will select all Columns specified in $col from $table, no conditional statement.
$col must be an array of only values of column names like this: $col = array("name1", "name2");

Returns a multidimensional array like this: array("column1"=>array("data1","data2"),"coulmn2"=>array("data1"));

delete($table,$column,$value)
This function will delete an entire row of data from $table where $column equals $value
No return value.

insert($table,$data)
This function will insert a new row of data from the $data array into $table.
The keys of the $data array are the column names, and their values are the data you want to insert under that column.
No return value

update($table,$data,$column,$value)
This function will update a pre-existing row of data from $table with new data from $data where $column equals $value.
The $data array is set up just like it is in insert(). Keys are column names, their values are the replacement data.
No return value

droptable($table)
This will remove $table and all data contained within from your database.
No return value

truncate($table)
This will completely empty $table of all data but the table itself will remain untouched.
No return value.

duplicateRow($table,$column,$value)
This will create an exact copy of a row of data from $table where $column equals $value
NOTE: If you have any type of unique index in $table then this function will not work!
No return value.
xeniar
New php-forum User
New php-forum User
 
Posts: 67
Joined: Tue Jan 24, 2012 1:54 pm

Return to Your Scripts

Who is online

Users browsing this forum: No registered users and 0 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.