How to use use db class in an external php file?

prestashop_newuser · November 11, 2013

Hi,

I am doing a small module in prestashop. In that module I have some ajax request. In view file(.tpl file) I am getting the form. From that view file lets say I have one dropdown like this



 <label>Please Select store</label> 

    <select id="storeinfo" class="storeinfo" onClick="getDetails();">

    <option value="Select">Select</option> 

    </select>

and in .js file I am getting the value of city through like this



 function getDetails() {

    var storedetails = $('#storedetails').val();

    $.ajax({

      type: "GET",

      url: baseDir + "/modules/storedetails.php",

      cache: false,

      data: { details : storedetails },

      success: function(html) {

        $(".box").html(html);

      }

    });  

  }

and in storedetails.php I have done the query



<?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(mysqli_error());



$result1 = "SELECT * FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";



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

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

 echo '<div class="store-info-wrap">';



  $phone_num = $row['store_name'];

  $address = $row['address'];

  echo "<strong>Phone Number</strong> ". $phone_num;

  echo "<strong>Store Address</strong> ". $address;  

  echo '</div>';

  }

?>

This is working without any problem. But prestashop tells that you have to use dbclasses whenever you are doing any query like (Tools::getvalue). so here I want to know as I have one external .php file where I have to make my own connection to the database.Then how can I use prestashop db classes here? Any example or any help,suggestions will be really appreciable. Thanks..

Rolige · November 11, 2013

You don't need include settings.inc.php and defines.inc.php, just include config.inc.php and for best practice include the php of your module, for example:

<?php
require_once(dirname(__FILE__).'/../../config/config.inc.php');
include_once(dirname(__FILE__).'/your_module_name.php');

Db::getInstance()->ExecuteS("SELECT * FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'");
echo 'Finished';

prestashop_newuser · November 11, 2013

You don't need include settings.inc.php and defines.inc.php, just include config.inc.php and for best practice include the php of your module, for example:
<?php
require_once(dirname(__FILE__).'/../../config/config.inc.php');
include_once(dirname(__FILE__).'/your_module_name.php');

Db::getInstance()->ExecuteS("SELECT * FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'");
echo 'Finished';

But how to use db class inside while loop? You can see I have used while loop and in that my query like this

while($row = mysqli_fetch_array($result)){
 echo '<div class="store-info-wrap">';


  $phone_num = $row['store_name'];
  $address = $row['address'];
  echo "<strong>Phone Number</strong> ". $phone_num;
  echo "<strong>Store Address</strong> ". $address;  
  echo '</div>';
  }

So how to use that db class inside this?

vekia · November 11, 2013

while($row = mysqli_fetch_array($result)){
 echo '<div class="store-info-wrap">';

  Db::getInstance()->ExecuteS("SELECT * FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'");
  $phone_num = $row['store_name'];
  $address = $row['address'];
  echo "<strong>Phone Number</strong> ". $phone_num;
  echo "<strong>Store Address</strong> ". $address;  
  echo '</div>';
  }

+ don't forget about require_once main init file

prestashop_newuser · November 11, 2013

while($row = mysqli_fetch_array($result)){
 echo '<div class="store-info-wrap">';

  Db::getInstance()->ExecuteS("SELECT * FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'");
  $phone_num = $row['store_name'];
  $address = $row['address'];
  echo "<strong>Phone Number</strong> ". $phone_num;
  echo "<strong>Store Address</strong> ". $address;  
  echo '</div>';
  }

+ don't forget about require_once main init file

When I am doing this code its working fine

<?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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$result = mysqli_query($connection,$query) or die(mysqli_error());
while($row = mysqli_fetch_array($result)) { 
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
  }
?>

But when I am doing only upto this part

<?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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$result = Db::getInstance()->execute($query);
while($row = mysqli_fetch_array($result)) { 
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
  }
?>

Its not working here...

Please guide me..

I have also tried

$result = Db::getInstance()->executeS($query);

But it is also not working.

But when I am doing

$row = Db::getInstance()->getRow($query);

it is giving me only one row as expected but I have more rows in my database by the above query.

By using getRow() my code 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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$row = Db::getInstance()->getRow($query);
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
?>

It is only showing one row but I have more than one row in my database and I want them

Edited November 11, 2013 by prestashop_newuser (see edit history)

vekia · November 11, 2013

When I am doing this code its working fine

<?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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$result = mysqli_query($connection,$query) or die(mysqli_error());
while($row = mysqli_fetch_array($result)) { 
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
  }
?>

But when I am doing only upto this part

<?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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$result = Db::getInstance()->execute($query);
while($row = mysqli_fetch_array($result)) { 
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
  }
?>

Its not working here...

Please guide me..

I have also tried

$result = Db::getInstance()->executeS($query);

