get the value from database by using DB::getInstance()->Execute() instead of mysqli_query()

[prestashop_newuser]

Hi,

In prestashop I am doing a small module. In that I have some jQuery ajax. For database connection I am using mysqli_connect as mysql_connect has been deprecated.

So my entire script to fetch some data from database through ajax looks like this

 

<?php

include '../../../config/settings.inc.php';

include '../../../config/defines.inc.php';

include '../../../config/config.inc.php';



$connection = mysqli_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_) or die(mysqli_error());

mysqli_select_db($connection,_DB_NAME_) or die(mysql_error());

$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";

$result = mysqli_query($connection,$query) or die(mysqli_error());

while($row = mysqli_fetch_array($result)) { 

  $data=$row['state_name'];

echo '<option value="'.$data.'">'.$data.'</option>';

  }

?>

 

Here its working absolutely fine. But as Prestashop tells to use 

 

DB::getInstance()->Execute() to ge the values instead of mysqli_query

, here I had tried DB::getInstance()->Execute() but it is not working at all. So can someone kindly suggest me how to solve this problem? Any help and suggestions will be really appreciable.

Thanks

 

[vekia]

can you show your code with DB::getInstance()->Execute()  ?

your method is definitely bad, and your module will not be accepted

[prestashop_newuser]

 

Hi,

In prestashop I am doing a small module. In that I have some jQuery ajax. For database connection I am using mysqli_connect as mysql_connect has been deprecated.

So my entire script to fetch some data from database through ajax looks like this

 

<?php
include '../../../config/settings.inc.php';
include '../../../config/defines.inc.php';
include '../../../config/config.inc.php';


$connection = mysqli_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_) or die(mysqli_error());
mysqli_select_db($connection,_DB_NAME_) or die(mysql_error());
$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";
$result = mysqli_query($connection,$query) or die(mysqli_error());
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }
?>

 

Here its working absolutely fine. But as Prestashop tells to use 

 

DB::getInstance()->Execute() to ge the values instead of mysqli_query

, here I had tried DB::getInstance()->Execute() but it is not working at all. So can someone kindly suggest me how to solve this problem? Any help and suggestions will be really appreciable.

Thanks

 

 

 

Here is the total code by using Db::getInstance() method

 

<?php
include '../../../config/settings.inc.php';
include '../../../config/defines.inc.php';
include '../../../config/config.inc.php';




$connection = mysqli_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_) or die(mysqli_error());
mysqli_select_db($connection,_DB_NAME_) or die(mysql_error());
$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";
$result = Db::getInstance()->getRow($query);//I have tried this one first then I tried the second one below
$result = Db::getInstance()->Execute($query);//also I had tried this one
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }
?>

[vekia]

have you read documentation that i posted in another thread with your question?

 

Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(); for select queries

[Azoda.net]

if you want to query update or insert let's use Db::getInstance(_PS_USE_SQL_SLAVE_)->execute();

else when you select queries use Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS()

[harshal77988]

Hello use like this for fetching records from database

my prestashop version is 1.6x

 

you need to use execute() instead of ExecuteS() 

 

sql = 'SELECT sum(product_count) AS total_likes_count product_count FROM '._DB_PREFIX_.'count_likes WHERE product_id="'.$product_id.'"';

$results = Db::getInstance()->execute($sql);

$count_total_likes = $results['total_likes_count'];

[luthfifr]

Hi, I also have a problem with execute() function. I have a query that looks like this:

$sql_addr = "SELECT a.id_address FROM pspf_address a JOIN pspf_customer b ON a.id_customer=b.id_customer WHERE b.email=".$_POST['customer_id'];
if($results = Db::getInstance(_PS_USE_SQL_SLAVE_)->Execute($sql_addr)){
	$addrID = $results;
} else {
	$addrID = "0";

That SELECT query should return a result with 1 column and 1 row with value of 5 (I tried the query in phpmyadmin).

 

But when it is executed in my php file, it returns value of 1. What did I do wrong?

 

Thank you.

Edited January 23, 2017 by luthfifr (see edit history)