Custom Sql

Archived from the Xataface Users forum.

chappers — Fri Nov 24, 2006 8:51 am

I have a bit of sql that i want to include in my application. (the sql references 2 tables, does a sum, group by etc etc).

I want there to be either a new TAB (next to the current tables) at the top, or a menu option to the left, which when pressed, will run this sql and present the results to the user.

What is the best way to achieve this ?

Thanks.

Also, while i remember, how do you get the navigation menu to appear on the left ?

Once again, apologies if these are simple questions, am new to Dataface. I think the tool is a fantastic piece of software, really excellent. Am just finding it a little difficult at the minute to find my way round the documentation. There is an excellent introduction, but after that am finding it hard to find things.

---

shannah — Fri Nov 24, 2006 2:56 pm

Hi Neil,

You would do this with a combination of “actions” and templates. I have tried to augment the actions tutorial to be a bit more helpful when it comes to more complicated actions like this: http://framework.weblite.ca/documentation/tutorial/getting_started/dataface_actions/view

To make the tables menu appear on the left, add a section to your conf.ini file called “_prefs” and add an option “horizontal_tables_menu” set to 0:

e.g:

Code: Select all

[_prefs] horizontal_tables_menu = 0

To add your own menu options, create a templates directory for your application and place a file named “Dataface_Application_Menu.html” This is where you can place HTML that will be displayed in the left column under the table navigation menu.

Hope this helps a little.

-Steve

---

chappers — Fri Nov 24, 2006 5:52 pm

Hi Steve,

Thanks very much for a quick reply.

With regards to the initial question regarding the custom sql, i think i nearly have it but not quite.

I have created an ACTIONS directory at application level, and created a SCORERSLIST.php file within it, which has the following code :-

db());
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
echo $row[‘player_name’] ;
echo ‘ : Goals Scored: ‘ . $row[‘gs’] . ‘
‘;
}
df_display(array(), ‘Scorerslist.html’);
}
}
?>

This though, prints the scorerslist (ie whats returned from the mysqlquery at the top of the page, outside of the dataface main section.

How do i get it to print within the Dataface main section, ie. just below the details/list/find tabs ??

Thanks again for your help.

---

shannah — Sat Nov 25, 2006 5:21 pm

The first parameter to df_display() is actually an array of symbols that you make available to your template.
e.g. if you did:

df_display(array(‘myname’=>’Bob’), ‘template.html’);

And your template.html looked like:

My name is {$myname}

The output of the template would be ‘My name is Bob’.

So your case you have 2 choices.

1. Store the mysql output in a single variable (say $mysql_output), and pass this to the template to be displayed:
   e.g.

Code: Select all

`ob_start(); // starts output buffering
// Fetch rows from MySQL one at a time
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
  echo $row[‘player_name’] ;
  echo ‘ : Goals Scored: ‘ . $row[‘gs’] . ‘
  ‘;
}
$output = ob_get_contents(); // store the contents fo the output buffer in the $output variable
ob_end_clean();

df_display(array(‘mysql_output’=>$output), ‘Scorerslist.html’);`

Then in your Scorerslist.html file you would simply place:
{$mysql_output}
wherever you wanted the output to appear.

1. You could pass the query results as an array to the template and allow the template to iterate through them (this is probably a better approach).

e.g.

Code: Select all

$rows = array(); while ( $row = mysql_fetch_array($res, MYSQL_ASSOC)){   $rows[] = $row; } df_display(array('rows'=>$rows), 'Scorerslist.html');

Then in the appropriate place of your template you would do:

Code: Select all

`{foreach from=$rows item=”row”}

• Name: {$row.player_name}

  … etc …
{/foreach}`

Hope this helps a little.

-Steve