Problem with a sql query and the dashboard

Archived from the Xataface Developers forum.

siggj — Sun Sep 19, 2010 9:29 am

Hello everybody !
I have a question about sql and the dashboard from the wiki.

So, I have a dashboard.php and in the handle function i have this :

Code: Select all

`$app =& Dataface_Application::getInstance();  // reference to Dataface_Application object
        $auth =& Dataface_AuthenticationTool::getInstance();
        $user =& $auth->getLoggedInUser();

        // Perform a custom SQL Query:
          $res = mysql_query(“select * from Event where fk_user = $user->getValue(‘id’) “, $app->db());

      
        df_display(array(‘Project’=>$projects,’Event’=>$res), ‘dashboard_project.html’);`

Then in the dashboard_project.html (from the templates directories) , I have :

Code: Select all

{use_macro file="Dataface_Main_Template.html"}                 {fill_slot name="main_column"}                          {foreach from=$Event item=event}                            <li><a href="{$event->getURL('-action=view')}#embed"> {$event->getTitle()} </a><br  /><em>from project name_of_project</em></li>                     {/foreach}                   {/fill_slot}             {/use_macro}

But this code doesn’t work…

Anybody know something about this ?

Thank you !

---

shannah — Mon Sep 20, 2010 10:56 am

The $res variable is not an array. It’s a database resource pointer. Hence you can’t loop through it directly with a foreach loop. You first need to put the elements into an array. e.g.

Code: Select all

$items = array(); while ($row = mysql_fetch_assoc($res) ) $items[] = $row; @mysql_free_result($res);  // free up memory with database pointer now that we're done.

---

siggj — Tue Sep 21, 2010 1:43 pm

Hello, thanks for responding !

I dont understand very well..
Can i do Something like this ?

Code: Select all

`$app =& Dataface_Application::getInstance();  // reference to Dataface_Application object
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();

$res = mysql_query(‘SELECT * FROM Event’, $app->db());

$items = array();
while ($row = mysql_fetch_assoc($res) ) $items[] = $row;
@mysql_free_result($res);  // free up memory with database pointer now that we’re done.

df_display(array(‘Project’=>$projects,’Event’=>$items), ‘dashboard_project.html’);`

Thank you for your time !

---

shannah — Tue Sep 21, 2010 1:52 pm

Yes.

---

siggj — Wed Sep 22, 2010 3:02 pm

I’m sorry but it doesn’t work… I have exactly this :

Code: Select all

<?php class actions_dashboard_project {     function handle(&$params){          $app =& Dataface_Application::getInstance();  // reference to Dataface_Application object                                // Perform a custom SQL Query:       $sql = 'SELECT * FROM Event;';                 $res = mysql_query($sql, $app->db());              $items = array();       while ($row = mysql_fetch_assoc($res)) $items[] = $row;               df_display(array('Event'=>$items), 'dashboard_project.html');     } }

And then in the dashboard :

Code: Select all

{foreach from=$Event item=event}             <li>{$event->val('ID_Event')}</li>        {/foreach}

So I ask you if you can explain me where it’s wrong, and why…
THank you for your time ..

---

shannah — Fri Sep 24, 2010 10:53 am

You are passing associative arrays to the template, but associative arrays don’t have any methods (e..g no val() method). The val() method is available only when working with Dataface_Record objects.

Using arrays in smarty templates, you access their elements with dot notation.

e.g.

Code: Select all

{foreach from=$Event item=event}             <li>{$event.ID_Event}</li>        {/foreach}