SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Inserting array values in PDO

    Greetings!
    I'm using php pdo in sqlsrv. I'm having trouble inserting my array and other values.
    Here is my code.

    PHP Code:
    $attributes =  $this->sanitized_attributes();
                
    $sql ":";
            
    $sql .= join(" , :"array_values(self::$db_fields));
            
            
    $st $this->db->prepare("USE guidance EXEC guidance.stud_precord_insert {$sql} ");
            foreach(
    $attributes as $key => &$value) {
             
    $k =  ':'.$key;
             
            if (
    $value === '')
            {
                 
    $val 'NULL';
                 
    $par =  PDO::PARAM_NULL;
                 echo 
    "bindValue($k$val,$par)<br/>";
                  
    $st->bindValue($k$val,$par);
            }
            elseif(
    is_array($value))
            {
                foreach(
    $value as $v)
                {
                     if (
    $v === '')
                      {
                      
    $v 'NULL';
                     
    $par =  PDO::PARAM_NULL;
                        echo 
    "bindValue($k$v,$par)<br/>";
                           
    $st->bindValue($k$v,$par);
                      }
                      else
                      {
                        
    $par =PDO::PARAM_STR;
                     echo 
    "bindValue($k$v,$par)<br/>";
                        
    $st->bindValue($k$v,$par);
                       }
                }
                
                 
    $l implode(', '$value);
                       echo 
    "bindValue($k$l,$par)<br/>";
              }
            else{
            
    $val $value;
            
    $par =PDO::PARAM_STR;
            echo 
    "bindValue($k$val,$par)<br/>";
             
    $st->bindValue($k$val,$par);
            }
            
            }
            
             
    $c =    $st->execute(); 
    It works well except that only one value of my array inserted successfully.
    Any help will do. Thank you in advance.

  2. #2
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    tabs.PNG

    perhaps this could help. Thank you in advance.

  3. #3
    SitePoint Addict bronze trophy
    Join Date
    Apr 2013
    Location
    Ithaca
    Posts
    351
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    Why are you trying to insert an array into Database in the very first place? In most cases array or delimited string in a database column signifies that the database structure is poorly designed, unless you have a very good reason.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm hoping to insert those data in my columns. Not supposed to be delimited strings. Thank you , thank you for the response.

  5. #5
    SitePoint Addict bronze trophy
    Join Date
    Apr 2013
    Location
    Ithaca
    Posts
    351
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    Oh I see, you are sending an array of data into database row, not in a column. Sorry about that, I did not read carefully and thought you were trying to send a serialized array or delimited string/array to one database column.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Any idea about it?

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    What does $st->debugDumpParams(); (run after the execute) tell you?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  8. #8
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your response. The attached file displays the number of parameters.
    And when I tried to use var_dump(), its null.

    tabs2.PNG

    Am I getting a positive result here?

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    So what I see here is that it's bound the names correctly.... And I can tell you a problem you have is your value of $k for array values of the attributes array.

    Your array walking doesnt change the value of $k so if you hand this code an array inside the attributes array, it will try and bind every value of the array to the same key.

    What're the contents of $attributes?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    This is pretty very long code. I've been using this as a template.

    Code:
    <?php
    
    
                  protected static $table_name = 'guidance.tbl_stud_precord';
                protected static $db_fields = array('stud_id_string',
                                                                        'stud_nationality',
                                                                        'stud_religion',
                                                                        'stud_bplace',
                                                                        'stud_sibling_rank',
                                                                        'stud_home_add',
                                                                        'stud_provincial_add',
                                                                        'stud_residence_no',
                                                                        'stud_mobile_no',
                                                                        'stud_married',
                                                                        'stud_working',
                                                                        'stud_offspring',
    								    
    								    //working student
    								    
    								    'stud_occ',
    								    'stud_occ_started',
    								    'stud_occ_employer',
    								    'stud_occ_contact',
    								    'stud_occ_add',
    								    
    								    //married student
    								    
    								    'stud_partner_name',
    								    'stud_partner_birthday',
    								    'stud_partner_birthplace',
    								    'stud_partner_nationality',
    								    'stud_partner_religion',
    								    'stud_partner_siblingrank',
    								    'stud_partner_occ',
    								    'stud_partner_emp_add',
    								    
    								    
    								    //student offspring
    								    
    								    'stud_offspring_name',
    								    'stud_offspring_birthday',
    								    'stud_offspring_gender',
    								    'stud_offspring_school',
    								    'stud_offspring_school_level'
    								    
                                                                        );
               
                 public $stud_id_string;
                 public $stud_nationality;
                 public $stud_religion;
                 public $stud_birthday;
    	      public $stud_birthplace;
                 public $stud_sibling_rank;
                 public $stud_home_add;
                 public $stud_provincial_add;
                 public $stud_residence_no;
                 public $stud_mobile_no;
                 public $stud_married;
                 public $stud_working;
                 public $stud_offspring;
    	     
    	       //working student
    	       public $stud_occ;
    	       public $stud_occ_started;
    	       public $stud_occ_employer;
    	       public $stud_occ_contact;
    	       public $stud_occ_add;
    	       
    	         //married student
    		 
    		 public $stud_partner_name;
    		 public $stud_partner_birthday;
    		 public $stud_partner_birthplace;
    		 public $stud_partner_nationality;
    		 public $stud_partner_religion;
    		 public $stud_partner_siblingrank;
    		 public $stud_partner_occ;
    		 public $stud_partner_emp_add;
    		 
    		 
    		 //student offspring
    		  //
    		  public $stud_offspring_name;
    		  public $stud_offspring_birthday;
    		  public $stud_offspring_gender;
    		  public $stud_offspring_school;
    		  public $stud_offspring_school_level;
    		 
    	       
    
        private static function instantiate($attributes)
        {
            // Could check that $record exists and is an array
            $object = new self;
            // Simple, long-form approach:
            // $object->id 				= $record['id'];
            // $object->username 	= $record['username'];
            // $object->password 	= $record['password'];
            // $object->first_name = $record['first_name'];
            // $object->last_name 	= $record['last_name'];
            
            // More dynamic, short-form approach:
          
            foreach ($record as $attribute => $value) {
                if ($object->has_attribute($attribute))
    	    {
                    $object->$attribute = $value;
                }
            }
            return $object;
        }
        
          private function has_attribute($attribute)
        {
            // We don't care about the value, we just want to know if the key exists
            // Will return true or false
            return array_key_exists($attribute, $this->attributes());
        }
        
           
     protected function attributes()
        {
            // return an array of attribute names and their values
            $attributes = array();
            foreach (self::$db_fields as $field) {
                if (property_exists($this, $field)) {
                    $attributes[$field] = $this->$field;
        }
            }
            return $attributes;
        }
       
       protected function sanitized_attributes() {
    	  global $database;
    	  $clean_attributes = array();
    	  // sanitize the values before submitting
    	  // Note: does not alter the actual value of each attribute
    	  foreach($this->attributes() as $key => $value){
    	    
    	    $clean_attributes[$key] = $this->clean($value);
    	  
    	  }
    	  return $clean_attributes;
    	}
    	
    	
    	function clean($var) {
        if(is_array($var)) {
            array_map('self::clean',$var);
        } else {
            $var = htmlentities(strip_tags($var),ENT_QUOTES);
        }
        return $var;
    }
    	
    	
    	
      
        
          public function create() {
    		// Don't forget your SQL syntax and good habits:
    		// - UPDATE table SET key='value', key='value' WHERE condition
    		// - single-quotes around all values
    		// - escape all values to prevent SQL injection
    		
            	
    		$attributes =  $this->sanitized_attributes();
    		
    	        $sql = ":";
    		$sql .= join(" , :", array_values(self::$db_fields));
    		
    		$this->db->beginTransaction();
    		
    		
    		$st = $this->db->prepare("USE guidance EXEC guidance.stud_precord_insert {$sql} ");
    		foreach($attributes as $key => &$value) {
    		 $k =  ':'.$key;
    		 
    		if ($value === '')
    		{
    		     $val = 'NULL';
    		     $par =  PDO::PARAM_NULL;
    		     echo "bindValue($k, $val,$par)<br/>";
    		      $st->bindValue($k, $val,$par);
    		}
    		
    		
    		elseif(is_array($value))
    		{
    			foreach($value as $v)
    			{
    			     if ($v === '')
    			      {
    				  $v = 'NULL';
    				 $par =  PDO::PARAM_NULL;
    				    echo "bindValue($k, $v,$par)<br/>";
    				       $st->bindValue($k, $v,$par);
    			      }
    			      else
    			      {
    				    $par =PDO::PARAM_STR;
    				 echo "bindValue($k, $v,$par)<br/>";
    				    $st->bindValue($k, $v,$par);
    			       }
    			       
    			        //$c =    $st->execute();
    			}
    			
    			
    		  }
    		else{
    		$val = $value;
    		$par =PDO::PARAM_STR;
    		echo "bindValue($k, $val,$par)<br/>";
    		 $st->bindValue($k, $val,$par);
    		}
    		
    		}
    		
    		 $c =    $st->execute();
    		 $this->db->commit();
    		 $rc =    $st->rowCount();
    		 $e = $st->errorCode() ;
    		 $arr = $st->errorInfo() ;
    		 //echo $c.'<br/>';
    		 echo $rc.'<br/>';
    		 echo $e;
    		 print_r($arr);
    	}
        
        
       
       
       }
    
    ?>

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    var_dump or print_r $attributes somewhere in your code after this line:
    PHP Code:
    $attributes =  $this->sanitized_attributes(); 
    As your initial image doesn't show something that matches the way this code should execute. (There should only be 1 entry in a row for a given field)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Sir Starlion, thank you for keeping my thread alive. I did what you said.
    Below are the values of my attributes.

    Code:
    Array
    (
        [stud_id_string] => 
        [stud_nationality] => test
        [stud_religion] => test
        [stud_bplace] => test
        [stud_sibling_rank] => 12
        [stud_home_add] => test
        [stud_provincial_add] => 
        [stud_residence_no] => 
        [stud_mobile_no] => 
        [stud_married] => 
        [stud_working] => 
        [stud_offspring] => 1
        [stud_occ] => 
        [stud_occ_started] => 
        [stud_occ_employer] => 
        [stud_occ_contact] => 
        [stud_occ_add] => 
        [stud_partner_name] => 
        [stud_partner_birthday] => 
        [stud_partner_birthplace] => 
        [stud_partner_nationality] => 
        [stud_partner_religion] => 
        [stud_partner_siblingrank] => 
        [stud_partner_occ] => 
        [stud_partner_emp_add] => 
        [stud_offspring_name] => Array
            (
                [0] => test2
                [1] => test4
            )
    
        [stud_offspring_birthday] => Array
            (
                [0] => 2009-02-03
                [1] => 1997-10-17
            )
    
        [stud_offspring_gender] => Array
            (
                [0] => female
                [1] => male
            )
    
        [stud_offspring_school] => Array
            (
                [0] => 
                [1] => 
            )
    
        [stud_offspring_school_level] => Array
            (
                [0] => 
                [1] => 
            )
    
    )

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    okay... and how do you name offspring 1 and offspring 2? A database insert cannot take 2 values for the same field in a single entry. Are you trying to insert multiple rows?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  14. #14
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yes and I think that is my problem. Inserting array and non-array values at once.

  15. #15
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I can see light in this http://wiki.hashphp.org/PDO_Tutorial...SQL_Developers

    I made a flag when a user has offspring. This is how I made it.
    Code:
     if ($offspring  = 1)
    		 {
    		  
    		  $attributes_array =  $this->sanitized_attributes_for_array();
    		  
    		    $sql2 = ":";
    		    $sql2 .= join(" , :", array_values(self::$db_fields_array));
    	       
    	
    		   $sql3 = ("    DECLARE @lastid int
    				      SELECT @lastid = MAX(stud_id)  FROM guidance.tbl_stud_precord
    				       IF @lastid IS NOT NULL
    				        INSERT INTO guidance.tbl_stud_offspring
    					   ( stud_off_name,
    					    stud_off_birthday ,
    					    stud_off_gender ,
    					    stud_off_school,
    					    stud_off_school_level )	
    				       VALUES ( {$sql2} ) " );
    		        
    			
    			    $st2 = $this->db->prepare($sql3);
    			    
    			foreach($attributes_array as $key => &$value)
    			{
    			     
    			      $k =  ':'.$key;
    			      echo '<pre>';
    			      $name = '';
    			      $st2 = $this->db->prepare($sql3);
    			
    			     $st2->bindParam($k, $name, PDO::PARAM_STR);
    			 
    			       foreach($value as $name )
    				     {
    				      
    					  if ($v === '')
    				       	     {
    					       $v = 'NULL';
    					      $par2 =  PDO::PARAM_NULL;
    					     }
    					   else
    					   {
    					      $par2 =PDO::PARAM_STR;
    					    }
    					       $st2->execute();
    				     }
    			}
    
    		 }
    My problem is where can I replace
    Code:
    $st2->bindParam($k, $name,PDO::PARAM_STR);
    to

    Code:
    $st2->bindParam($k, $name,$par2);

    Any help.

  16. #16
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    myfix. Thank you.
    Code:
    $t = count(array_keys($value));
    			 for($i=0;$i<$t;$i++)
    			  {
    				 $c =    $st2->execute();
    			  }


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •