|
Posted by McHenry on 05/03/06 03:35
actually didn't !
as it turns out my proposed solution didn't work:
http://bugs.mysql.com/bug.php?id=11638
So now I am unable to get a value back from a stored procedure using
parameters and the insert_id does not work either !!
Back to the drawing board...
"robert" <ab@no.spam-alama-ding-dong> wrote in message
news:Y%L5g.30$1h4.13@fe06.lga...
> 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]
|