PHP - SQL

From XennisWiki
Jump to: navigation, search

Idiorm

Examples

Configuration

E.g. for MySQL

ORM::configure(array(
    'connection_string' => 'mysql:host=HOST;dbname=DBNAME',
    'username' => 'USERNAME',
    'password' => 'PASSWORD'
));

Select many

$query = ORM::for_table('users')
           ->select_many(['id', 'name', 'mail'])
           ->order_by_asc('name')
           ->limit(10)
           ->offset(5);
$query->find_array();
//json_encode($query->find_array());

Select item

$query = ORM::for_table('users')
	    ->where_id_is(5);
	    ->select_many(['name', 'mail']);
$item = $query->find_one();
//if ($item) {
//    echo json_encode($item->as_array());
//}

Insert

$item = ORM::for_table('user')->create();
$item->set([
  'name' => 'Example'
  'mail' => 'mail@example.org'
])->save();

Update

$item = ORM::for_table('users')->find_one(4);
if ($item) {
    $item->set([
        'name' => 'MyUser',
        'mail' => 'myUser@example.org'
    ])->save();
}

Delete

$item = ORM::for_table('users')->find_one(4);
if ($item) {
    $item->delete();
}

WordPress query builder

<?php
/**
 * Create SQL queries.
 * 
 * @package common
 */
class MT_QueryBuilder {
	
	private $tablePraefix;
	private $tableName;
	private $select;
	private $join = '';
	private $groupBy = '';
	private $orderBy = '';
	private $limit = '';
	
	public function __construct($tablePraefix = 'wp_mt_') {
		$this->tablePraefix = $tablePraefix;
		return $this;
	}
	
	/**
	 * Set from of the query.
	 * 
	 * @param string $tableName From table name
	 * @param null|string|array $select Select from this field
	 * @return MT_QueryBuilder
	 */
	public function from($tableName, $select = NULL) {
		$this->tableName = $this->tablePraefix.$tableName;
		$this->addToSelect($tableName, $select);
		return $this;
	}
	
	/**
	 * 
	 * @param type $type
	 * @param string|true $joinTable
	 * @param string $joinCondition
	 * @param type $joinSelect
	 * @return MT_QueryBuilder
	 */
	private function _generalJoin($type, $joinTable, $joinCondition, $joinSelect = NULL) {
		if (!empty($joinTable) && !empty($joinCondition)) {
			if ($joinCondition === TRUE) {
				$joinCondition = $this->tableName.'.'.$joinTable.'='.$this->tablePraefix.$joinTable.'.id';
			}
			$this->addToJoin($type, $joinTable, $joinCondition);
			$this->addToSelect($joinTable, $joinSelect);
		}
		return $this;
	}
	
	public function join($joinTable, $joinCondition, $joinSelect = NULL) {
		return $this->_generalJoin('JOIN', $joinTable, $joinCondition, $joinSelect);
	}
	
	public function joinLeft($joinTable, $joinCondition, $joinSelect = NULL) {
		return $this->_generalJoin('LEFT JOIN', $joinTable, $joinCondition, $joinSelect);
	}
	
	public function joinInner($joinTable, $joinCondition, $joinSelect = NULL) {
		return $this->_generalJoin('INNER JOIN', $joinTable, $joinCondition, $joinSelect);		
	}
	
	public function joinLeftOuter($joinTable, $joinCondition, $joinSelect = NULL) {
		return $this->_generalJoin('LEFT OUTER JOIN', $joinTable, $joinCondition, $joinSelect);				
	}
	
	public function select($select) {
		if (!empty($select)) {
			if (!empty($this->select)) {
				$this->select .= ', ';
			}
			$this->select .= $select;
		}
		return $this;
	}
	
	public function where($condition) {
		if (!empty($condition)) {
			if (empty($this->where)) {
				$this->where .= 'WHERE';
			} else {
				$this->where .= ' AND';
			}
			$this->where .= ' '.$condition;
		}
		return $this;
	}
	
	public function whereEqual($first, $second) {
		$this->where($first.' = ' . $second);
		return $this;
	}
	
	public function groupBy($groupBy) {
		if(!empty($groupBy)) {
			if (is_array($groupBy)) {
				$groupBy = implode(',', $groupBy);
			}			
			$this->groupBy = ' GROUP BY '.$groupBy;
		}
		return $this;
	}
	
	/**
	 * 
	 * @param string|array $orderBy
	 * @return MT_QueryBuilder
	 */
	public function orderBy($orderBy) {
		if(!empty($orderBy)) {
			if (is_array($orderBy)) {
				$orderBy = implode(',', $orderBy);
			}
			$this->orderBy = ' ORDER BY '.$orderBy;
		}
		return $this;
	}	
	
	public function limit($amount, $offset = NULL) {
		if(!empty($offset) && !empty($amount)) {
			$this->limit = ' LIMIT '.$offset.', '.$amount;
		}
		else if(!empty($amount)) {
			$this->limit = ' LIMIT '.$amount;
		}
		return $this;
	}
	
	public function limitPage($page, $amount) {
		if (!empty($page)) {
			$this->limit($amount, ($page-1) * $amount);
		}
		return $this;
	}
	
	public function __toString() {
		return $this->get();
	}
	
	public function get() {
		if (empty($this->select)) {
			$this->select = '*';
		}
		return '
			SELECT '.$this->select.'
			FROM '.$this->tableName.' '
			.$this->join.' '
			.$this->where.' '
			.$this->groupBy. ' '
			.$this->orderBy.' '
			.$this->limit;
	}
		
	/**
	 * Adds a join.
	 * 
	 * @param string $type Join type, i.e. 'JOIN', 'INNER JOIN'
	 * @param string $joinTable Join table
	 * @param string $joinCondition Join condition
	 */
	private function addToJoin($type, $joinTable, $joinCondition) {
		$this->join .= ' '.$type.' '.$this->tablePraefix.$joinTable.'
				ON '.$joinCondition;
	}
	
	private function addToSelect($table, $select) {
		if (!empty($select)) {
			if (is_array($select)) {
				$this->select($this->getSelectStringFromArray($table, $select));
			} else if (is_string($select)) {
				$this->select($this->tablePraefix.$table.'.'.$select);
			}			
		}
	}	
	
	private function getSelectStringFromArray($table, $selectArray) {
		// Add table name as päfix
		$selectArray = preg_filter('/^/', $this->tablePraefix.$table . '.', $selectArray);
		// Create a string
		return implode(',', $selectArray);
	}
	
	public function getResult($output_type = 'OBJECT') {
		global $wpdb;
		return $wpdb->get_results($this->get(), $output_type);
	}
	
	public function getResultOne($output_type = 'OBJECT') {
		global $wpdb;
		return $wpdb->get_row($this->get(), $output_type);
	}
}

MySQL API (deprecated)

Further information: MySQL

Examples

Connect to database (mysql_connect)

/*
 * Database connection (Charset: UTF8)
 */
mysql_connect( $cDBserver, $cDBuser, $cDBpassword ) or die ("Keine Verbindung mit der Datenbank moeglich");
mysql_select_db( $cDBname ) or die ("Die Datenbank existiert nicht");
mysql_set_charset('utf8');

/*
 * Close MySQL connection
 */
mysql_close();

Insert data (mysql_query)

$name = "Hans";
$score = 700;

$query = "INSERT INTO neversquare_scores
	  VALUES ('$name', $score)";
$result = mysql_query($query);

if($result) {
    echo "Inserting successful";
    // get id by: mysql_insert_id()
} else {
    echo "Inserting failed";
}
mysql_free_result($result);

Select data (mysql_query)

$query = "SELECT name, score
       	  FROM   scores
       	  ORDER BY  score";
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
	$echo $row->name;
	$echo = $row->score;	
	$i++;	
}
mysql_free_result($result);

Functions

More information: MySQL Functions, MySQL-Datentypen

Funktion Beschreibung
mysql_num_rows Liefert die Anzahl der Datensätze im Ergebnis
mysql_affected_rows Liefert die Anzahl betroffener Datensätze für INSERT, UPDATE oder DELETE Anfragen
mysql_set_charset Setzt den Verbindungszeichensatz

See also

External links