MySQLI stored procedure return PK/AI insertID
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.
Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.
In my model, I am calling a stored procedure like so:
$data = array("customerID" => 123, "productID" => 456);
...
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?)";
$result = $this->db->query($procedure, $data);
}
As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.
Stored Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
SELECT
in_customerID,
in_productID,
p.retail,
p.faceValue
FROM
products as p
WHERE
p.productID = in_productID;
END
While this works fine, I am not able to get the insert_id from the PK/AI.
The only way this data is passed back (it seems) is when you use :
$this->db->insert('customers', $data);
return $this->db->insert_id();
I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.
How can I return the insert_id to my application when using a stored procedure setup like this?
mysql stored-procedures mysqli
asked Jan 19 '18 at 4:11
SBBSBB
12112
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.
Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.
In my model, I am calling a stored procedure like so:
$data = array("customerID" => 123, "productID" => 456);
...
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?)";
$result = $this->db->query($procedure, $data);
}
As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.
Stored Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
SELECT
in_customerID,
in_productID,
p.retail,
p.faceValue
FROM
products as p
WHERE
p.productID = in_productID;
END
While this works fine, I am not able to get the insert_id from the PK/AI.
The only way this data is passed back (it seems) is when you use :
$this->db->insert('customers', $data);
return $this->db->insert_id();
I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.
How can I return the insert_id to my application when using a stored procedure setup like this?
mysql stored-procedures mysqli
asked Jan 19 '18 at 4:11
SBBSBB
12112
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.
Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.
In my model, I am calling a stored procedure like so:
$data = array("customerID" => 123, "productID" => 456);
...
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?)";
$result = $this->db->query($procedure, $data);
}
As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.
Stored Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
SELECT
in_customerID,
in_productID,
p.retail,
p.faceValue
FROM
products as p
WHERE
p.productID = in_productID;
END
While this works fine, I am not able to get the insert_id from the PK/AI.
The only way this data is passed back (it seems) is when you use :
$this->db->insert('customers', $data);
return $this->db->insert_id();
I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.
How can I return the insert_id to my application when using a stored procedure setup like this?
mysql stored-procedures mysqli
asked Jan 19 '18 at 4:11
SBBSBB
12112
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.
Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.
In my model, I am calling a stored procedure like so:
$data = array("customerID" => 123, "productID" => 456);
...
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?)";
$result = $this->db->query($procedure, $data);
}
As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.
Stored Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
SELECT
in_customerID,
in_productID,
p.retail,
p.faceValue
FROM
products as p
WHERE
p.productID = in_productID;
END
While this works fine, I am not able to get the insert_id from the PK/AI.
The only way this data is passed back (it seems) is when you use :
$this->db->insert('customers', $data);
return $this->db->insert_id();
I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.
How can I return the insert_id to my application when using a stored procedure setup like this?
mysql stored-procedures mysqli
mysql stored-procedures mysqli
asked Jan 19 '18 at 4:11
SBBSBB
12112
asked Jan 19 '18 at 4:11
SBBSBB
12112
asked Jan 19 '18 at 4:11
SBBSBB
12112
asked Jan 19 '18 at 4:11
SBBSBB
12112
asked Jan 19 '18 at 4:11
SBBSBB
12112
12112
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Add OUT the_id to the argument list.
Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;
Then decide how you can retrieve the id from whatever variable you pass into the proc.
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f195749%2fmysqli-stored-procedure-return-pk-ai-insertid%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Add OUT the_id to the argument list.
Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;
Then decide how you can retrieve the id from whatever variable you pass into the proc.
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
add a comment |
Add OUT the_id to the argument list.
Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;
Then decide how you can retrieve the id from whatever variable you pass into the proc.
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
add a comment |
Add OUT the_id to the argument list.
Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;
Then decide how you can retrieve the id from whatever variable you pass into the proc.
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
Add OUT the_id to the argument list.
Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;
Then decide how you can retrieve the id from whatever variable you pass into the proc.
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
answered Jan 20 '18 at 0:59
Rick JamesRick James
42.6k22258
42.6k22258
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f195749%2fmysqli-stored-procedure-return-pk-ai-insertid%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
