how to show a database table created by me in a module

[pmaojo]

I have a table called pss_eventos created and want to show the results in the my account page. I think Im almost there, can you help me make this work?

 

thankyou
 

PHP

 

<?php

class SeguimientoEventos extends Module {

 

    function __construct() {

        $this->name = 'seguimientoeventos';

        $this->tab = 'front_office_features';

        $this->version = 1.0;

        $this->author = 'Pelayo Maojo';

        parent::__construct();

        $this->page = basename(__FILE__, '.php');

        $this->displayName = $this->l('Seguimiento de acciones');

        $this->description = $this->l('Sistema de seguimiento de servicio al cliente.');

    }

 

    function hookcustomerAccount($params) {

   global $smarty;

 

$sql="select * from pss_eventos"; // creamos la sentencia sql

    $query=mysql_query($sql); // ejecutamos la consulta

    $x=0;

    while($row=mysql_fetch_array($query)){

        $eventos[$x]=array($col['id_accion'], $col['id_cliente'], $col['desc_accion '], $col['fecha_hora']);

        $x++;

    }

    mysql_close();

    

    $smarty->assign('eventos' ,$eventos);

 

} 

 

public function getContent () {

   // Instruciones …

}

 

   function install() {

      if(parent::install() == false)

         return false;

      return true;

   }

}

?>

 

TPL:

 

<div class="row">

<h2>Seguimiento de servicios</h2>

<table class="table stripped" border="0" cellspacing="5" cellpadding="5">

            {foreach from=eventos key=key item=acciones name=lista }

            <tr class="">

                <td align="left">{$acciones['id_accion']}</td>

                <td align="left">{$acciones['id_cliente']}</td>

                <td align="justify">{$acciones['desc_accion']}</td>

                <td align="center">{$acciones['fecha_hora']}</td>

                <td align="center">{$acciones['notas']}</td>

            </tr>

            {foreachelse} <p>No hay acciones</p>

            {/foreach}    

        </table></div>

 

 

Result:
 

https://www.dropbox....25 22.04.49.pdf

 

it shows: e, e, e, e ??

 

And it should be

https://www.dropbox....25 22.05.09.pdf
 

[loulou66]

hi

 

in PS 1.5+ try this

  function hookcustomerAccount($params) {
   global $smarty;
   $eventos = Db::getInstance()->executeS('SELCET * FROM `'._DB_PREFIX_.'eventos`');   
  $this->context->smarty->assign('eventos', $eventos)    
   }

in  TPL

<div class="row">
<h2>Seguimiento de servicios</h2>
   <table class="table stripped" border="0" cellspacing="5" cellpadding="5">
     {if eventos}
        {foreach from=eventos key=key item=acciones }
            <tr class="tr-'.$key.'">
                <td align="left">{$acciones['id_accion']}</td>
                <td align="left">{$acciones['id_cliente']}</td>
                <td align="justify">{$acciones['desc_accion']}</td>
                <td align="center">{$acciones['fecha_hora']}</td>
                <td align="center">{$acciones['notas']}</td>
            </tr> 
          {/foreach}
      {else}
          <p>No hay acciones</p>
      {/if}
  </table>
</div>

@++

 

Loulou66

[pmaojo]

hi

 

in PS 1.5+ try this

  function hookcustomerAccount($params) {
   global $smarty;
   $eventos = Db::getInstance()->executeS('SELCET * FROM `'._DB_PREFIX_.'eventos`');   
  $this->context->smarty->assign('eventos', $eventos)    
   }

in  TPL

<div class="row">
<h2>Seguimiento de servicios</h2>
   <table class="table stripped" border="0" cellspacing="5" cellpadding="5">
     {if eventos}
        {foreach from=eventos key=key item=acciones }
            <tr class="tr-'.$key.'">
                <td align="left">{$acciones['id_accion']}</td>
                <td align="left">{$acciones['id_cliente']}</td>
                <td align="justify">{$acciones['desc_accion']}</td>
                <td align="center">{$acciones['fecha_hora']}</td>
                <td align="center">{$acciones['notas']}</td>
            </tr> 
          {/foreach}
      {else}
          <p>No hay acciones</p>
      {/if}
  </table>
</div>

@++

 

Loulou66

Hi

than you very much!

 

Your solution does not work though, it shows now a white page...

 

its not Not becouse you writte SELCET instead of SELECT,  i fixed that...)

 

It is a problem with the php part, couse If I delete it it shows again the content,

[loulou66]

Coucou

 

i'm  sorry ^^ i forget one " ; " 

repalce
$this->context->smarty->assign('eventos', $eventos)
by
$this->context->smarty->assign('eventos', $eventos);

@++

 

Loulou66

[pmaojo]

Ready Its working Now

it but the correct foreach is (using $ for $eventos behind from=):

 

<div class="row">

<h2>Seguimiento de servicios</h2>

   <table class="table stripped" border="0" cellspacing="5" cellpadding="5">

             {foreach from=$eventos key=key item=acc }

            <tr>

                <td align="left">{$acc['id_accion']}</td>

                <td align="left">{$acc['id_cliente']}</td>

                <td align="justify">{$acc['desc_accion']}</td>

                <td align="center">{$acc['fecha_hora']}</td>

                <td align="center">{$acc['notas']}</td>

            </tr> 

      {foreachelse}

          <p>No hay acciones</p>

      {/foreach}

  </table>

</div>

 

Thankyou for the great help!

Now I need to make the SQL table editable from back office and filter by user! 

[loulou66]

HI

 

for the back office with the ObjectModel you  add /update /save /getfields of your BDD

and Helper for display Form or List

 

look this

http://doc.prestashop.com/display/PS15/Diving+into+PrestaShop+Core+development#DivingintoPrestaShopCoredevelopment-TheObjectModelclass

and

http://doc.prestashop.com/display/PS15/Helpers

 

@++

 

Loulou66

[pmaojo]

so far, now I can see the values, but want to filter it a little bit:

 

I want to insert the customer ID into the SQL query with WHERE.

 

How could I use the customer ID variable of Prestashop here to make this work and only show the user his rows?:

 

$eventos = Db::getInstance()->executeS('SELECT * FROM `pss_tabla` WHERE id_cliente=  customer_id  }'); 

[loulou66]

Hi

$cutomerId=(int)$this->context->customer->id;

$eventos = Db::getInstance()->executeS('SELCET * FROM `'._DB_PREFIX_.'eventos` WHERE id_cliente ="'.$cutomerId.'" '); 

@++

 

loulou66

[pmaojo]

Hello, 

this works: 
 

   global $smarty;

   $userid = $this->context->customer->id;

   $eventos = Db::getInstance()->executeS("SELECT * FROM pss_eventos WHERE id_cliente=$userid"); 

   $this->context->smarty->assign('eventos', $eventos);

 

Thankyou, 

My last problem is the manager I want to create.

[yaimaru]

jmioim