luni, 22 iulie 2013

Php si Mysql - clasa de lucru cu comenzile de baza mysql

Am avut nevoie acum ceva timp de o clasa pentru lucrul cu mysql si am creeat o clasa minimala pentru comenzile de baza. Comenzile de baza Mysql sunt: select, insert, update și delete. E o clasa foarte simpla dar își face treaba foarte bine. Folosesc doar conexiune mysql temporara. După fiecare operațiune eliberez resursele și închid conexiunea. Singura chestiune pe care nu am luat-o în calcul este sql injection, dar o sa am un post viitor doar despre acest subiect.
 

<?php

/*
 +----------------------------------------------------------------------+
 |  MySqlLibClass  version 1.0
 +----------------------------------------------------------------------+
 | Copyright (C) Simedru Florin 2013,http://automatic-house.blogspot.ro/
 +----------------------------------------------------------------------+
 | This program is free software; you can redistribute it and/or modify |
 | it under the terms of the GNU General Public License as published by |
 | the Free Software Foundation; either version 2 of the License, or    |
 | (at your option) any later version.                                  |
 | This program is distributed in the hope that it will be useful       |
 | but WITHOUT ANY WARRANTY; without even the implied warranty of       |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the         |
 | GNU General Public License for more details.                         |
 +----------------------------------------------------------------------+
 | Author: Simedru Florin                                               |
 +----------------------------------------------------------------------+
*/

class MySqlLibClass{

