I will share with you a class created in PHP used to connect to MySql and also used to perform the necessary operations on the database (Select, Insert, Update e Delete).
Code of PHP class:
class MySQL {
public $dbCon;
public function __construct() {
$this->dbCon = mysqli_connect ( 'URL', 'USER', 'PASSWORD', 'DATABASE_NAME' );
mysqli_query($this->dbCon, 'SET NAMES "utf8";');
mysqli_query($this->dbCon, 'SET character_set_connection=utf8;');
mysqli_query($this->dbCon, 'SET character_set_client=utf8;');
mysqli_query($this->dbCon, 'SET character_set_results=utf8;');
}
public function __destruct() {
mysqli_close ( $this->dbCon );
}
/*
* insert function table name, array value
* $values = array('first_name' => 'pramod','last_name'=> 'jain');
*/
public function insert($table, $values) {
$sql = "INSERT INTO $table SET ";
$c = 0;
if (! empty ( $values )) {
foreach ( $values as $key => $val ) {
if ($c == 0) {
$sql .= "$key='".$val."'";
}else{
$sql .= ", $key='" . $val . "'";
}
$c ++;
}
} else {
return false;
}
$this->dbCon->query ( $sql ) or die ( mysqli_error () );
return mysqli_insert_id ( $this->dbCon );
}
/*
* update function table name, array value
* $values = array('first_name' => 'pramod','last_name'=> 'jain');
* $condition = array('id' =>5,'first_name' => 'pramod!');
*/
public function update($table, $values, $condition) {
$sql="update $table SET ";
$c = 0;
if (! empty ( $values )) {
foreach ( $values as $key => $val ) {
if ($c == 0) {
$sql .= "$key='".$val."'";
}else{
$sql .= ", $key='" . $val . "'";
}
$c ++;
}
}
$k = 0;
if (! empty ( $condition )) {
foreach ( $condition as $key => $val ) {
if ($k == 0) {
$sql .= " WHERE $key='".$val."'";
}else{
$sql .= " AND $key='" . $val . "'";
}
$k ++;
}
} else {
return false;
}
$result = $this->dbCon->query ( $sql ) or die ( mysqli_error () );
return $result;
}
/*
* delete function table name, array value
* $where = array('id' =>5,'first_name' => 'pramod');
*/
public function delete($table, $where) {
$sql = "DELETE FROM $table ";
$k = 0;
if (! empty ( $where )) {
foreach ( $where as $key => $val ) {
if ($k == 0) {
$sql .= " where $key='".$val."'";
}else{
$sql .= " AND $key='" . $val . "'";
}
$k ++;
}
} else {
return false;
}
$del = $result = $this->dbCon->query ( $sql ) or die ( mysqli_error () );
if ($del) {
return true;
} else {
return false;
}
}
/*
* select function
* $rows = array('id','first_name','last_name');
* $where = array('id >' =>5,'first_name =' => 'pramod!');
* $order = array('id' => 'DESC','name' => 'ASC');
* $limit = array(20,10);
*/
public function select($table, $rows = '*', $where = null, $order = null, $limit = null) {
if ($rows != '*') {
$rows = implode ( ", ", $rows );
}
$sql = 'SELECT '.$rows.' FROM ' . $table;
if ($where != null) {
$k = 0;
foreach ( $where as $key => $val ) {
if ($k == 0) {
$sql .= " where $key '".$val."'";
}else{
$sql .= " AND $key '" . $val . "'";
}
$k ++;
}
}
$countOrder = 0;
if ($order != null) {
$sql .= " ORDER BY ";
foreach ( $order as $key => $val ) {
if($countOrder > 0){
$sql .= ",";
}
$sql .= " $key " . $val . "";
$countOrder++;
}
}
if ($limit != null) {
$limit = implode ( ", ", $limit );
$sql .= " LIMIT $limit";
}
$result = $this->dbCon->query ( urldecode ($sql));
return $result;
}
public function query($sql) {
$result = $this->dbCon->query ( $sql );
return $result;
}
public function result($result) {
$row = $result->fetch_array ();
$result->close ();
return $row;
}
public function row($result) {
$row = $result->fetch_row ();
$result->close ();
return $row;
}
public function numrow($result) {
$row = $result->num_rows;
$result->close ();
return $row;
}
}
Example of a select using the class:
$database = new MySQL ();
$res = $this->con->select ( 'table_name', array (
'table_field1',
'table_field2'
), array (
'table_field1 = ' => 'Teste1',
'table_field2 = ' => 'Teste2'
),array('table_field1' => 'asc',
'table_field2' => 'desc'
) );
while ( $query = mysqli_fetch_array ( $res ) ) {
echo $query ['table_field1'];
echo $query ['table_field2'];
}