Jump to content

How do a SQL Query with Prestashop Db.php


Igor G. Vianna
 Share

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

Share this post


Link to post
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 4

Share this post


Link to post
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!

Share this post


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

}

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

  • 5 years later...

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

Share this post


Link to post
Share on other sites

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
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More