PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
hc
Forum Newbie
Posts: 13 Joined: Wed Oct 25, 2006 2:00 pm
Post
by hc » Mon Oct 30, 2006 9:15 am
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;
}
}
hc
Forum Newbie
Posts: 13 Joined: Wed Oct 25, 2006 2:00 pm
Post
by hc » Mon Oct 30, 2006 9:38 am
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
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Oct 30, 2006 9:42 am
Did you read the whole description?
e.g.
hc
Forum Newbie
Posts: 13 Joined: Wed Oct 25, 2006 2:00 pm
Post
by hc » Mon Oct 30, 2006 10:06 am
volka wrote: Did you read the whole description?
e.g.
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>';
}
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Oct 30, 2006 10:13 am
$db is in instance of what class?
hc
Forum Newbie
Posts: 13 Joined: Wed Oct 25, 2006 2:00 pm
Post
by hc » Mon Oct 30, 2006 10:23 am
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()
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Oct 30, 2006 4:14 pm
$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.
CoderGoblin
DevNet Resident
Posts: 1425 Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany
Post
by CoderGoblin » Tue Oct 31, 2006 3:18 am
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).