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

De admin 22/07/2013
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.="

<table width=100%>";
  $html_retVal.= "

<tr>";
  for($j=0;$j<$nr_col;$j++)
  {
    $fieldname[$j] = mysql_field_name($result,$j);
    $html_retVal.= "

<td><b>".$fieldname[$j]."</b></td>

";
 }
 $html_retVal.="</tr>

";
  while($line = mysql_fetch_array($result, MYSQL_ASSOC))
  {  
   $html_retVal.="

<tr >";
    for($i=0;$i<$nr_col;$i++)
    {
      $fieldname[$i] = mysql_field_name($result,$i);
      $values[$i] = @$line["$fieldname[$i]"];
      $html_retVal.= "

<td>".$values[$i]."</td>

";
    } 
   $html_retVal.= "</tr>

";
 } 
 $html_retVal.="</table>


";
 @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 <b>nu</b> 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 <b>nu</b> a fost stearsa!";
  }
  @mysql_free_result($result);
  mysql_close($conn);
  return $html_ret_value;
}

}
?>
Sper sa fie de folos cuiva.
O zi buna tuturor!