How do a SQL Query with Prestashop Db.php

[Igor G. Vianna]

hello,
I'm developing a store with prestashop and I need to create a module.
I managed to make it work except for one detail, I can not make it read data from the database.
I can use the "Db:: getInstance () -> numRows () " to count the number of records and this works, but I can not make it download the data from the user - name and birthday - to list in a While.
I've tried almost all possible functions in the db.php, getValue (), execute (), ExecuteS (), ... but all in vain.

Can anyone help me? I just need to do a simple SELECT on the database and print the results in a While.

Db::getInstance()->execute('SELECT `firstname` FROM `'._DB_PREFIX_.'customer`  WHERE birthday = `1989-02-29`');

Thank you in advance!


PS: Sorry my bad english I used a translator =D

[Cédric - Groupe SITTI]

hello,

Db::getInstance()->Execute('... SQL QUERY ...') => will return result of mysql_query() or false(error).

Db::getInstance()->ExecuteS('... SQL QUERY ...') => will return array of mysql_fetch_assoc for each result entries or false(error)

Db::getInstance()->getValue('... SQL QUERY WITHOUT LIMIT !!!...') => will return the first column of first result entry.

I hope it will help you.

[Igor G. Vianna]

Thanks for your reply.
But i cant do this work

Look. I translated the names of variables to aid in understanding.
I want to make a system list of birthdays of the day.
The "Query" is correct, as I do in MySQL and it works perfectly. In the PrestaShop not.

    public function hookBackOfficeHome($params)
   {
       $query = 'SELECT * FROM `'._DB_PREFIX_.'customer` WHERE `birthday`=("1989-02-28")';
       $qtybirthdays = Db::getInstance()->NumRows($query);
       if (!$qtybirthdays){
           $this->_html = "No birthdays today";
       } elseif ($qtybirthdays =='1') {
           print "Only ".$qtdaniversariantes." birthday today.";
           $this->lista_aniversariantes($query);
       } elseif ($qtybirthdays >= '2'){
           print $qtybirthdays." people have birthday today.";
           $this->lista_aniversariantes($query);            
       }

       return $this->_html;
   }

   public function lista_aniversariantes($query){
       $birthdays = Db::getInstance()->Execute($query);
      while($birthdays) {
       print $birthdays['firstname'];
       }

   }

Thx!

[Cédric - Groupe SITTI]

if your SQL query has no error try this :

public function lista_aniversariantes($query){
   $birthdays = Db::getInstance()->ExecuteS($query);

   foreach ($birthdays as $birthday) { 
       print $birthday['firstname'];
   }
}

[Cédric - Groupe SITTI]

Or try this one,
because Db::getInstance()->NumRows() require query execution before.

public function hookBackOfficeHome($params)
{
   $query = 'SELECT * FROM `'._DB_PREFIX_.'customer` WHERE `birthday`=("1989-02-28")';
   $birthdays = Db::getInstance()->ExecuteS($query);
   $qtybirthdays = count($birthdays);

   if (!$qtybirthdays){
       $this->_html = "No birthdays today";
   } elseif ($qtybirthdays =='1') {
       print "Only ".$qtdaniversariantes." birthday today.";
       $this->lista_aniversariantes($birthdays);
   } elseif ($qtybirthdays >= '2'){
       print $qtybirthdays." people have birthday today.";
       $this->lista_aniversariantes($birthdays);            
   }

   return $this->_html;
}

public function lista_aniversariantes($birthdays)
{
   foreach($birthdays as $birthday) {
       print $birthday['firstname'];
   }

}

[Igor G. Vianna]

thanks a lot for your fast reply!
now its working!!!

[thewordsmith]

Do you by chance know which table contains the merchant shipping postal code.

[vekia]

it's ps_address table, fieldname: postalcode

[thewordsmith]

For the sake of creating a shipping module for the USPS the postal code is stored in ps_configuration where name="PS_SHOP_CODE"

[Guest locen]

Hi, I made this query:

 SELECT o.reference,  pl.name as product_name, pc.name AS city, od.product_quantity, cs.firstname, cs.lastname, DATE(o.date_add) AS data_acq, ROUND (od.total_price_tax_incl, 3) AS price_with_tax,  cu.name AS curr, o.total_discounts_tax_excl, pt.rate AS tax_value_percent, od.product_reference

FROM ps_product p

LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product

LEFT JOIN ps_order_detail od ON p.id_product = od.product_id

LEFT JOIN ps_orders o ON o.id_order = od.id_order

LEFT JOIN ps_currency cu ON cu.id_currency = o.id_currency

JOIN ps_order_detail_tax dt ON od.id_order_detail = dt.id_order_detail

JOIN ps_tax pt ON pt.id_tax = dt.id_tax

JOIN ps_customer cs on o.id_customer=cs.id_customer

JOIN ps_address ad on o.id_address_delivery=ad.id_address

JOIN ps_country_lang pc on ad.id_country=pc.id_country

 JOIN ps_country_lang ps on pl.id_lang=pc.id_lang

WHERE  o.current_state = 2 OR o.current_state =  3 OR o.current_state = 4 OR o.current_state = 5 OR o.current_state = 9 OR o.current_state = 12 OR o.current_state = 13 OR o.current_state = 16 

 

 

GROUP BY od.id_order_detail

ORDER BY o.date_add;

 

 

But I have problem with prices. when I export file i have price number with a lot of decimals.

can someone help me?

[GoPure Kratom]

Very old thread, but if you really made a query that complicated and didn't know about ROUND function, I am a bit amazed.
Bascically ROUND(12.1234567890,2) will output 12.12 and ROUND(12.4567890,2) will output 12.46