Get data from a custom table into a custom page

[freakystreak]

I have added a custom page (not using the admin's create page feature). This page displays within my Prestashop without a problem. What I need to do is run a mysql query to get data from a table I've added in the main database.

 

How can I run the query and display the results in my custom prestashop page. I've searched google and this forum to find an answer but as yet not found a solution. Here is the code I've used to create the page.

 

newpage.php

 

<?php

include(dirname(__FILE__).'/config/config.inc.php');

include(dirname(__FILE__).'/header.php');

$smarty->display(_PS_THEME_DIR_.'newpage.tpl');

include(dirname(__FILE__).'/footer.php');

?>

 

and my template file newpage.tpl

 

<h2>{l s='This is new page title'}</h2>
<h3>{l s='To find a product, please type its name in the field below'}</h3><div> </div>
<p><a href="{$base_dir}" title="{l s='Home'}"><img src="{$img_dir}icon/home.gif" alt="{l s='Home'}" class="icon" /></a><a href="{$base_dir}" title="{l s='Home'}">{l s='Home'}</a></p>
<p>A test page</p>

 

Any help or links to tutorials or guide would be greatly appreciated.

[freakystreak]

Got this working. If anyone needs to do this themselves then please ask me and I'll tell you how I did it.

[vekia]

i suppose that you achieved it with code like:

$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'DATABASE`');

then in $query variable you've got query result

[6co]

hello Freakystreak, i'm trying to do something familiar :

- Custom text field with the same function, getting data from a table in database i added. i can't get it work

- I'd like to add somme customization by adding my text expressions stored in database (>3000) : 1 product with 3000 text customizations,

 

 

 

can you help please?

[lucasampo]

Hi vekia,

I'm trying do to something similar, eg. to show data from a custom table in ps database, in a personalized page. 

I'm not able to write the correct code for selecting data from the table and just printing it on screen on the custom page.

I'm trying to use the $his with a new variable $myvariable.

I need to select a column "x" from "mytable" for the customer is looking the page. I think is with "WHERE customer id", but it still doesn't work.

Could you help please?

Many thanks!

Luca

[vekia]

Hi vekia,

I'm trying do to something similar, eg. to show data from a custom table in ps database, in a personalized page. 

I'm not able to write the correct code for selecting data from the table and just printing it on screen on the custom page.

I'm trying to use the $his with a new variable $myvariable.

I need to select a column "x" from "mytable" for the customer is looking the page. I think is with "WHERE customer id", but it still doesn't work.

Could you help please?

Many thanks!

Luca

$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'mytable where id=2`');

[Pedro Lima]

Hi,

 

I'm trying to do something similar in order to extract some info to fill in the Dynamic Remarketing file for Google.

 

I want to print a table with some fields like product id, product name, product image URL, etc...

 

Could you share your code so I can have a starting point to do this?

 

Thanks a lot.

 

Best regards,

Pedro Lima

[tinybibiya]

Got this working. If anyone needs to do this themselves then please ask me and I'll tell you how I did it.

 

Hi,

 

Can you teach me? I am trying to get some data from database and display my stock count for dropshippers to view in 1 glance instead of checking in each product list.

[email protected]

[Shellanza]

$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'mytable where id=2`');

 

Hi Vekia,

I'm trying to understand how to manage this data with prestashop.

In this case your string:

 

$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'mytable where id=2`');

 

has to be placed on the TPL file or into the PHP? Where can I have a guide, a manual, a tutorial eventually?

 

Cheers!

[Pedro Lima]

Hi Vekia,

I'm trying to understand how to manage this data with prestashop.

In this case your string:

 

 

$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'mytable where id=2`');

 

has to be placed on the TPL file or into the PHP? Where can I have a guide, a manual, a tutorial eventually?

 

Cheers!

This is PHP code so I believe you will have to put this there. Although TLP files also have PHP code sometimes. Well, I suggest you to try with each one at a stime and do some echos to trach whether is outputting something or not.

[Shellanza]

Ok I'm gonna have some try...

Actually if I put into the tpl file this:

<?php
$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'provvigioni`');
?>

I get beck this:

executeS('SELECT * FROM `'._DB_PREFIX_.'provvigioni`'); ?>

So I guess is not the proper way... weird I see the closing php tag too... 

by the way, I will keep update so if I find a solution maybe someone would be appreciate.

[Shellanza]

Still no luck. I also try to create my first module with prestashop but no idea how to run php into a prestashop tpl page neither how to pass variables eventually... jeez...

[Pedro Lima]

Still no luck. I also try to create my first module with prestashop but no idea how to run php into a prestashop tpl page neither how to pass variables eventually... jeez...

 

I basically gave up using Prestashop. Used it only once in a huge online shop and it was a TRUE NIGHTMARE that lasted almost half-year to have it working flawless.

 

Now I only use WordPress with Woocommerce. Trully stable and easily customizable!

[tinybibiya]

Still no luck. I also try to create my first module with prestashop but no idea how to run php into a prestashop tpl page neither how to pass variables eventually... jeez...

 

Try referring this topic. i managed to get it done and display the values from my database to my custom page.

 

