|
|
Posted by McHenry on 09/28/78 11:46
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]
|