|
|
Posted by robert on 09/28/36 11:46
| The documentation on PHP & stored procedures is limited so can anyone
| confirm that the id value of the last inserted row is available via the
| "insert_id" function ?
this seems to describe it pretty well:
============ from php manual
The mysqli_insert_id() function returns the ID generated by a query on a
table with a column having the AUTO_INCREMENT attribute. If the last query
wasn't an INSERT or UPDATE statement or if the modified table does not have
a column with the AUTO_INCREMENT attribute, this function will return zero.
Note: Performing an INSERT or UPDATE statement using the LAST_INSERT_ID()
function will also modify the value returned by the mysqli_insert_id()
function.
Return Values
The value of the AUTO_INCREMENT field that was updated by the previous
query. Returns zero if there was no previous query on the connection or if
the query did not update an AUTO_INCREMENT value.
Note: If the number is greater than maximal int value, mysqli_insert_id()
will return a string.
============
now i know your great aversion to suggestion, however it is evident that
such could be helpful to you at this point in your learnin. as you've
noticed, i've cleared out my plonk list...hence this reply. if you behave
and don't take offense, here's a suggestion...
write your code generically such that you aren't strongly tied to any single
technology. many use peardb classes to abstract the code from the db. that
way, the db interface in php uses the same calls/properties/methods no
matter if the db is oracle, terdata, sql server, or mysql. i'm not a huge
fan of peardb but i do take the same approach to abstraction. i have a
simplified db class that works just fine. this is the functionality of its
execute() method:
static function execute($sql, $decode = false, $returnNewId = false)
{
self::$_lastStatement = $sql;
$array = array();
$key = 0;
$records = mysql_query($sql);
$fieldCount = @mysql_num_fields($records);
$translation = get_html_translation_table(HTML_ENTITIES);
$translation = array_flip($translation);
while ($row = @mysql_fetch_array($records, MYSQL_NUM))
{
for ($i = 0; $i < $fieldCount; $i++)
{
$value = $row[$i];
if ($decode){ $value = strtr($value, $translation); }
$array[$key][strtoupper(@mysql_field_name($records, $i))] = $value;
}
$key++;
}
if ($returnNewId)
{
$array = array();
$array[0]['ID'] = mysql_insert_id();
}
@mysql_free_result($records);
return $array;
}
called from code, an example would look like:
$sql = "insert into foo values('bar')";
$records = db::execute($sql, false, true);
echo '<pre>new id = ' . $records[0]['ID'] . '</pre>';
that way, when you upgrade/change databases, you only have to alter the db
class' connect and execute methods...the rest of the scripts simply call the
same interface they always have. now, you can easily take this one step
further in oo design and create an actual db interface and have many of the
db-specific classes implement that interface...then your db upgrade/change
would only need to:
$db = mysql::getInstance();
or
$db = sqlserver::getInstance();
anyway...you will most likely have ignored much of what i posted beyond the
initial php manual description of mysqli->insert_id and how it works. that's
fine...just be civil so i don't have to start re-populating my plonk list.
;^)
Navigation:
[Reply to this message]
|