Active Record (AR) och MYSQL

11 september, 2014

Tjenare alla

jag undrar hur man sätter på fel läge i mysql så att den ge från sig fel exempel man har ett fält med heltal och man försöker lägga in en sträng i databasen ?, jag själv använder mysqli.

jag vet att det går kolla innan man gör en sql frågan körs.

en del av koden kommer från min gamla userRecord som jag har gjort om till Record.

klassen gör att när du skapar en objekt och sedan använder save() funktionen då skapas tabellen med de egenskaperna med rätt typer och man kan lägga till egenskaperna och sparar
du kan också skicka in ett id till save() då uppdaterar den raden. du har också remove($id) sedan load('id',$id) och loadarr('id',$id) .

<?
class Record{
    
    private $data = array();
    private $table;
    private $addField;
    
    function __construct($name){
        $this->table = $name;
    }
    
    private function _createTable($table){
	$db = new DatabaseConnection();
	$sql='CREATE TABLE  `'.$db->mysql_escape_mimic($table).'` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY);';
	return $db->execute($sql);
    }
    
    private function _addField($key, $opition,$aftername=""){
	$db = new DatabaseConnection();
	if($aftername!=""){
	  $aftername = ' AFTER `'.$db->mysql_escape_mimic($aftername).'` ';
	}
	$this->addField.= 'ADD `'.$db->mysql_escape_mimic($key).'` '.$opition.$aftername.',';
    }
    
    private function _saveToTable($table){
	$db = new DatabaseConnection();
	if(substr($this->addField, -1) ==","){
	$this->addField = substr_replace($this->addField ,"",-1);	
	}
	$db->execute('ALTER TABLE `'.$table.'` '.$this->addField);
    }
    
    
    private function makeoptionsfield($string){
        if(gettype($string) == "array" || gettype($string) == "object" || gettype($string) == "resource"){
            $options = 'text NOT NULL';
        }
        
        if(gettype($string) == "string" || gettype($string) == "null"){
          if(strlen($string)<=255){
           $options = 'varchar(255) NOT NULL';
           }else{
           $options = 'text NOT NULL';
           }
       }
        
      
        if(gettype($string) == "boolean"){
            $options = 'tinyint(1) NOT NULL';
        }
        
        if(gettype($string) == "integer"){
            if(strlen($string)<=11){
                $options = 'int(11) NOT NULL';
            }else{
                $options = 'bigint(20) NOT NULL';
            }
        }
        
        return $options;
    } 
    
    private function _checkMysqlTable($err){
	$check = false;

		if(!empty($err)){
			
                    if(preg_match("/\.(.*)' doesn't /i", $err[0],$out)){
                    
                            $this->_createTable($out[1]);
                            foreach($this->data as $field => $data){
                                    if($field!='id'){
                                    $this->_addField($field,$this->makeoptionsfield($data));	
                                    }
                            }
                            $this->_saveToTable($out[1]);
                            
                            $check = true;
                            
                    }else{
                            $aftername="";
                            foreach($this->data as $key => $value){
                                 
                                 if(preg_match('/Unknown column \''.$key.'\' in \'field list\'/i', $err[0])){
                                    
                                    $this->_addField($key,$this->makeoptionsfield($value),$aftername);  
                                 }
                                 $aftername = $key;
                            }
                            $this->_saveToTable($this->table);
                    
                            $check = true;
                    }
				
		}
		return $check;
	} 
    
    
    function save($id=null){
        $db = new DatabaseConnection();
        $db->getError = true;
        
        if($this->table==''){
            throw new Exception('Du måste ange en tabellnamn innan du använder record klassen');
        }
        
        if($id==null){
            
             if($this->password!=false){   
            $new = $this->createpassword($this->password);
            $this->salt = $new['salt'];
            $this->password = $new['password'];
            $this->algorithm = $new['algorithm'];
            }
            $sql = new Querybuilder();
		$c = $values = $this->data;
            $id = $db->insertRow($sql->insert($this->table,$c));
            
            
            $err = $db->getErrorMessage();
	    
            $db->getError = false;
			
            if($this->_checkMysqlTable($err)){
            
                $id = $db->insertRow($sql->insert($this->table,$c));
            }
                return $id;
        }else{
            if($id > 0){

                 if($this->password!=false){
                 $new = $this->createpassword($this->password);
                 $this->password = $new['password'];
                 $this->salt = $new['salt'];
                 $this->algorithm = $new['algorithm'];
                 }

                $values = $this->data;
                $set = array();
                $i=0;
                
                foreach($values as $key => $value){
                    if(!empty($value)){
                            $data = $value;
                            $set[++$i]=$key.'="'.$data.'"';

                    }
                }

            $getdata = $db->updateRow('UPDATE `%s` SET '.join(", ",$set).' WHERE id=%d',$this->table,$id);

            $err = $db->getErrorMessage();
	    
            $db->getError = false;
             if($this->_checkMysqlTable($err)){
            
                $getdata = $db->updateRow('UPDATE `%s` SET '.join(", ",$set).' WHERE id=%d',$this->table,$id);
            }
            return $getdata;
            }
        }
    }
    
    function load($field,$word){
        $db = new DatabaseConnection();

        $record = $db->text->getSingleRow('SELECT * FROM '.$this->table.' WHERE %s = "%s"',$field,$word);
        if ($record) {
            $user = new Record($this->table);
            $user->assign($record);
            return $user;
        }
        return null;
    }
    
    function loadArr($field,$word){
        $db = new DatabaseConnection();
        $record = $db->getSingleRow('SELECT * FROM users WHERE %s = "%s"',$field,$word);
        return $record;
    }
    
    function remove($id=null){
            
            if($this->table==''){
            throw new Exception('Du måste ange en tabellnamn innan du använder record klassen');
            }
        
       
            if($id!=null){
            $db = new DatabaseConnection();
                  $db->delete('DELETE FROM `%s` WHERE `usera`.`id` = %d',$this->table,$id);
            }
    }
        
    private function createpassword($plain, $algorithm=null) {
        $password = array(
          'algorithm'=>($algorithm ? $algoritm : 'sha1salt'),
          'salt'=>null
        );
        switch($password['algorithm']) {
          case 'sha1salt':
          $password['salt'] = sha1(microtime());
          $password['password'] = sha1($password['salt'].$plain);
          break;
          case 'md5salt':
          $password['salt'] = md5(microtime());
          $password['password'] = md5($password['salt'].$plain);
          break;
          case 'sha1':
          $password['password'] = sha1($plain);
          break;
          case 'md5':
          $password['password'] = md5($plain);
          break;
          case 'plain':
          $password['password'] = $plain;
          break;
          default: throw new Exception('Unknown hashing algorithm');
        }
        return $password;
    }
        
    public static function getcountrybyip($ip){

	$url = 'http://www.ipnr.nu/ajax/';
	    $myvars = 'ip='.$ip;
	    
	    $ch = curl_init($url);
	    curl_setopt( $ch, CURLOPT_POST, 1);
	    curl_setopt( $ch, CURLOPT_POSTFIELDS, $myvars);
	    curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, 1);
	    curl_setopt( $ch, CURLOPT_HEADER, 0);
	    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
	    
	    $response = curl_exec( $ch );
	    
	return  json_decode($response);
    }

    public static function getrealipaddr(){
        if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
        {
          $ip=$_SERVER['HTTP_CLIENT_IP'];
        }
        elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
        {
          $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
        }
        else{
          $ip=$_SERVER['REMOTE_ADDR'];
        }
        return $ip;
    }
    
    public function assign($record){
        foreach($record as $key => $value){
            $this->{$key} = $value;
        }
    }
     
    function __set($name,$value){
       $this->data[$name] = $value;
    }
    function __get($name){
         if(key_exists($name,$this->data)){
              return $this->data[$name];
         }else{
              return false;
         }
    }
    
    function getData(){
         return $this->data;
    }
}

test kod

class userAR extends Record{
	
	function __construct(){
		parent::__construct('users'); 
	}
}

$user = new userAR();
$user->name = 'marcus';
$user->last = 'larsson';
$user->image = 'image.png';
$user->sex = 1;
$user->username = 'mackan';
$user->epost = 'mackan@mac3.se';
$user->city = 'karlstad';
$user->iso = 'SE';
print $user->name;

$user->save(3);

$usr = new userAR();
$data = $usr->load('id',3);
$data->name = 'Marcus';
$data->save($data->id);

$new = $usr->load('id',3);

print_r($data);

för kuna testa koden få man byta ut de databas objektena

11 september, 2014

Exceptions

Jag ser att du jobbar bra.

12 september, 2014

tack

jag har byggt in en bind funktion

<?
class Record{
    
    private $data = array();
    private $table;
    private $addField;
    
    function __construct($name){
        $this->table = $name;
    }
    
    private function _createTable($table){
	$db = new DatabaseConnection();
	$sql='CREATE TABLE  `'.$db->mysql_escape_mimic($table).'` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY);';
	return $db->execute($sql);
    }
    
    private function _addField($key, $opition,$aftername=""){
	$db = new DatabaseConnection();
	if($aftername!=""){
	  $aftername = ' AFTER `'.$db->mysql_escape_mimic($aftername).'` ';
	}
	$this->addField.= 'ADD `'.$db->mysql_escape_mimic($key).'` '.$opition.$aftername.',';
    }
    
    private function _saveToTable($table){
	$db = new DatabaseConnection();
	if(substr($this->addField, -1) ==","){
	$this->addField = substr_replace($this->addField ,"",-1);	
	}
	$db->execute('ALTER TABLE `'.$table.'` '.$this->addField);
    }
    
    
    private function makeoptionsfield($string){
        if(gettype($string) == "array" || gettype($string) == "object" || gettype($string) == "resource"){
            $options = 'text NOT NULL';
        }
        
        if(gettype($string) == "string" || gettype($string) == "null"){
          if(strlen($string)<=255){
           $options = 'varchar(255) NOT NULL';
           }else{
           $options = 'text NOT NULL';
           }
       }
        
      
        if(gettype($string) == "boolean"){
            $options = 'tinyint(1) NOT NULL';
        }
        
        if(gettype($string) == "integer"){
            if(strlen($string)<=11){
                $options = 'int(11) NOT NULL';
            }else{
                $options = 'bigint(20) NOT NULL';
            }
        }
        
        return $options;
    } 
    
    private function _checkMysqlTable($err){
	$check = false;

		if(!empty($err)){
			
                    if(preg_match("/\.(.*)' doesn't /i", $err[0],$out)){
                    
                            $this->_createTable($out[1]);
                            foreach($this->data as $field => $data){
                                    if($field!='id'){
                                    $this->_addField($field,$this->makeoptionsfield($data));	
                                    }
                            }
                            $this->_saveToTable($out[1]);
                            
                            $check = true;
                            
                    }else{
                            $aftername="";
                            foreach($this->data as $key => $value){
                                 
                                 if(preg_match('/Unknown column \''.$key.'\' in \'field list\'/i', $err[0])){
                                    
                                    $this->_addField($key,$this->makeoptionsfield($value),$aftername);  
                                 }
                                 $aftername = $key;
                            }
                            $this->_saveToTable($this->table);
                    
                            $check = true;
                    }
				
		}
		return $check;
	} 
    
    
    function save($id=null){
        $db = new DatabaseConnection();
        $db->getError = true;
        
        if($this->table==''){
            throw new SystemException('Du måste ange en tabellnamn innan du använder record klassen');
        }
        
        if($id==null){
            
            if($this->password!=false){
            $new = $this->createpassword($this->password);
            $this->salt = $new['salt'];
            $this->password = $new['password'];
            $this->algorithm = $new['algorithm'];
            }
            $sql = new Querybuilder();
		$c = $this->data;
            $id = $db->insertRow($sql->insert($this->table,$c));
            
            
            $err = $db->getErrorMessage();
	    
            $db->getError = false;
			
            if($this->_checkMysqlTable($err)){
            
                $id = $db->insertRow($sql->insert($this->table,$c));
            }
                return $id;
        }else{
            if($id > 0){

                if($this->password!=false){
                 $new = $this->createpassword($this->password);
                 $this->password = $new['password'];
                 $this->salt = $new['salt'];
                 $this->algorithm = $new['algorithm'];
                 }

                $values = $this->data;
                $set = array();
                $i=0;
                
                foreach($values as $key => $value){
                    if(!empty($value)){
                            $data = $value;
                            $set[++$i]=$key.'="'.$data.'"';

                    }
                }

            $getdata = $db->updateRow('UPDATE `%s` SET '.join(", ",$set).' WHERE id=%d',$this->table,$id);

            $err = $db->getErrorMessage();
	    
            $db->getError = false;
             if($this->_checkMysqlTable($err)){
            
                $getdata = $db->updateRow('UPDATE `%s` SET '.join(", ",$set).' WHERE id=%d',$this->table,$id);
            }
            return $getdata;
            }
        }
    }
    
    function load($field,$word){
        $db = new DatabaseConnection();

        $record = $db->text->getSingleRow('SELECT * FROM '.$this->table.' WHERE %s = "%s"',$field,$word);
        if ($record) {
            $user = new Record($this->table);
            $user->assign($record);
            return $user;
        }
        return null;
    }
    
    function loadArr($field,$word){
        $db = new DatabaseConnection();
        $record = $db->getSingleRow('SELECT * FROM users WHERE %s = "%s"',$field,$word);
        return $record;
    }
    
    function remove($id=null){
            
            if($this->table==''){
            throw new SystemException('Du måste ange en tabellnamn innan du använder record klassen');
            }
        
       
            if($id!=null){
            $db = new DatabaseConnection();
                  $db->delete('DELETE FROM `%s` WHERE `usera`.`id` = %d',$this->table,$id);
            }
    }
        
    private function createpassword($plain, $algorithm=null) {
        $password = array(
          'algorithm'=>($algorithm ? $algoritm : 'sha1salt'),
          'salt'=>null
        );
        switch($password['algorithm']) {
          case 'sha1salt':
          $password['salt'] = sha1(microtime());
          $password['password'] = sha1($password['salt'].$plain);
          break;
          case 'md5salt':
          $password['salt'] = md5(microtime());
          $password['password'] = md5($password['salt'].$plain);
          break;
          case 'sha1':
          $password['password'] = sha1($plain);
          break;
          case 'md5':
          $password['password'] = md5($plain);
          break;
          case 'plain':
          $password['password'] = $plain;
          break;
          default: throw new SystemException('Unknown hashing algorithm');
        }
        return $password;
    }
        
    public static function getcountrybyip($ip){

	$url = 'http://www.ipnr.nu/ajax/';
	    $myvars = 'ip='.$ip;
	    
	    $ch = curl_init($url);
	    curl_setopt( $ch, CURLOPT_POST, 1);
	    curl_setopt( $ch, CURLOPT_POSTFIELDS, $myvars);
	    curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, 1);
	    curl_setopt( $ch, CURLOPT_HEADER, 0);
	    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
	    
	    $response = curl_exec( $ch );
	    
	return  json_decode($response);
    }

    public static function getrealipaddr(){
        if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
        {
          $ip=$_SERVER['HTTP_CLIENT_IP'];
        }
        elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
        {
          $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
        }
        else{
          $ip=$_SERVER['REMOTE_ADDR'];
        }
        return $ip;
    }
    
    public function assign($record){
        foreach($record as $key => $value){
            $this->{$key} = $value;
        }
    }
     
    function __set($name,$value){
       $this->data[$name] = $value;
    }
    function __get($name){
         if(key_exists($name,$this->data)){
              return $this->data[$name];
         }else{
              return false;
         }
    }
    function getTable(){
         return $this->table;
    }
    
    function where($string=null){
        if($string!=null && $this->bind!=false){
        $sql = "select * FROM ";
	$db = new DatabaseConnection();
        
        	$i = 0;
	foreach($this->bind as $object){
	
	     if($i==0){
		 $sql.= $db->mysql_escape_mimic($object->getTable());
		 $i++;
	     }else{
		$sql.= ','.$db->mysql_escape_mimic($object->getTable());
		    $i++;
	     }
	     
	}
	$sql.=" where ";
       $sql.=$string;
       
        $data =  $db->getRows($sql);
       
       return  $data;
        }
        
    }
    
    function bind(Record $object){
       $object = func_get_args();
        $this->bind = $object;
        return $this;
    }
    function getData(){
         return $this->data;
    }
}

test

$user1 = new Record('users');
$user1->name = 'marcus';
$user1->last = 'larsson';
$user1->image = 'image.png';
$user1->sex = 1;
$user1->username = 'mackan';
$user1->epost = 'mackan@mac3.se';
$user1->city = 'karlstad';
$user1->iso = 'SE';

$user = new Record('permissions');
$user->name = 'marcus';
$user->last = 'larsson';
$user->image = 'image.png';
$user->sex = 1;
$user->username = 'mackan';
$user->epost = 'mackan@mac3.se';
$user->city = 'karlstad';
$user->iso = 'SE';

$bind = new Record();
$bind = $bind->bind($user,$user1)->where('users.id = permissions.id'); //skriver ut  array

Logga in

Active Record (AR) och MYSQL

Rekommendera Poster

mac3

Länk till kommentar

Dela på andra webbplatser

Gäst

Länk till kommentar

Dela på andra webbplatser

mac3

Länk till kommentar

Dela på andra webbplatser

Arkiverat

Vem är online 1 Medlem, 0 anonym, 14 gäster (Visa hela listan)

Populära medlemmar

Publika meddelanden

Senaste som Tittar

Senaste inlägg

Forum

Aktivitet

pcforalla.se