Page 1 of 1

get last inserted id, PostGreSQL

Posted: Mon Oct 30, 2006 9:15 am
by hc
In the section of code below, how can I get the last inserted id of the users.employees table. Thje DB is PostGreSQL. I saw the functions for MySQL, but is there anything similar that willl get the last inserted id within the current transaction for PGSQL? Thanks alot folks

if(!$found)
{
$db->query($sqlstrInsertUser);
$sUserAdded .= 'User Added' . '-' . $fname .' ' . $lname . '-' . $tax_id . '</br>';
}

Code: Select all

switch($action)
{
	case "officials":
	{
		echo 'Here we will do lookup by both tax_id and email. If either exists, we will skip the record, if not we will insert';
		echo '</br></br>';
		$user = new Employee();

		foreach ($_POST['chk'] as $name => $val)
		{
			$found = false;
			$title = $_POST['title_' . $val];
			$fname = $_POST['fname_' . $val];
			$mname = $_POST['mname_' . $val];
			$lname = $_POST['lname_' . $val];
			$tax_id = $_POST['taxid_' . $val];
			$username = $_POST['username_' . $val];
			$password = $_POST['password_' . $val];
			$suffix = $_POST['suffix_' . $val];
			$imported_by = $_POST['inserted_' . $val];


			if(isset($tax_id))
			{
				$sqlstrGetByTaxID = <<< SQL
					SELECT
						id
					FROM
						users.employees
					WHERE
						tax_id = '$tax_id'
SQL;

				$sqlstrGetByUsername = <<< SQL
					SELECT
						id
					FROM
						users.employees
					WHERE
						username = '$username'
SQL;

			$sqlstrInsertUser = <<< SQL
				INSERT INTO
					users.employees (username, pass, title, fname, mname, lname, tax_id, suffix, imported_by)
				VALUES ('$username', '$password', '$title', '$fname', '$mname', '$lname', '$tax_id', '$suffix', '$imported_by');
SQL;

				if($db->query($sqlstrGetByTaxID))
				{
					while($row = $db->fetch())
					{
						$found = true;
						$sTaxIDExists .= 'User Tax ID Found' . '-' . $fname .' ' . $lname . '-' . $tax_id . '</br>';
					}

					if($db->query($sqlstrGetByUsername))
					{
						while($row = $db->fetch())
						{
							if(!$found)
							{
								$found = true;
								$sUsernameExists .= 'Username Exists' . '-' . $username . '</br>';
							}
						}
					}
				}
				if(!$found)
				{
					$db->query($sqlstrInsertUser);
					$sUserAdded .= 'User Added' . '-' . $fname .' ' . $lname . '-' . $tax_id . '</br>';
				}
			}

		}
		echo $sUserAdded;
		echo $sTaxIDExists;
		echo $sUsernameExists;
		break;
	}
}

Posted: Mon Oct 30, 2006 9:23 am
by volka
take a look at the description of http://de2.php.net/pg_last_oid

but what if the table doesn't have oid's...

Posted: Mon Oct 30, 2006 9:38 am
by hc
but what if the table doesn't have oid's...

I only have a PK 'id serial NOT NULL'

So, what in that case?? Thanks for the help

Posted: Mon Oct 30, 2006 9:42 am
by volka
Did you read the whole description?
e.g.
http://de2.php.net/pg_last_oid wrote:To get the value of a SERIAL field in an inserted row, it is necessary to use [...]

Posted: Mon Oct 30, 2006 10:06 am
by hc
volka wrote:Did you read the whole description?
e.g.
http://de2.php.net/pg_last_oid wrote:To get the value of a SERIAL field in an inserted row, it is necessary to use [...]
Ok, thank you. I have this now, but it does not work correctly. How exactly should I be implementing this function?

Code: Select all

			$sqlstrInsertUser = <<< SQL
				INSERT INTO
					users.employees (username, pass, title, fname, mname, lname, tax_id, suffix, imported_by)
				VALUES ('$username', '$password', '$title', '$fname', '$mname', '$lname', '$tax_id', '$suffix', '$imported_by');

				$id = SELECT currval('users.employees_id_seq');
SQL;

				if($db->query($sqlstrGetByTaxID))
				{
					while($row = $db->fetch())
					{
						$found = true;
						$sTaxIDExists .= 'User Tax ID Found' . '-' . $fname .' ' . $lname . '-' . $tax_id . '</br>';
					}

					if($db->query($sqlstrGetByUsername))
					{
						while($row = $db->fetch())
						{
							if(!$found)
							{
								$found = true;
								$sUsernameExists .= 'Username Exists' . '-' . $username . '</br>';
							}
						}
					}
				}
				if(!$found)
				{
					$db->query($sqlstrInsertUser);
					$sUserAdded .= 'User Added-New ID' . $id . '-' . $fname .' ' . $lname . '-' . $tax_id . '</br>';
				}

Posted: Mon Oct 30, 2006 10:13 am
by volka
$db is in instance of what class?

Posted: Mon Oct 30, 2006 10:23 am
by hc
volka wrote:$db is in instance of what class?
I have a DB class with relavant functions for inserting, selecting, etc...

