0

I would like to add mass data to a mysql database using pdo
I want to update about 200 rows of data, each row 10 fields, i used four here below
connection to db is fine, i tested some result queries
fields are filled using foreach, which works also fine, i verified content
the problem however is the execute function, the execution gives an error and no data is inserted

what to do please, hereby present code

//set db
$host   = 'localhost';
$dbname = 'dbdata';
$attrs = array(PDO::ATTR_PERSISTENT => true);
$dbHandle = new PDO("mysql:host=$host;dbname=$dbname",'dbdatatable','tblpwd');
$dbHandle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//===================================================
//prepare sql
$sql = "UPDATE TickersList SET field1=?, field2=?, field3=? WHERE field4=?";
$STH = $dbHandle->prepare($sql);
//===================================================
//declare arrays 
$ufield1 = array();
$ufiled2 = array();
$ufiled3 = array();
$ufiled4 = array();
//===================================================
//fill arrays with data
foreach( $this->_data as $qty ){
$ufield1 [] = $qty->data1;
$ufield2 [] = $qty->data2;
$ufield3 [] = $qty->data3;
$ufield4 [] = $qty->data4;
}
//===================================================
//execute query - not working :(
$STH->execute($sql,$ufield1,$ufield2,$ufield3);
//===================================================
ProgramFOX
  • 6,131
  • 11
  • 45
  • 51
Pavel
  • 1
  • 1

1 Answers1

0

Your call to execute method is wrong and at wrong place. You should do it like this:

//fill arrays with data
foreach( $this->_data as $qty ){

$params = array($qty->data1,$qty->data2,$qty->data3,$qty->data4);
$STH->execute($params);

}
hynner
  • 1,352
  • 1
  • 11
  • 20
  • Thx yr feedback hynner. That works indeed. Unfortunately this is direct pdo execution and is hardly faster than classic mysqlquery. (14 seconds for 200 rows of data) I would like to use prepared query execution which is recommended for speed and security. Any idea? – Pavel Jun 20 '13 at 21:06
  • obviously you´re doing something very wrong. Which table engine do you use? My guess would be InnoDB, which is slower for writing.Try using MyISAM if you don´t really need InnoDB, if you do try looking [here](http://stackoverflow.com/questions/7585301/how-tos-for-mysql-innodb-insert-performance-optimization) for some optimization tips – hynner Jun 21 '13 at 05:08
  • yes, i am using MYISAM, but i'll try to optimise. The reason i wanted pdo is bc table has 16000 rows and maximum query result with fresh data is 200 rows. That means only after 80x14" or 20 minutes roughly refresh of full table can resume. I selected for pdo based on this document http://www.ilia.ws/files/quebec_PDO.pdf – Pavel Jun 21 '13 at 12:32
  • yep, after improve of table, i am satisfied about result. 16000 rows are updated within 1'. Doing the execute after the foreach however i couldn't find. Mny thx hynner! – Pavel Jun 21 '13 at 14:17