Jump to content

Get data from a custom table into a custom page


freakystreak

Recommended Posts

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.

Link to comment
Share on other sites

  • 3 weeks later...
  • 3 months later...

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?

  • Like 1
Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

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`');

  • Like 1
Link to comment
Share on other sites

  • 1 year later...

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

Link to comment
Share on other sites

  • 2 months later...
  • 6 months later...

$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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :-)

Link to comment
Share on other sites

  • 9 months later...
  • 3 weeks later...
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:

 


Link to comment
Share on other sites

  • 2 months later...

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.     /**
Directory /var/www/html/sf_prestashop/shop/log is not writable
Link to comment
Share on other sites

  • 3 years later...
  • 2 weeks later...
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.

Link to comment
Share on other sites

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.     /**
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]"

Link to comment
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
×
×
  • Create New...