But it is also not working.

But when I am doing

$row = Db::getInstance()->getRow($query);

it is giving me only one row as expected but I have more rows in my database by the above query.

By using getRow() my code 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 `store_name` FROM "._DB_PREFIX_."storeinfo WHERE `store_name`='".$_GET['storedetails']."'";
$row = Db::getInstance()->getRow($query);
  $phone_num = $row['state_name'];
echo '<option value="'.$phone_num.'">'.$phone_num.'</option>';
?>

It is only showing one row but I have more than one row in my database and I want them

+ don't forget about require_once main init file

you forgot about init library

use DB object, not simple mysql_conenction

Rolige · November 11, 2013

Take a look at prestashop docs for DB class best practices: http://doc.prestashop.com/display/PS15/DB+class+best+practices

Regards.

prestashop_newuser · November 12, 2013

you forgot about init library

use DB object, not simple mysql_conenction

@vekia I am not getting you what have you told.Can you tell me more about this? The points that you have told is like

don't forget about require_once main init file

you forgot about init library

use DB object, not simple mysql_conenction

So can you tell me more about this..I have already wasted 2 days with it. So any help and suggestions with any piece of example codes will be really appreciable.

Edited November 12, 2013 by prestashop_newuser (see edit history)

vekia · November 12, 2013

ohh so sorry to not explaining well.

if you want to use prestashop core functions like DB object (to database queries) - you need to include one more file:

include_once('../../init.php');

prestashop_newuser · November 12, 2013

ohh so sorry to not explaining well.

if you want to use prestashop core functions like DB object (to database queries) - you need to include one more file:

@vekia still its not working . Please guide me how to do this?

vekia · November 12, 2013

but what doesn't work? now you can use DB object

for example:

Db::getInstance()->ExecuteS('SELECT `id_category`,`name` FROM `'._DB_PREFIX_.'category_lang` LIMIT 10');

prestashop_newuser · November 12, 2013

but what doesn't work? now you can use DB object

for example:
Db::getInstance()->ExecuteS('SELECT `id_category`,`name` FROM `'._DB_PREFIX_.'category_lang` LIMIT 10');

@vekia I don't know but DB object is not working even I tried to include init file. by the way can you give me your mail id so that I can send you the module and you kindly check the issue.. I have already wasted 2 days in this.. and now I am totally blank...

Edited November 12, 2013 by prestashop_newuser (see edit history)

vekia · November 12, 2013

but what you mean that it doesn't work? what you've got in query results? you've got some errors? explain please

prestashop_newuser · November 12, 2013

but what you mean that it doesn't work? what you've got in query results? you've got some errors? explain please

I tried like this<?php
include '../../../init.php';
include '../../../config/settings.inc.php';
include '../../../config/defines.inc.php';
include '../../../config/config.inc.php';




$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";
$result= Db::getInstance()->ExecuteS($query);
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }
?>

As per your answer I made include init file and added db classes but Its not working.. it is not giving any query result from the database

But when I am doing my code like under its working

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

Edited November 12, 2013 by prestashop_newuser (see edit history)

vekia · November 12, 2013

second method is WRONG, your module will never be accepted on addons store with queries where you use there.

include init file after code to include config.inc.php file

prestashop_newuser · November 12, 2013

second method is WRONG, your module will never be accepted on addons store with queries where you use there.

include init file after code to include config.inc.php file

Yes I have included the init file just after config.inc.php but its not working again...

Here is the code

<?php


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


$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";
$result= Db::getInstance()->ExecuteS($query);
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }
?>

prestashop_newuser · November 12, 2013

Yes I have included the init file just after config.inc.php but its not working again...

Here is the code

<?php


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


$query = "SELECT DISTINCT `state_name` FROM "._DB_PREFIX_."storeinfo WHERE `country_name`='".$_GET['sta']."'";
$result= Db::getInstance()->ExecuteS($query);
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }
?>

@vekia any help,suggestions please

is there any help from you guys otherwise I have to quit from prestashop

Edited November 12, 2013 by prestashop_newuser (see edit history)

vekia · November 12, 2013

you still use mysqli functions. as i said before this method is WRONG

why you use mysqli method? you've got $result variable with query results from DB object

while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }

prestashop_newuser · November 12, 2013

you still use mysqli functions. as i said before this method is WRONG

why you use mysqli method? you've got $result variable with query results from DB object
while($row = mysqli_fetch_array($result)) { 
  $data=$row['state_name'];
echo '<option value="'.$data.'">'.$data.'</option>';
  }

oh..God finally got it. I have solved the problem. After two days of try finally got the solution. Prestashop rocks

Edited November 12, 2013 by prestashop_newuser (see edit history)

How to use use db class in an external php file?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in