|
|
Posted by robert on 09/28/80 11:46
np...glad you got it working.
;^)
"McHenry" <mchenry@mchenry.com> wrote in message
news:44577ba0$0$16981$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
| Thanks you for your help, greatly appreciated.
|
|
| "robert" <ab@no.spam-alama-ding-dong> wrote in message
| news:UGK5g.15$1h4.3@fe06.lga...
| >| 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.
| > ;^)
| >
| >
|
| I have actually worked around the problem by having the new ID returned
form
| the procedure as a parameter using:
|
| SET _leadid=LAST_INSERT_ID();
|
| Once again... thanks !
|
|
Navigation:
[Reply to this message]
|