Mixing
How can I change this stored procedure mysql code into pure mysql code
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I want to change this stored procedure code to pure mysql code and I did this but it complains DECLARE productQuantity INT and says the variable is not set or exist
I have tried changing the code to my own understanding by deleting the stored procedure code (function) from the pure code
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
IF productQuantity IS NULL THEN
INSERT INTO shopping_cart(cart_id, product_id, attributes,
quantity, added_on)
VALUES (inCartId, inProductId, inAttributes, 1, NOW());
ELSE
UPDATE shopping_cart
SET quantity = quantity + 1, buy_now = true
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes;
END IF;
END$$
and this is the code I change it to
public function addProductToCart($cart_id, $product_id, $attributes){
$addProduct = 'DECLARE productQuantity INT ';
$addProduct .= 'SELECT * FROM `shopping_cart` WHERE cart_id =
' . $cart_id . ' AND product_id = ' . $product_id;
$addProduct .= ' AND attributes = ' . $attributes . ' INTO
productQuantity ';
$addProduct .= 'IF productQuantity IS NULL THEN INSERT INTO
`shopping_cart`(';
$addProduct .= 'cart_id, product_id, attributes, quantity, added_on) VALUES (';
$addProduct .= $cart_id . ', ' . $product_id . ', ' . $attributes . ', 1 , 1 , NOW()';
$addProduct .= ' ELSE UPDATE `shopping_cart` SET quantity = quantity + 1, buy_now = true WHERE cart_id = ' . $cart_id;
$addProduct .= ' AND product_id = ' . $product_id . ' AND attributes = ' . $attributes;
$parameters = ['cart_id' => $cart_id, 'product_id' => $product_id, 'attributes' => $attributes];
$query = $this->query($addProduct, $parameters);
return $query;
}
private function query($sql, $parameters = ) {
$query = $this->pdo->prepare($sql);
$query->execute($parameters);
return $query;
}
This code is written in PHP7
I expect it to insert or update the affected rows in the database table
php mysql sql
edited Jan 3 at 8:19
Nick
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
add a comment |
0
I want to change this stored procedure code to pure mysql code and I did this but it complains DECLARE productQuantity INT and says the variable is not set or exist
I have tried changing the code to my own understanding by deleting the stored procedure code (function) from the pure code
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
IF productQuantity IS NULL THEN
INSERT INTO shopping_cart(cart_id, product_id, attributes,
quantity, added_on)
VALUES (inCartId, inProductId, inAttributes, 1, NOW());
ELSE
UPDATE shopping_cart
SET quantity = quantity + 1, buy_now = true
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes;
END IF;
END$$
and this is the code I change it to
public function addProductToCart($cart_id, $product_id, $attributes){
$addProduct = 'DECLARE productQuantity INT ';
$addProduct .= 'SELECT * FROM `shopping_cart` WHERE cart_id =
' . $cart_id . ' AND product_id = ' . $product_id;
$addProduct .= ' AND attributes = ' . $attributes . ' INTO
productQuantity ';
$addProduct .= 'IF productQuantity IS NULL THEN INSERT INTO
`shopping_cart`(';
$addProduct .= 'cart_id, product_id, attributes, quantity, added_on) VALUES (';
$addProduct .= $cart_id . ', ' . $product_id . ', ' . $attributes . ', 1 , 1 , NOW()';
$addProduct .= ' ELSE UPDATE `shopping_cart` SET quantity = quantity + 1, buy_now = true WHERE cart_id = ' . $cart_id;
$addProduct .= ' AND product_id = ' . $product_id . ' AND attributes = ' . $attributes;
$parameters = ['cart_id' => $cart_id, 'product_id' => $product_id, 'attributes' => $attributes];
$query = $this->query($addProduct, $parameters);
return $query;
}
private function query($sql, $parameters = ) {
$query = $this->pdo->prepare($sql);
$query->execute($parameters);
return $query;
}
This code is written in PHP7
I expect it to insert or update the affected rows in the database table
php mysql sql
edited Jan 3 at 8:19
Nick
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
2
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an;.
– jeroen
Jan 3 at 8:24
add a comment |
0
0
0
I want to change this stored procedure code to pure mysql code and I did this but it complains DECLARE productQuantity INT and says the variable is not set or exist
I have tried changing the code to my own understanding by deleting the stored procedure code (function) from the pure code
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
IF productQuantity IS NULL THEN
INSERT INTO shopping_cart(cart_id, product_id, attributes,
quantity, added_on)
VALUES (inCartId, inProductId, inAttributes, 1, NOW());
ELSE
UPDATE shopping_cart
SET quantity = quantity + 1, buy_now = true
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes;
END IF;
END$$
and this is the code I change it to
public function addProductToCart($cart_id, $product_id, $attributes){
$addProduct = 'DECLARE productQuantity INT ';
$addProduct .= 'SELECT * FROM `shopping_cart` WHERE cart_id =
' . $cart_id . ' AND product_id = ' . $product_id;
$addProduct .= ' AND attributes = ' . $attributes . ' INTO
productQuantity ';
$addProduct .= 'IF productQuantity IS NULL THEN INSERT INTO
`shopping_cart`(';
$addProduct .= 'cart_id, product_id, attributes, quantity, added_on) VALUES (';
$addProduct .= $cart_id . ', ' . $product_id . ', ' . $attributes . ', 1 , 1 , NOW()';
$addProduct .= ' ELSE UPDATE `shopping_cart` SET quantity = quantity + 1, buy_now = true WHERE cart_id = ' . $cart_id;
$addProduct .= ' AND product_id = ' . $product_id . ' AND attributes = ' . $attributes;
$parameters = ['cart_id' => $cart_id, 'product_id' => $product_id, 'attributes' => $attributes];
$query = $this->query($addProduct, $parameters);
return $query;
}
private function query($sql, $parameters = ) {
$query = $this->pdo->prepare($sql);
$query->execute($parameters);
return $query;
}
This code is written in PHP7
I expect it to insert or update the affected rows in the database table
php mysql sql
edited Jan 3 at 8:19
Nick
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
I want to change this stored procedure code to pure mysql code and I did this but it complains DECLARE productQuantity INT and says the variable is not set or exist
I have tried changing the code to my own understanding by deleting the stored procedure code (function) from the pure code
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
IF productQuantity IS NULL THEN
INSERT INTO shopping_cart(cart_id, product_id, attributes,
quantity, added_on)
VALUES (inCartId, inProductId, inAttributes, 1, NOW());
ELSE
UPDATE shopping_cart
SET quantity = quantity + 1, buy_now = true
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes;
END IF;
END$$
and this is the code I change it to
public function addProductToCart($cart_id, $product_id, $attributes){
$addProduct = 'DECLARE productQuantity INT ';
$addProduct .= 'SELECT * FROM `shopping_cart` WHERE cart_id =
' . $cart_id . ' AND product_id = ' . $product_id;
$addProduct .= ' AND attributes = ' . $attributes . ' INTO
productQuantity ';
$addProduct .= 'IF productQuantity IS NULL THEN INSERT INTO
`shopping_cart`(';
$addProduct .= 'cart_id, product_id, attributes, quantity, added_on) VALUES (';
$addProduct .= $cart_id . ', ' . $product_id . ', ' . $attributes . ', 1 , 1 , NOW()';
$addProduct .= ' ELSE UPDATE `shopping_cart` SET quantity = quantity + 1, buy_now = true WHERE cart_id = ' . $cart_id;
$addProduct .= ' AND product_id = ' . $product_id . ' AND attributes = ' . $attributes;
$parameters = ['cart_id' => $cart_id, 'product_id' => $product_id, 'attributes' => $attributes];
$query = $this->query($addProduct, $parameters);
return $query;
}
private function query($sql, $parameters = ) {
$query = $this->pdo->prepare($sql);
$query->execute($parameters);
return $query;
}
This code is written in PHP7
I expect it to insert or update the affected rows in the database table
php mysql sql
php mysql sql
edited Jan 3 at 8:19
Nick
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
edited Jan 3 at 8:19
Nick
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
edited Jan 3 at 8:19
Nick
38.9k132443
edited Jan 3 at 8:19
Nick
38.9k132443
edited Jan 3 at 8:19
Nick
38.9k132443
38.9k132443
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
asked Jan 3 at 8:16
Gbenga OgunbuleGbenga Ogunbule
446
446
2
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an;.
– jeroen
Jan 3 at 8:24
add a comment |
2
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an;.
– jeroen
Jan 3 at 8:24
2
2
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an
;.– jeroen
Jan 3 at 8:24
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an
;.– jeroen
Jan 3 at 8:24
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f54018564%2fhow-can-i-change-this-stored-procedure-mysql-code-into-pure-mysql-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f54018564%2fhow-can-i-change-this-stored-procedure-mysql-code-into-pure-mysql-code%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
2
You completely miss the point of preparing a statement and binding the variables. You should start with the manual: php.net/manual/en/pdo.prepare.php. And you are running multiple sql statements in 1 query, which probably will not work, even when you separate the statements correctly with an
;.– jeroen
Jan 3 at 8:24