Here are two functions in it. One is query, one is fetch. I am just unsure WHERE to call the fetch after calling the query....

Thanks

Code: Select all

	/********************************************************
		void query($sqlstr);

		Function Description
			Executes the $sqlstr
	********************************************************/
	function query($sqlstr)
	{
		$this->qnum++;
		ob_start();
		if($this->db_conn)
		{
			switch($this->db_type)
			{
				case DB_MYSQL:
					//	MYSQL DB
					$this->db_result = mysql_query($sqlstr, $this->db_conn);
					if(!$this->db_result)
					{
						$this->errors[] = "Could not execute the query <hr><pre>$sqlstr<pre><hr>" . mysql_error($this->db_conn);
					}
					else
					{
						$this->num_rows = mysql_num_rows($this->db_result);
					}
					break;
				case DB_PG:
					//	PG DB
					$this->db_result = pg_query($this->db_conn, $sqlstr);
					if(!$this->db_result)
					{
						$this->errors[] = "Could not execute the query <hr><pre>$sqlstr<pre><hr>" . pg_last_error($this->db_conn);
					}
					else
					{
						$this->num_rows = pg_num_rows($this->db_result);
					}
					break;
				case DB_MS:
					//	ACCESS DB
					$this->db_result = odbc_exec($this->db_conn, "$sqlstr");
					if(!$this->db_result)
					{
						$this->errors[] = "Could not execute the query <hr><pre>$sqlstr<pre><hr>" . odbc_error($this->db_conn);
					}
					else
					{
						$this->num_rows = odbc_num_rows($this->db_result);
					}
					break;
				default:
					$this->errors[] = "Incorrect db type.";
					break;
			}
		}
		else
		{
			$this->errors[] = "Not connected to a database. Can not execute query.";
		}

		ob_end_clean();

		$error = $this->CheckError();

		if($error)
		{
			$retval = false;
		}
		else
		{
			$retval = true;
		}

		return $retval;
	} // query()


	/********************************************************
		void fetch($returntype);

		Function Description
			Pulls rsults from the query and returns them.
	********************************************************/
	function fetch($returntype = DB_ASSOC)
	{
		$retval = false;
		if($this->db_result)
		{
			switch($returntype)
			{
				case DB_ROW:
					switch($this->db_type)
					{
						case DB_MYSQL:
							$retval = mysql_fetch_row($this->db_result);
							break;
						case DB_PG:
							$retval = pg_fetch_row($this->db_result);
							break;
						case DB_MS:
							$retval = odbc_fetch_row($this->db_result);
							break;
						default:
							$this->errors[] = "Incorrect db type.";
							break;
					}
					break;
				case DB_ARRAY:
					switch($this->db_type)
					{
						case DB_MYSQL:
							$retval = mysql_fetch_array($this->db_result);
							break;
						case DB_PG:
							$retval = pg_fetch_array($this->db_result);
							break;
						case DB_MS:
							$retval = odbc_fetch_array($this->db_result);
							break;
						default:
							$this->errors[] = "Incorrect db type.";
							break;
					}
					break;
				case DB_ASSOC:
					switch($this->db_type)
					{
						case DB_MYSQL:
							$retval = mysql_fetch_assoc($this->db_result);
							break;
						case DB_PG:
							$retval = pg_fetch_assoc($this->db_result);
							break;
						case DB_MS:
							$retval = odbc_fetch_array($this->db_result);
							//$retval = ($this->db_result);
							break;
						default:
							$this->errors[] = "Incorrect db type.";
							break;
					}
					break;
				default:
					$this->errors[] = "Incorrect return type.";
					break;
			}
		}
		else
		{
			$this->errors[] = "There are no results available.";
		}

		return $retval;
	} // fetch()

Posted: Mon Oct 30, 2006 4:14 pm
by volka
$sqlstrInsertUser = <<< SQL
INSERT INTO
users.employees (username, pass, title, fname, mname, lname, tax_id, suffix, imported_by)
VALUES ('$username', '$password', '$title', '$fname', '$mname', '$lname', '$tax_id', '$suffix', '$imported_by');

$id = SELECT currval('users.employees_id_seq');
SQL;
That's one single string.
You want to send the SELECT currval('users.employees_id_seq') query and fetch its result after the insert statement.

Posted: Tue Oct 31, 2006 3:18 am
by CoderGoblin
OK doesn't work for a generic get any database class but...

Normally when I need to insert I use 2 queries. A select to get the ID storing the resulting value as PHP variable.

Code: Select all

$sql="SELECT nextval('id_seq'::text) as id";
the other to perform the insert...

Code: Select all

$sql="INSERT INTO tablename (id,value) VALUES ({$id},'{$value}')";
Disadvantage of this method is it ties you more closely to postgres but that is not a problem where I work.

Don't forget to use pg_escape_string for values where necessary.

If you need a catch all database class I would have to say why create a new one when there are several in existance. ADODB and even the Zend Framework have them (not sure how complete the Zend Framework is though).