Obtain (and not display however) the number of products with a "particular reference"

[iammabbella]

Hello,

 

I'm super new to Prestashop, I need to obtain in the page "order-address.tpl" the total number of products (I'm in the checkout procedure, so only the products the client "is buying") with a certain field reference, for instance "accessory-nano".

I've already the total number of products, but I need only this value, just a numeric value.

Is it possible?

For now I have $productNumber (the products total).

[NemoPS]

I assume you only need to check that with products inside the cart. Haven't tested the following, but it should work:

 

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT COUNT(DISTINCT p.id_product)
FROM '._DB_PREFIX_.'cart_product cp
LEFT JOIN '._DB_PREFIX_.'product p
WHERE cp.id_cart = '.$this->context->cart->id.'
AND p.reference = '.$reference_variable);

 

Of course, must be placed in your php script

[iammabbella]

I assume you only need to check that with products inside the cart. Haven't tested the following, but it should work:

 

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
SELECT COUNT(DISTINCT p.id_product)
FROM '._DB_PREFIX_.'cart_product cp
LEFT JOIN '._DB_PREFIX_.'product p
WHERE cp.id_cart = '.$this->context->cart->id.'
AND p.reference = '.$reference_variable);

 

Of course, must be placed in your php script

 

"I assume you only need to check that with products inside the cart":

Yes u are right..!

 

That's a good solution, but where could I put this code, in which page I mean... not in .tpl file, or am I wrong?

I love code in pure php, directly, normally if I had a problem like this in php, I would have resolved that in little time, but with Smarty and the "Presta code architecture" I'm stuck with no time for learn

[NemoPS]

Uh, I thought you already had your php file running. You can create a module and hook it to that page (if a hook doesn't exist, create a new one, place it in the order-addresses.tpl file as {hook h='hookname'})

 

Or you can edit OrderAddressesController.php and add it somewhere (likely, in initContent) and then assign your products to smarty.

[iammabbella]

Uh, I thought you already had your php file running. You can create a module and hook it to that page (if a hook doesn't exist, create a new one, place it in the order-addresses.tpl file as {hook h='hookname'})

 

Or you can edit OrderAddressesController.php and add it somewhere (likely, in initContent) and then assign your products to smarty.

 

The latest one maybe, today I'm gonna test this solution, then I'll tell you what's the result.

Thanks a lot for now...

[iammabbella]

The latest one maybe, today I'm gonna test this solution, then I'll tell you what's the result.

Thanks a lot for now...

 

I get an error...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cp.id_cart = 84 AND p.reference = accessorio' at line 4 SELECT COUNT(DISTINCT p.id_product) FROM ps_cart_product cp LEFT JOIN ps_product p WHERE cp.id_cart = 84 AND p.reference = accessorio

at line 605 in file classes/db/Db.php

[NemoPS]

your reference must be put inside quotes, like this:

 

WHERE cp.id_cart = 84 AND p.reference = accessorio' at line 4 SELECT COUNT(DISTINCT p.id_product) FROM ps_cart_product cp LEFT JOIN ps_product p WHERE cp.id_cart = 84 AND p.reference = "accessorio"

 

Of course, this varies depending on the quotes you use for the query at the beginning, you can also enclose the variable directly if they're double quotes

[iammabbella]

your reference must be put inside quotes, like this:

 

WHERE cp.id_cart = 84 AND p.reference = accessorio' at line 4 SELECT COUNT(DISTINCT p.id_product) FROM ps_cart_product cp LEFT JOIN ps_product p WHERE cp.id_cart = 84 AND p.reference = "accessorio"

 

Of course, this varies depending on the quotes you use for the query at the beginning, you can also enclose the variable directly if they're double quotes

 

I used exactly your code, with this line AND p.reference = '.$reference_variable);

Is the debug of Prestashop that write the variable, but in code is like above.

[NemoPS]

..yes of course, but I forgot the quotes here, thinking you used a number:

 

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

SELECT COUNT(DISTINCT p.id_product)

FROM '._DB_PREFIX_.'cart_product cp

LEFT JOIN '._DB_PREFIX_.'product p

WHERE cp.id_cart = '.$this->context->cart->id.'

AND p.reference = "'.$reference_variable.'"');

[iammabbella]

..yes of course, but I forgot the quotes here, thinking you used a number:

 

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

SELECT COUNT(DISTINCT p.id_product)

FROM '._DB_PREFIX_.'cart_product cp

LEFT JOIN '._DB_PREFIX_.'product p

WHERE cp.id_cart = '.$this->context->cart->id.'

AND p.reference = "'.$reference_variable.'"');

 

Ok, thanks, I'll try thanks for patience...

[iammabbella]

Ok, thanks, I'll try thanks for patience...

 

There still is the error, I really don't know what's going on, never happen with my own classe that I create for db access.

What's wrong?

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cp.id_cart = 84 AND p.reference = "accessorio"' at line 4 SELECT COUNT(DISTINCT p.id_product) FROM ps_cart_product cp LEFT JOIN ps_product p WHERE cp.id_cart = 84 AND p.reference = "accessorio"

at line 605 in file classes/db/Db.php

Edited March 15, 2013 by iammabbella (see edit history)

[iammabbella]

There still is the error, I really don't know what's going on, never happen with my own classe that I create for db access.

What's wrong?

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cp.id_cart = 84 AND p.reference = "accessorio"' at line 4 SELECT COUNT(DISTINCT p.id_product) FROM ps_cart_product cp LEFT JOIN ps_product p WHERE cp.id_cart = 84 AND p.reference = "accessorio"

at line 605 in file classes/db/Db.php

 

Without the "LEFT" works.

[iammabbella]

Without the "LEFT" works, however it's strange that return always the value 1, even though there are more products with the keyword "accessorio".

 

SELECT COUNT(DISTINCT p.id_product)

FROM ps_cart_product cp

JOIN ps_product p

WHERE cp.id_cart = 84 AND p.reference = "accessorio"

 

Return 2 in phpmyadmin, but in Presta return 1...

 

Ps.

Scusa se te lo domando, ma sei italiano??

Edited March 16, 2013 by iammabbella (see edit history)

[NemoPS]

What do you get with LEFT? Only that error?

 

ps:yep

Edited March 18, 2013 by Nemo1 (see edit history)

[iammabbella]

What do you get with LEFT? Only that error?

 

ps:yep

 

I have resolved with this query:

 

 

$this->result_ticket = Db::getInstance()->getValue('

SELECT SUM(cp.quantity)

FROM '._DB_PREFIX_.'cart_product cp

INNER JOIN '._DB_PREFIX_.'product p

ON cp.id_product = p.id_product

WHERE p.reference = "'.$reference_variable.'"

AND cp.id_cart = '.$this->context->cart->id.'

');

 

Works great!

However this pull out the number of tickets in the cart (with sum), who have the field reference value equal to "accessorio", and throws the result in order-address.tpl.

Edited March 18, 2013 by iammabbella (see edit history)