  var $host_db;    // MySql host name
  var $nume_db;    // Database name
  var $user_db;    // User name
  var $parola_db;  // Password
  var $prefix_db;  // prefixul pentru toate tablele din baza de date 

//******************************************************************************
// Nume functie:MySqlLibClass($hostname_db,$name_db,$username_db,$password_db,$prefix_db)
// Descriere:Constructorul clasei  MySqlLibClass - seteaza datele de conectare la MySQL 
// Returneaza: none
//******************************************************************************

function MySqlLibClass($hostname_db,$name_db,$username_db,$password_db,$prefix_db)
{  
  $this->host_db = $hostname_db;
  $this->nume_db = $name_db;
  $this->user_db = $username_db;
  $this->parola_db = $password_db;
  $this->prefix_db="".$prefix_db."";  
}

//******************************************************************************
// Nume functie : setDBHost($host) ... 
// Descriere :functii utile pentru manipularea mai usoara a datelor de conexiune
// Returneaza : dupa caz 
//******************************************************************************
function setDBHost($host)
{
  $this->host_db = $host;
}
function setDBName($name)
{
  $this->nume_db = $name;
}  
function setDBUser($user)
{
$this->user_db = $user; 
}  
function setDBPass($pass)
{
 $this->parola_db = $pass; 
}  
function getDBHost()
{
  return $this->host_db;
}  
function getDBName()
{
 return $this->nume_db;
}  
function getDBUser()
{
return $this->user_db; 
}  
function getDBPass()
{
 return  $this->parola_db; 
} 

//******************************************************************************
// Nume functie : connect()
// Descriere : Realizeaza conexiunea MySQL
// Returneaza : $conn -> conexiunea la serverul MySql
//******************************************************************************
function connect()
{
  $conn=0;
  $conn = mysql_connect($this->host_db,$this->user_db,$this->parola_db)  or die( "Nu ma pot conecta >> ".mysql_error()." >> $query "." 
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); mysql_select_db($this->nume_db,$conn) or die("Eroare in query
>>$query
Nu pot sa selectez baza de date >> ".mysql_error()." >> >$this->nume_db<"); return $conn; } //****************************************************************************** // Nume functie : close($conn) // Descriere : Inchide conexiunea MySQL // Returneaza : rezultat //****************************************************************************** function close($conn) { return mysql_close($conn); } //****************************************************************************** // Nume functie : query($query) // Descriere : executa si afiseaza rezultatele unei interogari SQL tabelar // Returneaza : numele coloanelor si datele interogarii //****************************************************************************** function query_html($query) { $nr_col=0; $form_query =""; $result=0; $i=0; $j=0; $conexiune = $this->connect(); $result = mysql_query($query,$conexiune) or die("Error in query
>>$query
Query failed >>$query".mysql_error()); $nr_col = mysql_num_fields($result); $html_retVal.=""; $html_retVal.= ""; for($j=0;$j<$nr_col;$j++) { $fieldname[$j] = mysql_field_name($result,$j); $html_retVal.= ""; } $html_retVal.=""; while($line = mysql_fetch_array($result, MYSQL_ASSOC)) { $html_retVal.=""; for($i=0;$i<$nr_col;$i++) { $fieldname[$i] = mysql_field_name($result,$i); $values[$i] = @$line["$fieldname[$i]"]; $html_retVal.= ""; } $html_retVal.= ""; } $html_retVal.="
".$fieldname[$j]."
".$values[$i]."

"; @mysql_free_result($result); $this->close($conexiune); return $html_retVal; } //****************************************************************************** // Nume functie : query($query) // Descriere : executa o interogare MySql // Returneaza : numele coloanelor si datele interogarii //****************************************************************************** function query($query) { $result=0; $conexiune = $this->connect(); $result = mysql_query($query,$conexiune) or die("Error in query
>>$query
Query failed >>$query".mysql_error()); return $result; } //****************************************************************************** // Nume functie :insert_record_simple ($table) // Descriere : insereaza o inregistrare noua in tabela aferenta // Returneaza : string query = interogare //****************************************************************************** function insert_record($table) { $conn=0; $lista_values= ""; $lista_fields = ""; $conn=$this->connect(); $query = "select * from $table"; $result = mysql_query($query,$conn) or die("Error in query
>>$query
Query failed
".mysql_error()); $nr_col = mysql_num_fields($result); $j=0; for($i=0;$i<$nr_col;$i++) { $fieldname[$i] = mysql_field_name($result,$i); $values[$i] = @$_POST[$fieldname[$i]]; if(is_array($_POST[$fieldname[$i]]) == "Array") { $values[$i] = ""; $values[$i] = @$_POST[$fieldname[$i]][0]; } else $values[$i] = @$_POST[$fieldname[$i]]; $lista_values= $lista_values."'".$values[$i]."',"; $lista_fields = $lista_fields.$fieldname[$i]." , "; } $length_values = strlen($lista_values); $length_fields = strlen($lista_fields); $lista_values=substr($lista_values, 0, $length_values-1); $lista_fields=substr($lista_fields, 0, $length_fields-2); $query = "INSERT INTO $table ($lista_fields) VALUES ($lista_values);"; $result = mysql_query($query) or die("Error in query
>>$query
Query failed
".mysql_error()); @mysql_free_result($result); mysql_close($conn); return $query; } //****************************************************************************** // Nume functie :get_data($table,$fieldname_input,$fieldname_return,$value) // Descriere : returneaza valoarea campului $fieldname_input in cazul in care // $fieldname_input='$value' // select * from $table where $fieldname_input='$value' // Returneaza : string //****************************************************************************** function get_data($table,$fieldname_input,$fieldname_return,$value) { $conn=0; $conn=$this->connect(); $query1 = "select * from $table where $fieldname_input='$value'"; $result1 = mysql_query($query1,$conn) or die("Error in query
>>$query
Error : Query failed : >>".mysql_error()."$query1"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); while($line1 = mysql_fetch_array($result1, MYSQL_ASSOC)) { $ret_value = $line1["$fieldname_return"]; $ret_value = str_replace("<","<",$values2 ); $ret_value = str_replace(">",">",$values2 ); return $ret_value; } } //****************************************************************************** // Nume functie : set_data_ID // Descriere : Executa un update mysql // Returneaza : none //****************************************************************************** function set_data_ID($table,$fieldname,$data,$id) { $data = str_replace("<","<",$data ); $data = str_replace(">",">",$data ); $conn=0; $conn=$this->connect(); $query = "UPDATE $table SET $fieldname='$data' where ID='$id'"; $result = mysql_query($query,$conn) or die("Error in query
>>$query
set_data_user: Query failed: >>".mysql_error()." Query>> $query1"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); @mysql_free_result($result); mysql_close($conn); } //****************************************************************************** // Nume functie : delete_record_ID // Descriere : Executa un delete mysql pentru intregistarea cu ID-ul selectat // Returneaza : none //****************************************************************************** function delete_record_ID($table,$ID) { $conn=0; $conn=$this->connect(); mysql_select_db($this->nume_db,$conn) or die("Error in query
>>$query
update_record_ext: Query failed: >>".mysql_error()."$query"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); $query = "DELETE FROM $table WHERE ID='$ID'"; $result = mysql_query($query) or die("Error in query
>>$query
delete_record: Query failed : >>".mysql_error()."$query"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); if($result != FALSE ) { $html_ret_value .= "
Înregistrarea $ID a fost stearsa ."; } else { $html_ret_value .= "
Înregistrarea $ID nu a fost stearsa!"; } @mysql_free_result($result); mysql_close($conn); return $html_ret_value; } //****************************************************************************** // Nume functie : delete_record // Descriere : Executa un delete mysql pentru intregistarea cu $fieldname-ul // selectat egal cu $value // DELETE FROM $table WHERE $fieldname='$value' // Returneaza : none //****************************************************************************** function delete_record($table,$fieldname,$value) { $conn=0; $conn=$this->connect(); mysql_select_db($this->nume_db,$conn) or die("Error in query
>>$query
update_record_ext: Query failed: >>".mysql_error()."$query"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); $query = "DELETE FROM $table WHERE $fieldname='$value'"; $result = mysql_query($query) or die("Error in query
>>$query
delete_record: Query failed : >>".mysql_error()."$query"."
>> Line:".__LINE__."
>>File:". __FILE__."
>>Function :".__FUNCTION__."
>>Class :".__CLASS__."
"); if($result != FALSE ) { $html_ret_value .= "
Înregistrarea $value a fost stearsa ."; } else { $html_ret_value .= "
Înregistrarea $value nu a fost stearsa!"; } @mysql_free_result($result); mysql_close($conn); return $html_ret_value; } } ?>
Sper sa fie de folos cuiva.
O zi buna tuturor!

Niciun comentariu:

Trimiteți un comentariu

Moderatorul îşi rezervă dreptul de a şterge anumite comentarii nepotrivite. (cuvinte jignitoare, rasiste sau altele)