https://www.prestashop.com/forums/topic/345759-get-data-from-db-in-tpl/

 

You need 2 files.

 

Your tpl put in /public_html/themes/[your_theme]

Your php put in /public_html/controllers/front

 

You also need to create a page in Preferences -> SEO & URLs. Once you put in the two files in your ftp with the correct class name, the option for you to choose to make a page is there.

[vekia]

if you dont want to create controller it is enough to include init.php, then mysql queries will work properly and whole prestashop classes/controllers too.

to check what i mean please analyse the sources of ajax_homeslider.php that is available in homeslider module directory :-)

[som90]

I need to work with custom database table in registration template in prestashop.Would you please help me?i am in need of urgency.

[Knowband Plugins]

Add the following line of code in the starting of your custom page:

 

include(dirname(__FILE__).'/../../config/config.inc.php');

 

Note: The path to include the file might be different as well based on the relative path of your custom file.

 

This line will include the /config/config.inc.php file in your custom page and after that, you can run SQL queries or any other PrestaShop code like you do on any other page.

 

An example of an SQL query:

 

$query = 'select * from '._DB_PREFIX_.'orders where id_order=2';

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

Check following links for any further details about running SQL queries in PrestaShop:

 

http://doc.prestashop.com/display/PS16/Best+Practices+of+the+Db+Class

[kirubanidhi]

I'm execute below this query, its getting error. can you help me.how to resolve it.

 

 

Code:

 

$email = trim(Tools::getValue('email'));

$sql='SELECT company FROM `._DB_PREFIX_.`customer WHERE email='.$email;

$result=DB::getInstance()->Execute($sql);

if($result){

echo "True:".$result;

}else{

echo "False:".$result;

}

 

Error:

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com' at line 1
 

SELECT company FROM `._DB_PREFIX_.`customer WHERE [email protected]
at line 615 in file classes/db/Db.php

 

609.             WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
610.         }
611.         else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
612.         {
613.             if ($sql)
614.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
615.             throw new PrestaShopDatabaseException($this->getMsgError());
616.         }
617.     }
618.
619.     /**

• DbCore->displayError - [line 314 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 454 - classes/db/Db.php] - [1 Arguments]
• DbCore->execute - [line 280 - controllers/front/AuthController.php] - [1 Arguments]
• AuthControllerCore->processSubmitLogin - [line 268 - controllers/front/AuthController.php] - [0 Argument]
• AuthControllerCore->postProcess - [line 158 - classes/controller/Controller.php] - [0 Argument]
• ControllerCore->run - [line 348 - classes/Dispatcher.php] - [0 Argument]
• DispatcherCore->dispatch - [line 55 - index.php] - [0 Argument]

Directory /var/www/html/sf_prestashop/shop/log is not writable

[changa]

hello i am in my page tpl on prestashop i want to display the list of all product name but i have bee working on it for more that 10 hours

 

[Rachit Agarwal]

On 5/29/2016 at 7:09 PM, Shellanza said:

Ok I'm gonna have some try...

Actually if I put into the tpl file this:

<?php
$query=Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT * FROM `'._DB_PREFIX_.'provvigioni`');
?>

I get beck this:

executeS('SELECT * FROM `'._DB_PREFIX_.'provvigioni`'); ?>

So I guess is not the proper way... weird I see the closing php tag too... 

by the way, I will keep update so if I find a solution maybe someone would be appreciate.

All the queries in prestashop should be written in classes, helpers & controllers. So you should write this query in PHP file instead on TPL file.

Please try again by putting in PHP file and let me know in case of any issue.

[Rachit Agarwal]

On 6/15/2017 at 11:07 AM, kirubanidhi said:

I'm execute below this query, its getting error. can you help me.how to resolve it.

 

 

Code:

 

$email = trim(Tools::getValue('email'));

$sql='SELECT company FROM `._DB_PREFIX_.`customer WHERE email='.$email;

$result=DB::getInstance()->Execute($sql);

if($result){

echo "True:".$result;

}else{

echo "False:".$result;

}

 

Error:

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com' at line 1
 

SELECT company FROM `._DB_PREFIX_.`customer WHERE [email protected]
at line 615 in file classes/db/Db.php

 

609.             WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
610.         }
611.         else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
612.         {
613.             if ($sql)
614.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
615.             throw new PrestaShopDatabaseException($this->getMsgError());
616.         }
617.     }
618.
619.     /**

• DbCore->displayError - [line 314 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 454 - classes/db/Db.php] - [1 Arguments]
• DbCore->execute - [line 280 - controllers/front/AuthController.php] - [1 Arguments]
• AuthControllerCore->processSubmitLogin - [line 268 - controllers/front/AuthController.php] - [0 Argument]
• AuthControllerCore->postProcess - [line 158 - classes/controller/Controller.php] - [0 Argument]
• ControllerCore->run - [line 348 - classes/Dispatcher.php] - [0 Argument]
• DispatcherCore->dispatch - [line 55 - index.php] - [0 Argument]

Directory /var/www/html/sf_prestashop/shop/log is not writable

I believe email should be enclosed within inverted commas. So your query should be like below:

SELECT company FROM `._DB_PREFIX_.`customer WHERE email="[email protected]"