Writing database query without writing a new module

[JustStarted]

Hi,

Is there any way to execute some SQL query and put the result in one of my CSS

area?
Here what i want to do:

1. I want to create a block in home page and show the SQL query result in that block?

Can you please provide step by step ins. For a beginer its very helpful.

Thnak you

[rocky]

Use code like the following in the PHP file that calls the TPL:

$result = Db::getInstance()->executeS('SELECT * FROM `'._DB_PREFIX_.'table');

This code will get an array of all the rows in table. Change table to whatever you want to get. You can add individual fields to the SELECT clause or add a WHERE clause to the end to choose specific items.

You can then use code like the following to pass the array into a TPL file:

$smarty->assign('table', $result);

Then you can use a foreach loop in the TPL file:

{foreach from=$table item=row}
   {$row.field}
{/foreach}

Change field to whatever field in the table you want to display.

[JustStarted]

great!!

[hotseon]

It too diffcult to me...

[lpfernandes]

Hi
Trying to run you code but get the following error:
Fatal error: Smarty error: [in /homepages/32/d285565264/htdocs/e-com/themes/prestashop/status.tpl line 13]: syntax error: foreach: 'item' must be a variable name (literal string) (Smarty_Compiler.class.php, line 1174) in /homepages/32/d285565264/htdocs/e-com/tools/smarty/Smarty.class.php on line 1095

What I'm trying to do is a simple page that reads the time an order was placed and show a different image according to how long has the order been placed.
I'm completely new to ps so any help would be much appreciated.

Thanks

[rocky]

I've corrected my code above. It should be item=row not item=$row

[lpfernandes]

Thanks, it does not shows any errors now but I cant get it to display anything either?!

[brandonc503]

nothing displays for me eather

 

$APIdata = Db::getInstance()->executeS('SELECT * FROM `'._DB_PREFIX_.'address');

$smarty->assign('tableone', $APIdata);

 

{foreach from=$tableone item=row}

{row.field}

{/foreach}

 

 

did you put at ` in wrong spot?

Edited August 10, 2012 by brandonc503 (see edit history)

[Brenz]

You need to use the real column name from ps_address instead of 'field"

 

{foreach from=$tableone item=row}

{row.address1}

{/foreach}

[brandonc503]

Then you can use a foreach loop in the TPL file:

 

{foreach from=$table item=row}

{$row.field}

{/foreach}

What if I dont wana to display in the template file but I want the $row.field to = the current variable $shipping_cost

[brandonc503]

like this?

 

foreach (from $tableone item=row)

{

$row.postalcode

}

[brandonc503]

and should

$result = Db::getInstance()->executeS('SELECT * FROM `'._DB_PREFIX_.'table');

have a ` after table?