Thread: Uncertain about recoding prepared statements from MySQL to PostgreSQL
Within a lengthy form script: In MySQL, I had, by way of example, $sql = 'INSERT INTO testee VALUES (? ? ? ? ?)'; $stmt = $dbcx->stmt_init(); $stmt->bind_param('sssss',$protocode,$service,$sample,$normid, $proctored); $stmt->execute(); In PostgreSQL, to accomplish the same thing, I would want to try: $query = "INSERT INTO testee (protocode,service,sample,normid,proctored) VALUES ($1,$2,$3,$4,$5)"; $result = pg_prepare($cnx,"newtestee",$query); pg_execute($cnx,"newtestee",array($protocode,$service,$sample,$normid, $proctored)); Have I got this synyax down correctly? I presume the pg_execute provides the data type information (the 'sssss' in the MySQL bind-param??? I have modified another field (not shown above) as being type TIMESTAMP DEFAULT now(). I assume I would 'pass over' such fields were they to appear in the above example. In MySQL the '?' placeholder in the INSERT was replaced with: current_timestamp(). Thanks for the 'steer'. A few more coversions and I will develop a rhythm of familiarity. For now, I want to avoid any bad habits and faulty thinking. Its much more difficult to UNlearn than it is to learn in the first place. George
On Nov 2, 2007, at 8:13 PM, G. J. Walsh wrote: > $query = "INSERT INTO testee > (protocode,service,sample,normid,proctored) > VALUES ($1,$2,$3,$4,$5)"; > $result = pg_prepare($cnx,"newtestee",$query); > pg_execute($cnx,"newtestee",array($protocode,$service,$sample,$normid, > $proctored)); > > Have I got this synyax down correctly? Looks correct to me. > > I presume the pg_execute provides the data type information (the > 'sssss' > in the MySQL bind-param??? I think the PHP interface sends everything as strings. In general it just works, but suspect there are cases where you might have to provide some explicit typing. > > I have modified another field (not shown above) as being type > TIMESTAMP > DEFAULT now(). I assume I would 'pass over' such fields were they to > appear in the above example. > > In MySQL the '?' placeholder in the INSERT was replaced with: > current_timestamp(). You would want to leave them out of the insert statement or use the key word DEFAULT as the value. See the INSERT docs for more details on this: http://www.postgresql.org/docs/8.2/interactive/sql-insert.html > > Thanks for the 'steer'. A few more coversions and I will develop a > rhythm of familiarity. For now, I want to avoid any bad habits and > faulty thinking. Its much more difficult to UNlearn than it is to > learn > in the first place. Here is a PHP class for calling PostgreSQL functions with prepared statements which might be useful or instructive: http://pgedit.com/resource/php/pgfuncall John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL