PHP Language - insert_id & Stored Procedures

Author

insert_id & Stored Procedures

McHenry

2006-05-02, 6:58 pm

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 ?

The table uses an AUTO_INCREMENT constraint for the PK column.

I have a sp that is called from PHP to insert a row into a table, after the
procedure has been executed I can obtain db errors, if any, and the number
of rows affected successfully however the insert_id is always 0, even when a
rows has been inserted ?

$leadid=$mysqli->insert_id; //doesn't work
$error = $mysqli->error; //works !
$affected_rows=$stmt->affected_rows; //works !

Thanks in advance...

robert

2006-05-02, 6:58 pm

| 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_nam
e($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.
;^)

McHenry

2006-05-02, 6:58 pm

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_nam
e($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 !

robert

2006-05-02, 6:58 pm

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_nam
e($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 !
|
|

McHenry

2006-05-02, 6:58 pm

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_nam
e($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 !
> |
> |
>
>

robert

2006-05-02, 9:57 pm

"McHenry" <mchenry@mchenry.com> wrote in message
news:4457fad9$0$16970$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
| 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...

kk

can you post the proc you're creating/using? also, post the relevant table
structures so we can prototype them on our boxes.

what version of mysql are you using? would you also post a snippet of the
code that calls the proc and handles getting the new id?

thx...we'll get it working. is this still related to parsing and storing
results from urls? if so, i may just go ahead and work up the whole thing if
you can provide some specs and urls for testing.

McHenry

2006-05-02, 9:57 pm

"robert" <ab@no.spam-alama-ding-dong> wrote in message
news:iZT5g.60$tD3.33@fe03.lga...
>
> "McHenry" <mchenry@mchenry.com> wrote in message
> news:4457fad9$0$16970$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
> | 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...
>
> kk
>
> can you post the proc you're creating/using? also, post the relevant table
> structures so we can prototype them on our boxes.
>
> what version of mysql are you using? would you also post a snippet of the
> code that calls the proc and handles getting the new id?
>
> thx...we'll get it working. is this still related to parsing and storing
> results from urls? if so, i may just go ahead and work up the whole thing
> if
> you can provide some specs and urls for testing.
>
>

actually Robert, this one is totally off the famous Excel topic :)

I have posted the code in a post above but I didn't mention I am using PHP 5
and MySQL 5 on WindowsXP

Thanks

robert

2006-05-02, 9:57 pm

| actually Robert, this one is totally off the famous Excel topic :)

i'm chuckling. ;^)

| I have posted the code in a post above but I didn't mention I am using PHP
5
| and MySQL 5 on WindowsXP

i assumed as much given your interface is mysqli and that you can create
stored procs...i'm using a much older version of mysql. i too am using
windows xp as a prototyping platform. i don't think the os will be too much
of a factor w/ the current issue...but who knows.

Sponsored Links