Querying the Database

New Articles

Sneak Past the PHP Upload Limits

The Case Against PHP templates.

Strange Characters and Blobs.

We have already looked at how we can convert our database schema from mysql to postgres using our online converter. We have also taken a look at how we can import the schema into postgres system and how we can connect to it using PHP. Now let's look at how we can actually perform queries against these tables.

Since the existing version of the rad user manager is compatible only with mySql, where ever database needs to be queried it calls the mysql_query() function of PHP. Obviously we need to change that if we are to make the software compatible with postgresql. So we create a new function.

function db_query($query)
{
	global $db_type;
	error_log("\n\n $query");
	
	if($db_type == 'mysql')
	{
		$res = mysql_query($query);
	}	
	else
	{
	
		$res = pg_query($query);
		error_log(pg_last_error());
	}
	return $res;
}

Where ever mysql_query() had been used we will now start using this newly created db_query function. As you can see, it checks the $db_type variable to determine whether the query should be executed against a postgresql system or a mysql database. The variable itself has to be defined the inc/config.php file (which you will find in the download).

	$db_type = "pgsql";

If you are SED or regular expression addict you can now replace all occurrences of mysql_query with db_query many others will be more comfortable using the 'find in files' feature available in modern text editors. Once you have done that let's load up the usermanager in our browser just to see how it looks like since we made the changes. Don't expect things to go smoothly just yet, we have some distance to travel before we are done.

While we are still on the subject of executing queries, lets observe that both systems support the LIMIT clause for SELECTS however there is a slight difference in syntax. While mysql expects it to be in the format LIMIT {number }[,offset], in postgresql there is separate keyword named offset. So in the latter the expected format is LIMIT { number | ALL } [OFFSET number]. That's one more thing you should watch out for when you are searching and replacing. Now let's go on to the next step.

 

  Part 1:   Getting Started   ,   The Schema   ,   Queries
  Part 2:   Passwords   ,   Times Up   ,   Errors   ,   Download