4 Replies - 2410 Views - Last Post: 13 June 2011 - 03:54 AM Rate Topic: -----

#1 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

PDO & Prepared Statements - Insert Function

Posted 12 June 2011 - 02:01 PM

Hey,

I'm trying to code a reusable function for inserting data into tables with varying columns and data types. The main issue at hand is how i would pass the data to the function to begin with. I was considering having an array with key value pairings for column and value, then seperating them within the function, and assigning them into a query. Or would i be better off using two seperate arrays for column and value?

Next is the construction of the query itself. How would i go about cycling the array of columns/values and inserting these into the query as column/values?

This may help you understand if i'm not clear here

Is This A Good Question/Topic? 0
  • +

Replies To: PDO & Prepared Statements - Insert Function

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2988
  • Posts: 10,323
  • Joined: 08-August 08

Re: PDO & Prepared Statements - Insert Function

Posted 12 June 2011 - 02:57 PM

I'm guessing you're trying to bind an array, so you'd probably want to do something like this:
for($i = 0; $i < count($arr); $i++) {
	$sth->bindParam($i, $arr[$i]);
}

...er, that probably needs to be:
for($i = 1; $i <= count($arr); $i++) {
	$sth->bindParam($i, $arr[$i-1]);
}

This post has been edited by CTphpnwb: 12 June 2011 - 03:05 PM

Was This Post Helpful? 2
  • +
  • -

#3 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: PDO & Prepared Statements - Insert Function

Posted 13 June 2011 - 12:33 AM

Thats great, thanks!

The code you've given me, should i be editing this to check the data type or the current array value (PDO::PARAM_*) and inserting it into the bindParam statement, or will it work without this?

Any ideas on how i would go about inserting the '?' into the prepared statement?
Counting the array would give me how many i need, but is this how i would construct for the sql query?


$columns = NULL;
for($i = 1; $i <= count($arr); $i++) {
    if($columns = NULL){
         $columns = '?';
    } else {
         $columns.= ', ?';
    }
}

('INSERT INTO $table ($columns) VALUES ($values)');



Or am i as lost as mary's lamb?
Was This Post Helpful? 0
  • +
  • -

#4 E_Geek  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 45
  • View blog
  • Posts: 236
  • Joined: 20-February 11

Re: PDO & Prepared Statements - Insert Function

Posted 13 June 2011 - 02:43 AM

Ok, i've had a crack and came up with the following, could you just let me know if i'm completely wrong and foolish or if i've got the jist of it?

I'm working this function inside a class, which itself is the database object (hence $this->_link)

	function insert_into_table($table, $values, $columns){
	
		//Construct placeholders for columns.
		$ps_columns = NULL;
		for($i = 1; $i <= count($columns); $i++) {
			if($ps_columns = NULL){
				 $ps_columns = '?';
			} else {
				 $ps_columns.= ', ?';
			}
		}
		
		//Construct placeholders for values
		$ps_values = NULL;
		for($i = 1; $i <= count($values); $i++) {
			if($ps_values = NULL){
				 $ps_values = '?';
			} else {
				 $ps_values.= ', ?';
			}
		}
		
		//Prepare the statement
		$ps = $this->_link->prepare('INSERT INTO $table ($ps_columns) VALUES ($ps_values)');
	
		//Bind each column 
		for($i = 1; $i <= count($columns); $i++) {
			$ps->bindParam($i, $columns[$i-1]);
		}
		
		//Bind each value
		for($i = 1; $i <= count($values); $i++) {
			$ps->bindParam($i, $values[$i-1]);
		}
		
		$ps->execute();
		$count = $ps->rowCount();
		
		if $count = 0 {
			return False;
		} else {
			return True;
		}

	}

Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,233
  • Joined: 08-June 10

Re: PDO & Prepared Statements - Insert Function

Posted 13 June 2011 - 03:54 AM

the question marks can be stringified with the help of some native functions:
$str = implode(", ", array_fill(0, $num, "?"));

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1