Tag Archive for database

Utilisation de mysqli (syntaxe objet)

<?php

/* how to create connexion (4th parameter = default database) */
$mysqli = new mysqli($host, $usr, $pass, $db);

/* how to verify connexion */
if (mysqli_connect_errno()) {
    printf("Echec de la connexion : %s
", mysqli_connect_error());
}

/* how to set the default charset to use */
if (!$mysqli->set_charset("utf8")) {
   printf("Erreur lors du chargement du jeu de caractères utf8 : %s
", $mysqli->error);
}

/* how to use real_escape_string on string */
$nom = $mysqli->real_escape_string($nom);
$mail = $mysqli->real_escape_string($mail);
$tel = $mysqli->real_escape_string($tel);


/*how to get data in a table with a while */
$query = "SELECT nom, mail, tel FROM ufr_jury ORDER by nom;";
$ressource = $mysqli->query($query);
while($result = $ressource->fetch_assoc()){
						
	echo "<tr>";
							
	echo "<td>".$result['nom']."</td>";
	echo "<td>".$result['mail']."</td>";
	echo "<td>".$result['tel']."</td>";
							
	echo "</tr>";

}

/* how to free result ressource */
$ressource->close();
						
/* how to close connection */
$mysqli->close();

?>

source

LoneDatabaseClass

/*

LoneDatabaseClass * PHP SQL class for MySQL, Oracle and MS SQL
ref: lonedb  ver: 0.5  date:20100305
Copyright (c) 2009 Milan Stankovic
<a href="mailto:semidot@gmail.com">semidot@gmail.com</a>, <a href="mailto:coevolvesoft@gmail.com">coevolvesoft@gmail.com</a>
License: MIT, <a href="http://www.opensource.org/licenses/mit-license.php" >http://www.opensource.org/licenses/mit-license.php</a>

____________________________________________________________________________
todo:
ora: bind and execute always
ora: insert -add sequence name

____________________________________________________________________________
changes:

20100305
stable mssql support
20100120
mssql support
20091210
merged my and ora versions

____________________________________________________________________________
public:

$con, $rcount, $keys, $mode
select($table, $cols = '*', $where = null, $order = null, $opt = 0)
insert($table, $values, $cols = null, $filename='', $filefield='')
delete($table, $where = null)
update($table, $where, $values, $filename='', $filefield='')
selectAssociative($table, $cols = '*', $where = null, $order = null, $opt = 0)
getResult()
setConnection($host, $user, $pass, $dbname)
setDatabase($dbname)
disconnect()
cleanup($data, $write = false)
errDetails($rsc=null)

____________________________________________________________________________
private:

$db_host, $db_user, $db_pass, $db_name, $result, $oracon, $orast
connect()
tableExists($table)
cleanupSlashes($data, $write = false)
ms_ : select(), update(), insert(), delete(), selectAssociative()
my_ : select(), update(), insert(), delete(), selectAssociative()
ora_ : select(), update(), insert(), delete(), selectAssociative(), updateFile(), insertFile()

____________________________________________________________________________
notes:

All methods return true if completed successfully and false if an error occurred, except getResult and cleanup.
On setConnection, setDatabase, insert, delete and update, result is empty array, on disconnect result is intact.
For update and insert, filename and filefield used in oracle mode for file handling (bind mode).

____________________________________________________________________________

*/


class LoneDatabaseClass {

    var $db_host = '';			// use setConnection()
    var $db_user = '';
    var $db_pass = '';
    var $db_name = '';
    var $result = array();		// use getResult()
    var $con = false;			// connection active flag
    var $rcount = -1;
    var $keys = array();	
    var $oracon = null;
    var $orast = null;
    var $mode = '';


    function select($table, $cols = '*', $where = null, $order = null, $opt = 0) {
		// required: table: table name
		// optional: cols: columns requested, comma separated
		//		where: expresion as a string
		//		order: 'column DESC' expresion as a string
		//		opt: oracle options: OCI_BOTH | ( OCI_ASSOC | OCI_NUM ) & OCI_RETURN_NULLS & OCI_RETURN_LOBS
		
		if (!$this->tableExists($table)) { return false; }	
		$q = 'SELECT '.$cols.' FROM '.$table;
                if ($where != null) { $q .= ' WHERE '.$where; }
                if ($order != null) { $q .= ' ORDER BY '.$order; }
		if ($this->mode=='mysql') {
			return $this->my_select($q);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_select($q);
		}
		else if ($this->mode=='oracle') {
			return $this->ora_select($q, $opt);
		}
		return false;
	}
	
    function insert($table, $values, $cols = null, $filename='', $filefield='') {
		// required: table: table name
		// 		values: values as array
		// optional: cols: column names, comma separated
		//		filename, filefield: oracle file handling
		
		if (!$this->tableExists($table)) { return false; }
		if ($this->mode=='mysql') {
			return $this->my_insert($table, $values, $cols);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_insert($table, $values, $cols);
		}		
		else if ($this->mode=='oracle') {
			if ($filename=='') {
				return $this->ora_insert($table, $values, $cols);
			}
			else {
				return $this->ora_insertFile($table, $values, $cols, $filename, $filefield);
			}
		}
		return false;
	}

    function update($table, $where, $values, $filename='', $filefield='') {
		// required: table: tabel name
		//		where: expresion as a string
		//		values: values as array, column names as keys
		//		filename, filefield: oracle file handling

		if (!$this->tableExists($table)) { return false; }
		if ($where=='' || empty($values)) { return false; }
		if ($this->mode=='mysql') {
			return $this->my_update($table, $where, $values);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_update($table, $where, $values);
		}
		if ($this->mode=='oracle') {
			if ($filename=='') {
				return $this->ora_update($table, $where, $values);
			}
			else {
				return $this->ora_updateFile($table, $where, $values, $filename, $filefield);
			}
		}
		return false;
	}

    function delete($table, $where = null) {

		if (!$this->tableExists($table)) { return false; }
		if ($where == null) { return false; }
		if ($this->mode=='mysql') {
			return $this->my_delete($table, $where);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_delete($table, $where);
		}
		else if ($this->mode=='oracle') {
			return $this->ora_delete($table, $where);
		}
		return false;
	}

    function selectAssociative($table, $keycolumn, $cols = '*', $where = null, $order = null, $opt = 0) {

		if (!$this->tableExists($table)) { return false; }
                $q = 'SELECT '.$cols.' FROM '.$table;
                if ($where != null) { $q .= ' WHERE '.$where; }
                if ($order != null) { $q .= ' ORDER BY '.$order; }
		if ($this->mode=='mysql') {
			return $this->my_selectAssociative($q, $keycolumn);
		}
		else if ($this->mode=='oracle') {
			return $this->ora_selectAssociative($q, $keycolumn, $opt);
		}
		else if ($this->mode=='mssql') {
			return $this->ms_selectAssociative($q, $keycolumn, $table);
		}		
		return false;
	}


	// ____________________________________________________________________________

	function ms_select($q) {			// mssql select

		//echo $q;

                $query = mssql_query($q);
                if (!$query) { return false; }
		
                $this->rcount = mssql_num_rows($query);
		if ($this->rcount < 1) { return true; }

                for ($i = 0; $i < $this->rcount; $i++) {
        	    $r = mssql_fetch_array($query, MSSQL_ASSOC);
        	    $this->keys = array_keys($r);
		    $kcount = count($this->keys);
                    for ($x = 0; $x < $kcount; $x++) {

				//$v= iconv('UCS-2LE', 'UTF-8//IGNORE', $r[$this->keys[$x]]);

				$v = $r[$this->keys[$x]];
				
                if($this->rcount > 1) { $this->result[$i][$this->keys[$x]] = $v; }
                else { $this->result[$this->keys[$x]] = $r[$this->keys[$x]]; }
            }
        }
        return true;
	}

	function ms_update($table, $where, $values) {			// mssql update
    	
        $keys = array_keys($values);
		$vcount = count($values);
		$q = '';
        for ($i = 0; $i < $vcount; $i++) {
            if ($q!=''){ $q .= ','; }
            $q .= $keys[$i].'='.$values[$keys[$i]];
        }
		$q = 'UPDATE '.$table.' SET '.$q;
        $q .= ' WHERE '.$where;
        $ok = mssql_query($q);
		return $ok;
	}

    function ms_insert($table, $values, $cols) {			// mssql insert
    
        $q = 'INSERT INTO '.$table;
        if ($cols != null) { $q .= ' ('.$cols.')'; }
        $values = implode(',',$values);
        $q .= ' VALUES ('.$values.')';
        $ok = mssql_query($q);
        return $ok;
	}	


	function ms_selectAssociative($q, $keycolumn, $table) {			// mssql select associative

        $query = mssql_query($q);
        if (!$query) { return false;}
        $this->rcount = mssql_num_rows($query);
		if ($this->rcount < 1) { return true; }
		$doconv = $this->columnsToConvert($table);
        for ($i = 0; $i < $this->rcount; $i++) {
        	$r = mssql_fetch_array($query, MSSQL_ASSOC);
        	$this->keys = array_keys($r);
			if (!in_array($keycolumn, $this->keys)) { return false; }
			$kcount = count($this->keys);
            for ($x = 0; $x < $kcount; $x++) {
				if ($doconv[$this->keys[$x]]) {
					$v= iconv('UCS-2LE', 'UTF-8//IGNORE', $r[$this->keys[$x]]);
				}
                else {
                	$v = $r[$this->keys[$x]];
				}
				$this->result[$r[$keycolumn]][$this->keys[$x]] = $v;
            }
        }
        return true;
	}

	function ms_delete($table, $where) {					// mssql delete
	
		$q = 'DELETE FROM '.$table.' WHERE '.$where;
        $ok = mssql_query($q);
        return $ok;
	}	
	
	// ____________________________________________________________________________


	function my_select($q) {			// mysql select
	
		$query = mysql_query('SET CHARACTER SET utf8;');
        $query = mysql_query($q);
        if (!$query) { return false;}
        $this->rcount = mysql_num_rows($query);
		if ($this->rcount < 1) { return true; }
        for ($i = 0; $i < $this->rcount; $i++) {
        	$r = mysql_fetch_array($query);
        	$this->keys = array_keys($r);
			$kcount = count($this->keys);
            for ($x = 0; $x < $kcount; $x++) {
                if (!is_int($this->keys[$x])) { // Sanitizes keys so only alphavalues are allowed
                    if($this->rcount > 1) { $this->result[$i][$this->keys[$x]] = $r[$this->keys[$x]]; }
                    else { $this->result[$this->keys[$x]] = $r[$this->keys[$x]]; }
                }
				else {
					unset($this->keys[$x]);
				}
            }
        }
        return true;
	}

	function my_update($table, $where, $values) {			// mysql update
    	
        $keys = array_keys($values);
		$vcount = count($values);
		$q = '';
        for ($i = 0; $i < $vcount; $i++) {
            if ($q==''){ $q = $keys[$i].'='.$values[$keys[$i]]; }
            else { $q .= ','.$keys[$i].'='.$values[$keys[$i]]; }
        }
		$q = 'UPDATE '.$table.' SET '.$q;
        $q .= ' WHERE '.$where;
		$query = mysql_query('SET CHARACTER SET utf8;');
        $query = mysql_query($q);
        if ($query) { return true; }
        else { return false; }
	}

    function my_insert($table, $values, $cols) {			// mysql insert
    
        $q = 'INSERT INTO '.$table;
        if ($cols != null) { $q .= ' ('.$cols.')'; }
        $values = implode(',',$values);
        $q .= ' VALUES ('.$values.')';
		$ok = mysql_query('SET CHARACTER SET utf8;');
        if ($ok) { $ok = mysql_query($q); }
        return $ok;
	}	

	function my_selectAssociative($q, $keycolumn) {			// mysql select associative

		$query = mysql_query('SET CHARACTER SET utf8;');
        $query = mysql_query($q);
        if (!$query) { return false;}
        $this->rcount = mysql_num_rows($query);
		if ($this->rcount < 1) { return true; }
        for ($i = 0; $i < $this->rcount; $i++) {
        	$r = mysql_fetch_array($query);
        	$this->keys = array_keys($r);
			if (!in_array($keycolumn, $this->keys)) { return false; }
			$kcount = count($this->keys);
            for ($x = 0; $x < $kcount; $x++) {
                if (!is_int($this->keys[$x])) { // Sanitizes keys so only alphavalues are allowed
                    $this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]];
                }
				else {
					unset($this->keys[$x]);
				}
            }
        }
        return true;
	}

	function my_delete($table, $where) {					// mysql delete
	
		$q = 'DELETE FROM '.$table.' WHERE '.$where;
		$ok = mysql_query('SET CHARACTER SET utf8;');
        if ($ok) { $ok = mysql_query($q); }
        return $ok;
	}
	
	// ____________________________________________________________________________


	function ora_select($q, $opt) {													// oracle select

		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
		$oramode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS;
		if ($opt!=0) { $oramode = $opt; }
        $this->rcount = 0;
		while ($r = oci_fetch_array($this->orast, $oramode)) {
			if (empty($this->keys)) {
        		$this->keys = array_keys($r);
				$kcount = count($this->keys);
			}
            for ($x = 0; $x < $kcount; $x++) {
                if (is_int($this->keys[$x])) { return false; } // only alphavalues are allowed, OCI_ASSOC
                $this->result[$this->rcount][$this->keys[$x]] = $r[$this->keys[$x]];
            }
			$this->rcount++;
        }
		if($this->rcount == 1) { // columns as keys on one
			$t = $this->result[0];
			$this->result = array();
            for ($x = 0; $x < $kcount; $x++) {
            	$this->result[$this->keys[$x]]=$t[$this->keys[$x]];
			}
		}
        return true;
	}

	function ora_update($table, $where, $values) {									// oracle update

        $keys = array_keys($values);
		$vcount = count($values);
		$q = '';	
        for ($i = 0; $i < $vcount; $i++) {
            if ($q==''){ $q = $keys[$i].'='.$values[$keys[$i]]; }
            else { $q .= ','.$keys[$i].'='.$values[$keys[$i]]; }
        }
		$q = 'UPDATE '.$table.' SET '.$q;
        $q .= ' WHERE '.$where;
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
        return true;	
	}
	
	function ora_updateFile($table, $where, $values, $filename, $filefield) {		// oracle update file

        $keys = array_keys($values);
		$vcount = count($values);
		$q = '';
        for ($i = 0; $i < $vcount; $i++) {
        	$pair = $keys[$i].'= '.$values[$keys[$i]];
        	if ($keys[$i]==$filefield) { $pair=$filefield.'= empty_blob()'; }
            if ($q==''){ $q = $pair; }
            else { $q .= ','.$pair; }
        }
		$q = 'UPDATE '.$table.' SET '.$q;
		$q .= ' WHERE '.$where;
		$q .= ' returning '.$filefield.' into :blobdata';	
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		$lob = oci_new_descriptor($this->oracon, OCI_D_LOB);
		oci_bind_by_name($this->orast, ':blobdata', $lob, -1, OCI_B_BLOB);
		if (!oci_execute($this->orast, OCI_DEFAULT)) { $this->errDetails($this->orast); return false; }
		$lob->import($filename);
		oci_commit($this->oracon);
		$lob->close();
		return true;
	}

    function ora_insert($table, $values, $cols) {									// oracle insert
		global $oraseq;
		
	    $q = 'INSERT INTO '.$table;
        if ($cols != null) { $q .= ' (ID,'.$cols.')'; }
        $values = implode(',',$values);
        $q .= ' VALUES ('.$oraseq.'.NextVal, '.$values.')';
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
        return true;
	}

    function ora_insertFile($table, $values, $cols, $filename, $filefield) {		// oracle insert file
		global $oraseq;
		
		$keys = array_keys($values);
		$vcount = count($values);
        $q = 'INSERT INTO '.$table;
        if ($cols != null) { $q .= ' (ID,'.$cols.')'; }
		$q .= ' VALUES ('.$oraseq.'.NextVal';
        for ($i = 0; $i < $vcount; $i++) {
        	if ($keys[$i]==$filefield) { $q .= ',empty_blob()'; }
			else {$q .= ','.$values[$keys[$i]]; }
        }		
		$q .= ') returning '.$filefield.' into :blobdata';
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		$lob = oci_new_descriptor($this->oracon, OCI_D_LOB);
		oci_bind_by_name($this->orast, ':blobdata', $lob, -1, OCI_B_BLOB);
		if (!oci_execute($this->orast, OCI_DEFAULT)) { $this->errDetails($this->orast); return false; }
		$lob->import($filename);
		oci_commit($this->oracon);
		$lob->close();
		return true;
	}	

	function ora_selectAssociative($q, $keycolumn, $opt) {										// oracle select associative

		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
		$oramode = OCI_ASSOC+OCI_RETURN_NULLS;
		if ($opt!=0) { $oramode = $opt; }
        $this->rcount = 0;		
		while ($r = oci_fetch_array($this->orast, $oramode)) {
			if (empty($this->keys)) {
        		$this->keys = array_keys($r);
				$kcount = count($this->keys);
				if (!in_array($keycolumn, $this->keys)) { return false; }
			}
            for ($x = 0; $x < $kcount; $x++) {
                if (is_int($this->keys[$x])) { return false; } // only alphavalues are allowed, OCI_ASSOC
				$this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]];
			}
			$this->rcount++;
		}	
        return true;
	}

	function ora_delete($table, $where) {								// oracle delete
	
        $q = 'DELETE FROM '.$table.' WHERE '.$where;
		$this->orast = oci_parse($this->oracon, $q);
		if (!$this->orast) { $this->errDetails($this->oracon); return false; }
		if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; }
        return true;
	}
	
	// ____________________________________________________________________________
		
	
    function getResult() {
        return $this->result;
    }	
	
    function setConnection($host, $user, $pass, $dbname) {
    	global $dbmode;

		$this->mode = $dbmode;
        if ($this->con) {
			$ok = $this->disconnect();
			if(!$ok) { return false; }
		}
		$this->oracon = null;
		$this->orast = null;
        $this->con = false;
		$this->rcount = -1;
		$this->keys = array();
        $this->result = array();
		$this->db_host = $host;
		$this->db_user = $user;
		$this->db_pass = $pass;
        $this->db_name = $dbname;
		$this->result = array();
        $ok = $this->connect();  // $this->con==true on success
		return $ok;
    }


    function setDatabase($dbname) {
    	// change database
    	global $dbmode;
		
		$this->mode = $dbmode;	
        if ($this->con) {
			$ok = $this->disconnect();
			if(!$ok) return false;
		}
        $this->con = false;
		$this->rcount = -1;
		$this->keys = array();
        $this->result = array();
        $this->db_name = $dbname;
        $ok = $this->connect();  // $this->con==true on success
		return $ok;
    }

	function disconnect() {

		$ok = false;
		if ($this->mode=='mysql') {
			$ok = mysql_close();
		}
		else if ($this->mode=='mssql') {
			$ok = mssql_close();
		}
		else if ($this->mode=='oracle') {
			if ($this->orast) { $ok = oci_free_statement($this->orast); }
			if ($ok && $this->oracon) { $ok = oci_close($this->oracon); }
		}
		$this->con = !$ok;
		return $ok;
	}
	
    function connect() {

		if ($this->db_host=='' || $this->db_user=='' || $this->db_pass=='' || $this->db_name=='') { return false; }
		
		$ok = false;
		if ($this->mode=='mysql') {
	        $dbh = mysql_connect($this->db_host,$this->db_user,$this->db_pass);
	        if ($dbh) { $ok = mysql_select_db($this->db_name, $dbh); }
		}
		else if ($this->mode=='oracle') {
			$this->oracon = oci_connect($this->db_user,$this->db_pass,$this->db_host.'/'.$this->db_name);
        	if (!$this->oracon) { $this->errDetails(); }
			else { $ok = true; }
		}
		else if ($this->mode=='mssql') {
	        $dbh = mssql_connect($this->db_host,$this->db_user,$this->db_pass);
	        if ($dbh) { $ok = mssql_select_db($this->db_name, $dbh); }
		}
		$this->con = $ok;
		return $ok;
    }

    function tableExists($table) {

		if (!$this->con || $table=='') { return false; }    	
    	$this->result = array();  // resets
    	$this->rcount = -1;
    	$this->keys = array();
		
		$ok = false;

    	if ($this->mode=='mysql') {
    		$q = 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"';
	        $q = mysql_query($q);
	        if ($q) {
	            if (mysql_num_rows($q)==1) { $ok = true; }
	        }    		
		}
		else if ($this->mode=='oracle') {
			$q = "select * from all_objects where object_type in ('TABLE','VIEW') and object_name = '$table'";
			$this->orast = oci_parse($this->oracon, $q);
			if (!$this->orast) { $this->errDetails($this->oracon); }
			else if (!oci_execute($this->orast)) { $this->errDetails($this->orast); }
			else { $ok = true; }
		}
		else if ($this->mode=='mssql') {
			if (strpos($table, 'INNER JOIN')!=-1) { return true;}
			$table = str_replace(array('[',']'), '', $table);
			$q = "SELECT name FROM sys.sysobjects WHERE name='$table' AND OBJECTPROPERTY(id,'IsUserTable')=1";
	        $q = mssql_query($q);
	        if ($q) {
	            if (mssql_num_rows($q)==1) { $ok = true; }
			}
		}
        return $ok;
    }

	// ____________________________________________________________________________


	function cleanup($data, $write = false) {
		
	    if (is_array($data)) {
	        foreach ($data as $key => $value) {
	            $data[$key] = $this->cleanupSlashes($value, $write);
	        }
	    } else {
	        $data = $this->cleanupSlashes($data, $write);
	    }
	    return $data;
	}
	
	function cleanupSlashes($data, $write = false) {
		
	    if (isset($data)) { // preserve null
	        if (get_magic_quotes_gpc()) {
	            $data = stripslashes($data);
	        }
	        if ($write && $this->mode=='mysql') {
	            $data = mysql_real_escape_string($data);
	        }
	    }
	    return $data;
	}

	function errDetails($rsc=null) {
		global $debug;
		
		if ($debug!==true) { return true; }
		if ($rsc==null) { $err = oci_error(); }
		else { $err = oci_error($rsc); }
		echo '<br /><font color="red"><strong>Oracle Error Details for '. $err['code'];
		echo '<br />message = '  . $err['message'];
		echo '<br />position = ' . $err['offset'];
		echo '<br />statement = '  . $err['sqltext'];
		echo '</strong></font><br /><br />';
		return true;
	}

	// ____________________________________________________________________________
	// MS SQL UCS-2 text read/write,
	// declare fields in model: convert(varbinary(2*size), fieldname) as fieldname
	
	function columnsToConvert($table) {
		// result as [ sColname=>bDoConv ]
		global $fields;
		
		if (isset($fields[$table])) {
			$cols = array_keys($fields[$table]);
		}
		else {
			return array('itemkey'=>false, 'value'=>true);
		}
		$ret = array();
		foreach ($cols as $k=>$f) {
			if ($fields[$table][$f]['type']=='ntext') {
				$ret[$f] = true;
			}
			else {
				$ret[$f] = false;
			}
		}
		return $ret;
	}

	/* not used */
	
	function columnsDeclare($table) {
		// result as [ sColname=>declaration ]
		global $fields;
		
		if (isset($fields[$table])) {
			$cols = array_keys($fields[$table]);
		}
		else {
			return array('itemkey'=>'itemkey', 'value'=>'convert(varbinary(200), value) as value');
		}
		$ret = array();
		foreach ($cols as $k=>$f) {
			$ret[$f] = $fields[$table][$f]['colname'];
		}
		return $ret;		
	}
	
	function encUCS2($str) {
    	$ucs2 = iconv('UTF-8', 'UCS-2LE', $str);
		// converting UCS-2 string into "binary" hexadecimal form
		$arr = unpack('H*hex', $ucs2);
		return "0x{$arr['hex']}"; // include it in sql statement without quotes		
	}
	
	// iconv('UCS-2LE', 'UTF-8', $str) to decode
		
	function ucs2html($str) {
		$str=trim($str);
		$len=strlen($str);
		$html='';
		for($i=0;$i<$len;$i+=2)
		$html.='&#'.hexdec(dechex(ord($str[$i+1])).sprintf("%02s",dechex(ord($str[$i])))).';';
		return($html);
	}
	
	function hex2utf($UtfCharInHex) {
		$OutputChar = "";
		$UtfCharInDec = hexdec($UtfCharInHex);
		if($UtfCharInDec < 128) $OutputChar .= chr($UtfCharInDec);
		else if($UtfCharInDec < 2048) $OutputChar .= chr(($UtfCharInDec >> 6) + 192) . chr(($UtfCharInDec & 63) + 128);
		else if($UtfCharInDec < 65536) $OutputChar .= chr(($UtfCharInDec >> 12) + 224) . chr((($UtfCharInDec >> 6) & 63) + 128) . chr(($UtfCharInDec & 63) + 128);
		else if($UtfCharInDec < 2097152) $OutputChar .= chr($UtfCharInDec >> 18 + 240) . chr((($UtfCharInDec >> 12) & 63) + 128) . chr(($UtfCharInDec >> 6) & 63 + 128) . chr($UtfCharInDec & 63 + 128);
		return $OutputChar;
	}	
	
}

source

Declare Variables for Table In Visual Basic

Private Function getInformation() As DataTable
        Try
            Dim dt As DataTable
            Dim arlParameters As New ArrayList

            arlParameters.Add(New clsODP.SP_Parameter("vManufacturer", manufacturer))
            dt = ODP.storedProcedure_getData("PK_MANUMODEL.getInformation", arlParameters)

            Return dt

        Catch ex As Exception
            Throw
        End Try
    End Function

source

Package Body

create or replace PACKAGE BODY "PK_STOCK" AS

  procedure loadStockInfo(vFleet in varchar2, vPk in number, io_cursor in out t_cursor) IS
  
  Begin
  
  open io_cursor for
    select distinct s.stock_number, s.site, s.manufacturer, s.manufacturer_part_number as model, s.manufacturer_key, 
            s.group_key, s.stock_level, s.reorder_point, s.avg_unit_price,
            s.description, s.lead_time, s.quality_class, s.depleted_date, s.mpn_key, nvl(max(vce.vendor_contact_equipment_id), -1) as mmId, s.is_obsolete
            from (select s.pk, s.fleet, s.site, s.stock_number, s.manufacturer, s.manufacturer_part_number,
                    s.manufacturer_key, s.stock_level, s.reorder_point, s.avg_unit_price,
                    s.description, s.lead_time, s.quality_class, s.depleted_date, s.mpn_key, nvl(g.group_key,s.manufacturer_key) as group_key, s.is_obsolete
                  from stock_p s, group_man g
                  where g.manufacturer_key(+) = s.manufacturer_key
                  and g.group_type(+) = 0
                  and s.fleet = vFleet
                  and s.pk = vPk) s, vendor_contact_equipment vce
            where vce.manufacturer_key(+) = s.group_key
            and vce.model_key(+) = s.mpn_key
            group by s.stock_number, s.site, s.manufacturer, s.manufacturer_part_number, s.manufacturer_key, 
            s.group_key, s.stock_level, s.reorder_point, s.avg_unit_price,
            s.description, s.lead_time, s.quality_class, s.depleted_date, s.mpn_key, s.is_obsolete;
            
    exception
          when others then
            PK_ERROR.sperror_log('loadStockInfo',SQLERRM,SQLCODE);
            open io_cursor for select -1 from dual;
  
  end loadStockInfo;
  
  
  procedure loadComponents(vFleet in varchar2, vPk in number, io_cursor in out t_cursor) IS
  
  Begin
  
  open io_cursor for
        SELECT DISTINCT c.pk   ,
          c.fleet              ,
          c.component          ,
          c.site               ,
          c.unit               ,
          c.manufacturer       ,
          c.model              ,
          c.epri_classification,
          c.quality_class      ,
          c.description        ,
          mv.stock_count       ,
          qsv.obsolete
        FROM stock_p s        
        join fleet_bom_p b
          on s.stock_number = b.stock_number
          and s.fleet = b.fleet
          and s.site = b.site        
        left outer join component_p c
          on b.fleet = c.fleet
          and b.site = c.site
          and b.component = c.component          
        left outer join mv_fleet_bom_count mv
          on b.component = mv.component
          and b.fleet = mv.fleet
          and b.site = mv.site        
        left outer join quick_search_view_stock qsv
          on qsv.stock_number = s.stock_number
          and qsv.site = s.site
          and qsv.fleet = s.fleet        
        WHERE s.fleet            = vFleet
          AND s.pk               = vPk
          AND c.pk is not null;
          
    exception
          when others then
            PK_ERROR.sperror_log('loadComponents',SQLERRM,SQLCODE);
            open io_cursor for select -1 from dual;
  
  end loadComponents;
  
  procedure loadStats(vFleet in varchar2, vPk in number, io_cursor in out t_cursor) IS
  
  Begin
  
  open io_cursor for
  select 1 from dual;
  
    exception
          when others then
            PK_ERROR.sperror_log('loadStats',SQLERRM,SQLCODE);
            open io_cursor for select -1 from dual;
  
  end loadStats;
  
  procedure loadAltManuModel(vFleet in varchar2, vStockNumber in varchar2, vPK in number, io_cursor in out t_cursor) IS
  
  Begin
  
  open io_cursor for
  select s.stock_number, s.manufacturer, s.manufacturer_part_number as model, s.pk, 
    (case when s.is_obsolete = 'YES' then 1 else 0 end) as obsolete, s.site, s.fleet
  from stock_p s
  where s.fleet = vFleet
  and s.stock_number = vStockNumber
  and s.pk != vPK;
  
    exception
          when others then
            PK_ERROR.sperror_log('loadAltManuModel',SQLERRM,SQLCODE);
            open io_cursor for select -1 from dual;
  
  end loadAltManuModel;
 
 procedure loadMaintPlan(vFleet in varchar2, vSite in varchar2, vStockNumber in varchar2, io_cursor in out t_cursor) IS
 
  Begin
  
  open io_cursor for 
  
  select mp_number, freq,
        year1, year2, year3,
        year4, year5, year6,
        year7, year8, year9, year10,
        freq_in_days, due_date
      
  from maint_plan mp, maint_plan_part mpp
  where mp.mp_id = mpp.mp_id
  AND fleet = vFleet
  AND site = vSite
  AND stock_number = vStockNumber
  order by mp_number;
  
  exception
          when others then
            PK_ERROR.sperror_log('loadMaintPlan',SQLERRM,SQLCODE);
            open io_cursor for select -1 from dual;
  
  end loadMaintPlan;
  
  -- Procedure: loadWOData
  -- Description: Loads Work Order Data for PM by selecting all work orders associated with stock number
  -- Created: 7/23/2009 (Kyle N.)
  -- Modified: 10/7/2009, Greg F, outer joined to the component_p table to get the PK
  PROCEDURE loadWOData(vFleet IN VARCHAR2, vSite IN VARCHAR2, vStockNumber IN VARCHAR2, io_cursor IN OUT t_cursor) IS
  
    BEGIN
      OPEN io_cursor FOR
        SELECT DISTINCT wpp.basic_start_date, 
                        wpp.site, 
                        wpp.order_number, 
                        wpp.component, 
                        wpp.description,  
                        wpp.task_type,
                        wpp.stock_number,
                        wpp.pk,
                        cp.pk as component_pk
        FROM work_order_parts_p wpp
        left outer join component_p cp
        on cp.component = wpp.component
        and cp.fleet = wpp.fleet
        and UPPER(cp.site) = wpp.site
        WHERE wpp.fleet = vFleet
        AND wpp.site = vSite
        AND wpp.stock_number = vStockNumber
        ORDER BY basic_start_date nulls last;
      
    EXCEPTION
    WHEN OTHERS THEN
      PK_ERROR.sperror_log('loadWOData',SQLERRM,SQLCODE);
      OPEN io_cursor FOR SELECT -1 FROM dual;
               
  END loadWOData;

