MySQL vs PostgreSQL (Coding)

PostgreSQL

MySQL

$connection_id = pg_connect(“host=$hostname dbname=$database user=$username password=$password”);

$connection_id=mysql_pconnect($hostname, $username, $password);
mysql_select_db($database, $connection_id);

Connection

$result_data = pg_exec($connection_id, “query goes here”);

$result_data = mysql_query(“query goes here”, $connection_id);

Query

$rslt=pg_exec($connection_id, “SELECT * from blah”);
$limit=pg_numrows($rslt);
for($rownum=0;$rownum<$limit;$rownum++)
{$value=pg_fetch_array($rslt, $rownum);
//do stuff here
}

$rslt=mysql_query(“SELECT * FROM blah”, $connection_id);
while($value=mysql_fetch_array($rslt)){
//do data handling stuff
}

Fetching Data

function postg_insert_id($tablename, $fieldname){
global connection_id;
$result=pg_exec($connection_id, “SELECT last_value FROM ${tablename}_
${fieldname}_seq”);
$seq_array=pg_fetch_row($result, 0);
return $seq_array[0];
}

mysql_insert_id()

The row ID of last inserted row

CREATE TABLE practicetable{
someID SERIAL};

CREATE TABLE practicetable(
`someID` int not null auto_increment primary key)

Auto Increment

pg_numrows($result)
pg_cmdtuples($result)
pg_fetch_object($result, $row)
pg_fetch_row($result, $row)
pg_fetch_array($result, $row)

mysql_num_rows($result)
mysql_affected_rows($result)
mysql_fetch_object($result)
mysql_fetch_row($result)
mysql_fetch_array($result)

PHP Functions