Posted By

emuen on 05/27/07

Tagged

Versions (?)

Last Edited at 05/27/07 06:25am

Statistics

Viewed 4492 times

Favorited by 22 user(s)

Related snippets

MYSQL Database Class

/ Published in: PHP

This class operates a range of mysql functions base on arrays.

Example:

Name:
Description:

Highlighted TextExpand

<?php

/**
 * Database class
 * 
 * @version: 2.2
 * @author: Emil T. Kampp <emil@kampp-productions.dk>
 * @revised: 27 may 2007
 * 
 **/

class Database {
	var $host;
	var $name;
	var $user;
	var $pass;
	var $prefix;
	var $linkId;
	
	function Database($mysql) {
		foreach($mysql as $k => $v){
			$this->$k = $v;
		}
		if(strlen($this->prefix)>0 && substr($this->prefix, -1) !== "_") $prefix .= "_";
		$this->prefix = $prefix;
	}
	
	function getLastID() {
		$id = mysql_fetch_row(mysql_query("SELECT LAST_INSERT_ID()", $this->linkId));
		return $id[0];
	}
	
	function getPossibleValues($tableA, $whereA) {
		if(gettype($tableA) == "array") {
			$table = "";
			foreach($tableA as $t) {
				$table .= $this->prefix.$t.", ";
			}
			$table = substr($table, 0, -2);
		} else $table = $this->prefix.$tableA;
		if(gettype($whereA) != "array") {
			$whereA = array($whereA);
		}
		$return = array();
		foreach($whereA as $where) {
			$sql = mysql_query("SHOW COLUMNS FROM ".$table." LIKE '%".$where."%'");
			while($arr = mysql_fetch_array($sql)) {
				if(strpos($arr['Type'], 'enum')===0) {
					$vals = substr($arr['Type'], 5, -1);
				} else {
					$vals = substr($arr['Type'], 4, -1);
				}
				$vals = str_replace("'","",$vals);
				$vals = explode(",",$vals);
				$i = 1;
				foreach($vals as $val) {
					$return[$arr['Field']][$i++] = $val;
				}
				$return[$arr['Field']]['default'] = $arr['Default'];
				if($arr['Null'] != "NO") $return[$arr['Field']][0] = NULL;
			}
		}
		return $return;
	}
	
	function connect() {
		$this->linkId = mysql_connect($this->host, $this->user, $this->pass);
		if(!$this->linkId) {
			return false;
		}
		if(mysql_select_db($this->name, $this->linkId)) return true;
		mysql_close($this->linkId);
		return false;
	}
	
	function runSelect($tables, $where = "1", $fieldsA = "*", $order = false, $limit = false, $offset = false, $group = false) {
		if(gettype($tables) == "array") {
			$table = "";
			foreach($tables as $t) {
				$table .= $this->prefix.$t.", ";
			}
			$table = substr($table, 0, -2);
		} else $table = $this->prefix.$tables;
		if(gettype($fieldsA) == "array") {
			$fields = "";
			$keys = array_keys($fieldsA);

if($keys[0] != '0') {
				foreach($keys as $key) {
					$fields .= $key.' AS '.$fieldsA[$key].', ';
				}
			} else {
				foreach($fieldsA as $field) {
					$fields .= $field.', ';
				}
			}
			$fields = substr($fields, 0, -2);
			
		} else $fields = $fieldsA;
		$query = "SELECT ".$fields." FROM ".$table." WHERE ".$where.
			($order!== false?" ORDER BY ".$order:($group!==false ? " GROUP BY ".$group : "")).
			($limit !== false?" LIMIT ".$limit:"").
			($offset !== false?" OFFSET ".$offset:"");

return mysql_query($query, $this->linkId);
	}
	
	function runUpdate($table, $valuesA, $where = "1") {
		if(gettype($valuesA) == "array") {
			$fields = "";
			$values = "";
			$keys = array_keys($valuesA);
			foreach($keys as $key) {
				if($valuesA[$key] !== NULL)
					$values .= "`".$key."`='".str_replace("'",'\'', $valuesA[$key])."',";
				else
					$values .= $key."=NULL,";
			}
			$fields = substr($fields, 0, -1);
			$values = substr($values, 0, -1);
		} else $values = $valuesA;
		$query = "UPDATE ".$this->prefix.$table." SET ".$values." WHERE ".$where;

if(mysql_query($query, 
				$this->linkId))
			return mysql_affected_rows($this->linkId);
		return false;
	}
	
	function runDelete($table, $where = "1") {
		if(mysql_query("DELETE FROM ".$this->prefix.$table." WHERE ".$where, $this->linkId))
			return mysql_affected_rows($this->linkId);
		return false;
	}
	
	function runInsert($table, $valuesA, $onDuplicate = NULL) {
		if(gettype($valuesA) == "array") {
			$fields = "";
			$values = "";
			$keys = array_keys($valuesA);
			foreach($keys as $key) {
				$fields .= "`".$key."`, ";
				$values .= ($valuesA[$key]===NULL?"NULL, ":"'".str_replace("'", '\'', $valuesA[$key])."', ");
			}
			$fields = substr($fields, 0, -2);
			$values = substr($values, 0, -2);
		}
		
		$onDup = "";
		if($onDuplicate != NULL) {
			$onDup = " ON DUPLICATE KEY UPDATE ";
			if(gettype($onDuplicate) == "array") {
				$keys = array_keys($onDuplicate);
				foreach($keys as $key) {
					$onDup .= '`'.$key.'`='.($onDuplicate[$key]===NULL?"NULL,":"'".str_replace("'", '\'', $onDuplicate[$key])."', ");
				}
				$onDup = substr($onDup, 0, -2);
			} else $onDup .= $onDuplicate;
		}
		$query = "INSERT INTO ".$this->prefix.$table.($fields!==NULL?"(".$fields.")":"").
			" VALUES (".$values.")".$onDup;

if(mysql_query($query, $this->linkId)) 
			return mysql_affected_rows($this->linkId);
		return false;
	}
	
	function getCells($table){
		$query = "SHOW COLUMNS FROM `".$table."`";
		$fields = mysql_query($query, $this->linkId) or die('hej');
		return $fields;
	}
	
	function translateCellName($cellName){
		$sql = $this->runSelect("mysql_cell_translation","mysql_name = '".$cellName."'");
		$row = mysql_fetch_assoc($sql);
		return $row['human_name']?$row['human_name']:'<span class="faded">['.$cellName.']</span>';
	}
	
	function getError() {
		return mysql_error($this->linkId);
	}
	
	function close()
	{
		mysql_close($this->linkId);
	}
}
?>

URL: http://kampp-productions.dk/functions/examples/database.php

Report this snippet Tweet