Moving from mysql to postgres

New Articles

Saving session data in a database.

Mystery of the dead speaker.

readfile vs include.

By now we have made sufficient progress in our attempt of adding pgsql support for Rad User Manager that we were able to login to the system during the last step. We have a few more changes to make, one of the most important tasks is to complete the user signup section.

Some of our tables have auto_increment fields these are known as sequence fields in postgresql. As you know when a row is inserted into such a table the auto increment field is assigned a sequential number by the database. Sometimes we find that we need to retrieve this number as happens during the sign up process. The mysql_insert_id function of PHP is what is being currently used for this purpose but unfortunately the postgres functions of PHP does not include an equivalent.

Fortunately postgres includes a currval() function which can be used to find the last value picked off the sequence, but before we make use of it we need to side track a bit because the automatically assigned value is meaningless for us if an error was encountered when executing the query. Therefor we need to polish up the error handling before we make use of currval.

	
	function db_error_log()
	{
		global $db_type;
		
		
		if($db_type == 'mysql' && mysql_errno() != 0)
		{
			$errMessage = mysql_error();
			error_log($errMessage);
			return $errMessage;
		}
		else
		{
			$errMessage = pg_last_error();
			error_log($errMessage);
			return $errMessage;
		}
	}

These few lines of code will log the last error reported by the DB. The is usually written to your webserver log file but check your php.ini settings and your webserver's documentation to be sure. The same message is returned to the calling method.

Let's do a search and replace for all occurances of mysql_error with our newly build db_error_log. We need to watch for any occurances of mysql_errno as well. Since it's exact equivalent does not exist on the other system we need to make a slight modification to our code.

	$err = db_error_log();
	if($err !='')
	{
		....
	}

Now that error handling is out of the way, let's create a function to retrieve the insert id. Notice that it accepts a single parameter which is the name of the sequence in pgsql but needs not be defined in mysql. So we give it a default value.

	function db_error_log()
	{
		global $db_type;
		
		
		if($db_type == 'mysql')
		{
			if(mysql_errno() != 0)
			{
				$errMessage = mysql_error();
				error_log($errMessage);
				return $errMessage;
			}
		}
		else
		{
			$errMessage = pg_last_error();
			error_log($errMessage);
			return $errMessage;
		}
	}

Since we already have quite three snippets of code on this page let's not overload it with another sample on how we should change calls to mysql_insert_id but don't forget to make the changes that you will find in the download.

 

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