END;

source

Package

create or replace
PACKAGE "PK_STOCK" AS

  type t_cursor is ref cursor;
  
  procedure loadStockInfo(vFleet in varchar2, vPk in number, io_cursor in out t_cursor);
  procedure loadComponents(vFleet in varchar2, vPk in number, io_cursor in out t_cursor);
  procedure loadStats(vFleet in varchar2, vPk in number, io_cursor in out t_cursor);
  procedure loadAltManuModel(vFleet in varchar2, vStockNumber in varchar2, vPK in number, io_cursor in out t_cursor);
  procedure loadMaintPlan(vFleet in varchar2, vSite in varchar2, vStockNumber in varchar2, io_cursor in out t_cursor);
  PROCEDURE loadWOData(vFleet IN VARCHAR2, vSite IN VARCHAR2, vStockNumber IN VARCHAR2, io_cursor IN OUT t_cursor);
  
END;

source

(php sql to array tree) Convert parent child database structure to a tree and then print it as html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>PHP Tree Builder</title>
  </head>
  <body>
<?php

function tree_example(){
  $rows = array();  //stores all the database rows that are to be converted into a tree
  $tree = array();  //stores the tree
  $tree_index = array();  //an array used to quickly find nodes in the tree
  $id_column = "tag_id";  //The column that contains the id of each node
  $parent_column = "parent_id";  //The column that contains the id of each node's parent
  $text_column = "name";  //The column to display when printing the tree to html
    
  //a sample array - normally you would create this array from your database table (sorted by id)
  //it is important that they keys of the array = the id of each row
  $rows[1] = array("tag_id" => 1, "name" => "food", "parent_id" => NULL);
  $rows[2] = array("tag_id" => 2, "name" => "pizza", "parent_id" => 1);
  $rows[3] = array("tag_id" => 3, "name" => "pepperoni", "parent_id" => 2);
  $rows[4] = array("tag_id" => 4, "name" => "lazagna", "parent_id" => 1);
  $rows[5] = array("tag_id" => 5, "name" => "pie", "parent_id" => 6);
  $rows[6] = array("tag_id" => 6, "name" => "dessert", "parent_id" => NULL);  //a parent defined after the child
  $rows[7] = array("tag_id" => 7, "name" => "cinnamon", "parent_id" => 8);
  $rows[8] = array("tag_id" => 8, "name" => "bear claw", "parent_id" => 9);
  $rows[9] = array("tag_id" => 9, "name" => "donut", "parent_id" => 6); //a parent defined after another late parent and with two children
  
  //build the tree - this will complete in a single pass if no parents are defined after children
  while(count($rows) > 0){
    foreach($rows as $row_id => $row){
      if($row[$parent_column]){
        if((!array_key_exists($row[$parent_column], $rows)) and (!array_key_exists($row[$parent_column], $tree_index))){
           unset($rows[$row_id]);
        }
        else{
          if(array_key_exists($row[$parent_column], $tree_index)){
            $parent = & $tree_index[$row[$parent_column]];
            $parent['children'][$row_id] = array("node" => $row, "children" => array());
            $tree_index[$row_id] = & $parent['children'][$row_id];
            unset($rows[$row_id]);
          }
        }
      }
      else{
        $tree[$row_id] = array("node" => $row, "children" => array());
        $tree_index[$row_id] = & $tree[$row_id];
        unset($rows[$row_id]);
      }
    }
  }
  
  //we are done with index now so free it
  unset($tree_index);
  
  //start printing out the tree
  $html = "    <div id='tree'>
";
  $html .= "      <ul>
";
  foreach($tree as $node){
    //go to each top level node and print it and it's children
    $html .= print_tree($node, $text_column, 8, 2);
  }
  $html .= "      </ul>
";
  $html .= "  </div>
";
  return $html;
}

//recursive function used to print tree structure to html
function print_tree($node, $text_column, $indent, $indent_size){
  //print the current node
  $html = str_repeat(" ", $indent) . "<li>". $node['node'][$text_column];
  if($node['children']){
    $html .= "
". str_repeat(" ", $indent + $indent_size) . "<ul>
";
    //then print it's children nodes
    foreach($node['children'] as $child){
      $html .= print_tree($child, $text_column, $indent + $indent_size * 2, $indent_size);
    }
    $html .= str_repeat(" ", $indent + $indent_size) . "</ul>
". str_repeat(" ", $indent);
  }
  $html .= "</li>
";
  return $html;
}

print tree_example();

?>
  </body>
</html>

source

Database connectivity problems (HUGE)

It may be entirely possible that you experienced a momentary availability affecting multiple customers in the MySQL SmartPool, referred to as the "bad neighbor effect". Although we have systems in place to handle these situations automatically, because of the shared environment of the (gs) Grid-Service, these brief outages can happen from time-to-time. Automatic procedures and our administrators, quickly took steps to resolve the issue, by bursting the offender to an isolated container, to free up load on the SmartPool.

We apologize for this inconvenience, and appreciate your patience, while we dealt with this matter. I have verified that MySQL is currently fully operational on your (gs) Grid-Service. 
You can also use a proxy server to verify the domain is still/back up. This will essentially reroute your web request through a different path than normal.

Although this is a rare occurrence, the only recommendation to completely prevent these types of outages in the future is to migrate to a (dv)Dedicated-Virtual Service or add a MySQL Container to your account. The latter gives your databases dedicated resources (cpu, memory, storage) and does not rely on the shared resources of the MySQL SmartPool of the (gs). You can learn more about MySQL containers at the (mt) Media Temple website:

<a href="http://mediatemple.net/webhosting/gs/features/containers.php#mysql" >http://mediatemple.net/webhosting/gs/features/containers.php#mysql</a>

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

source

Backup a mysql database script

#!/usr/bin/env ruby
require File.dirname(__FILE__) + '/../config/boot'
require 'erb'
require 'yaml'

unless config = YAML::load(ERB.new(IO.read(RAILS_ROOT + "/config/database.yml")).result)[RAILS_ENV]
  abort "No database is configured for the environment '#{RAILS_ENV}'"
end

db_out_path = File.join(RAILS_ROOT, 'tmp', "dbdump_#{RAILS_ENV}.sql")
system "mysqldump -u #{config['username']} --password=#{config['password']} #{config['database']} > #{db_out_path}"

source

Importing a MySQL database in Windows

mysql -h localhost -u username -p password -D database_name < c:path	oackup_file.sql

source

Find all databases and login credentials

SELECT domains.name AS domain_name,
data_bases.name AS database_name, db_users.login, accounts.password
FROM data_bases, db_users, domains, accounts
WHERE data_bases.dom_id = domains.id
AND db_users.db_id = data_bases.id
AND db_users.account_id = accounts.id
ORDER BY domain_name;

source