Jump to content

How do a SQL Query with Prestashop Db.php


Igor G. Vianna

Recommended Posts

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

  • Like 1
Link to comment
Share on other sites

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.

  • Like 5
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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'];
   }

}

Link to comment
Share on other sites

  • 2 years later...
  • 2 years later...
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?
Link to comment
Share on other sites

  • 5 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...