Creating table views

Archived from the Xataface Users forum.

geller — Fri Aug 25, 2006 3:08 pm

I’m in the process of setting up a database driven ‘restaurant menu’ website where the restaurant owners are able to update their own menus and restaurant details etc.

I am using a single table per restaurant but want to create different views from that table, effectively make it appear like 4-5 individual tables. Is there a way within dataface to do this.

njw — Fri Aug 25, 2006 3:37 pm

There may be a way, but why are you using a single table when you really want 4 or 5? Surely it would be much easier with a normalised database with tables for: restaurant details, menus (by week perhaps?), dishes (then they can be easily added back in again), etc. Dataface would handle that structure easily. You could add the restaurant code on each record if you wanted to separate the restaurants.

Different views could be done with coding, but why paddle against the current?

Neil

shannah — Fri Aug 25, 2006 3:47 pm

I’m not sure that I understand completely what you are trying to do. A data-driven restaurant menu web site would be well suited to Dataface though.

Some specific questions whose answers may help me to understand better:

You say that you are using a single table per restaurant. Does this mean that for each restaurant you have a separate table? Or to you mean that you have one table and you are storing the data for all restaurants in this table?

Just off the top of my head, you’d probably want to have structure similar to the following:

Tables:
restaurants
menus
dishes

Table descriptions:
restaurant table holds information about each restaurant like name, address, contact info, etc..
menus table holds information about a particular menu (perhaps a wine menu, or a lunch menu, or a dinner menu, etc..).
dishes table holds information about a particular dish (name, description, prices, photo, etc…)

Relationships:

Each restaurant can have many menus. Each menu can have many dishes.

With Dataface you would set it up so that a particular restaurant owner would go directly to the record for his restaurant. There he would see info about the menus and dishes for that restaurant (using relationships).

Does this sound like what you want to accomplish?

-Steve

geller — Sat Aug 26, 2006 6:54 am

I guess ideally that is the kind of structure that should be implemented but I have approached from a non DB orientated background. I was also under the impression that if a table were being updated it is locked while the update is taking place. If numerous people have the ability to access and update then in a normailised structure they will be trying to alter the same table/tables?

I am using 2 databases one which is accesible only by the webmaster with all the contact details for each place on it which is searchable. Then a second DB which will be accessible by the owners for them to update their homepage and menus.

I started with multiple tables but changed to 1 table per establishment for ease of implementation and manageability (200+ restaurants…. maybe!). Every aspect of the owners page is changeable by him apart from the layout & colour scheme. The owner table has only 27 fields and would require 5 views:-

1 for the restaurant homepage details
KEY,
restaurant\_name,
address\_details,
opening,
paragraph1,
paragraph2,
image,
menu\_table1,
menu\_table2,
menu\_table3,
menu\_table4,
visa,
delta,
mastercard,
switch,
solo,
accessible,
baby\_changing,
smoking,

2
board1\_description,
board1\_price,
3
board2\_description,
board2\_price,
4
board3\_description,
board3\_price,
5
board4\_description,
board4\_price

I know that it is not the correct way to do it and a relational DB would be far superior because a user could then search for a specific meal for example and bring up the restaurants.. perhaps in the future….

I have tried other code generators phprunner for example and can achieve it with that but I think that the way dataface is constructed is ideally suited for scalability which is what I am looking for.

http://www.eatout-iom.co.uk/creek/ very basic at present but functions!

Cheers Graham

njw — Sat Aug 26, 2006 8:53 am

Hi Graham

I still recommend that you use a relational structure. There is a recent post that tells you how to implement locking if you need to, but the locking is at record level, not table level. Many people can modify a table at the same time without problems, unless two people try to modify the same record at the same time. In your scenario, this is very unlikely.

The realtional design also makes it much easier to manage into the future as you add functionality.

I’m happy to help with the design if you wish.

Neil

shannah — Sat Aug 26, 2006 9:11 am

quote:———————-
I was also under the impression that if a table were being updated it is locked while the update is taking place. If numerous people have the ability to access and update then in a normailised structure they will be trying to alter the same table/tables?
—————————-: end quote

I wouldn’t be too concerned about this. MySQL is used to power a lot of very busy sites that are being updated constantly by multiple users. Unless you’re talking about people saving hundreds of records per second this won’t be an issue. With only 200 restaurants this certainly wouldn’t be an issue. If you were running 200 thousand restaurants you would have to take some care in your design to handle the scale, but you can cross that bridge when you come to it.

quote:———————-
I am using 2 databases one which is accesible only by the webmaster with all the contact details for each place on it which is searchable. Then a second DB which will be accessible by the owners for them to update their homepage and menus.
—————————–: end quote

Just a note. You are able to assign quite fine-grained permissions at even the record level using dataface. Even if everyone’s information is stored in a single table, you can easily make it so that people can only update their own information - or that only the webmaster can update certain records or certain fields.

quote:———————–
I have tried other code generators phprunner for example and can achieve it with that but I think that the way dataface is constructed is ideally suited for scalability which is what I am looking for.
——————————: end quote

Just a note. Dataface is actually not a code generator. It is a framework that enables you to build applications without writing very much code. Code generators generate code for your application but the code tends to be difficult to manage in the long run, since you would have to make changes to the code (sometimes tens of thousands of lines of code) if you need to modify your app. Dataface, on the other hand, does the heavy lifting for you so that you can focus on the important parts of your application. It uses configuration files to set up the behavior of your application, but no code is generated. If you want to change the configuration, you just change the configuration file. It allows you to extend your applications using PHP, but all of the actual code in your app would be your own - not generated.

Subtle distinction, but worth noting i think.

Best regards

Steve

geller — Sat Aug 26, 2006 4:34 pm

Steve/Neil

Thanks for your comments and reassurances regarding relational databases and Dataface Both now appear ideally suited to my needs/requirements.

-Neil if you would be happy to suggest a structure for a database and it is not too much trouble for you, I would be grateful. Please remember I ain’t no DBA as you will probably have guessed.. so if you could take it easy on me!

Cheers
Graham

njw — Sat Aug 26, 2006 11:23 pm

Steve’s template above is a good place to start. Keep it relatively simple to begin with. I have assumed that you can use phpMyAdmin or similar to set up the MySQL tables.

Using your restaurant table as defined above, add a field for UserId. This will allow you to restrict update access to restaurant data

Also consider whether to have a separate field for “Nearest town” and possibly distance from that town centre. In time, this could get more complex by having a table of towns to allow you to select the towns from the table, and then display the restaurants associated with that town. I am building something similar at the moment that you can have a look at if you wish - nothing difficult to do as I am not much of a PHP programmer and my HTML is pretty basic also - www.archomai.co.uk/ACF (user: guest / password: limited). This is read only, but gives you the idea of what can be done.

For the Menus table:

MenuId (Primary key)
RestaurantId (used to tie menus to retaurants)
Menu Name
StartDate
EndDate

Dishes table:
DishId (Primary key)
RestaurantId (You may want this to be specific to the user rather than the restaurant, but this is the simpler route).
Description
Price
Special (flag whether a special or not - or text to describe when this is available)

MenuDishes table:

MenuDishesId (Primary Key)
MenuId
DishId

Set up the login script (see Steve’s tutorial) and consider whether you want account data on the User table for your own purposes e.g. full name, address details, contract length. live flag, etc. This will allow you to restrict people to their own restaurants/menus/dishes through the permissions system - see Steve’s notes - but you can add this code later once the rest is working.

Within Dataface, set up the restaurant and dishes tables in conf.ini as the only tables to display in the tables side menu for now.

Then set up relationships.ini files for as described in the “Getting Started with Dataface” tutorial:

Restaurant -> Menus
Menus -> Dishes using MenuDishes

You’ll need to use the __sql__ option as you need to restrict the SELECT to the current restaurant only.

Set up formatting instructions using fields.ini files. For instance, I usually hide all the Primary key fields, set bigger text areas than the default, change some of the labels, etc. See the documentation on the fields.ini file.

Hopefully that will help you get started. In time, you may be able to have a single dataface application for edit and view, or you may find it easier to have separate applications.

geller — Tue Aug 29, 2006 3:41 pm

-Neil

I am a little lost, have created all tables but it is the relationships that are confusing me. My restaurant table(restinfo) doesn’t appear to have any field in common with any other table? So I have created a field ‘restaurantid’ for that table.

Can you describe the relationships (sql joins etc) and which ‘tables’ directory the ini files should go.

(Restaurant -> Menus)

/tables/restinfo
[menu]
menu.restaurantid = “$restaurantid”

(Menus -> Dishes using MenuDishes? help)

I am afraid I am too new to dataface and sql relationships to grasp it just yet.
Cheers

njw — Tue Aug 29, 2006 4:08 pm

First, you don’t need to define the relationships in SQL, just the tables. Dataface handles the rest. You seem to have set the restaurant - menus relationship correctly. This goes in tables/restinfo.

Because the menus - dishes relation could be a many to many relation, you need a table that connects them together. At its simplest this will have three fields:

MenuDishesId
MenuId
DishesId

MenuDishesId is just a unique primary key.

This table will not appear to a user, it is just used to connect the Menu and Dishes tables together.

In tables\menus, create a relationships.ini file. This needs to hold the more complex relationship definition:

[Dishes]
Dishes.DishesId = MenuDishes.DishesId
MenuDishes.MenuID = “$MenuID”

This will then allow you to add dishes to your menus, and see the list of the dishes within each menu.

It is probably a good idea to add some test data to your main tables (restaurants, menus, dishes) before trying Dataface as it should be easier to see what is going on.

Its worth persevering - it really is good.

Neil

geller — Wed Aug 30, 2006 2:44 pm

hi neil

Ok I have done that bit but I am still a bit puzzeled with the structure.

If an owner wants create separate starter, main menu and specials menu can this be acheived with this structure? How would they determine what description went into each?

Have a look

http://www.eatout-iom.co.uk/datafaceapi/

look a record 110 in restinfo

cheers

shannah — Wed Aug 30, 2006 10:41 pm

Hi Graham,

I took a look at your app and it looks like it’s coming along. First thing I noticed though is that it is not picking up the dataface stylesheet. In your index.php file there is likely a line near the beginning that says:

df_init(__FILE__, ‘http://eatout-iom.co.uk/dataface-build’);

The url as the 2nd parameter of df_init() has to be a valid url to the dataface directory. The url you have supplied, however, is not valid url - it cannot find the directory. Your app will look much better when this gets sorted out.

I looked at record 110, and it appears as though you have found out how to add multiple menus. Correct me if I’m wrong.

Best regards

Steve

geller — Thu Aug 31, 2006 3:35 pm

Looks much better now. If I could just get the relationships thing right in my head.

I am looking at this from an end user perspective. If you refer to the user homepage http://www.eatout-iom.co.uk/creek/ then the user expects to edit 4 menus but this is not what he sees when logging into dataface. I am sure that this will confuse your ‘average joe’ it does me. (Am I missing something?)

I still cannot see the link between Menu and dishes. If I could click on starters and then edit just them directly then that would be logical to me.

Dataface is impressive though…

shannah — Thu Aug 31, 2006 7:06 pm

Hi Graham,

You’re on the right track. Looks like the foundations are in place. You just need to start customizing things a bit to that the flow of control is more to your liking.

Your first issue mentioned is that people logging in don’t see 4 menus. They only see the menus currently in the relationship (this could be none). Is it the case that every restaurant should have 4 menus: starters, main, etc…? Or do you want these just to be the defaults.

If you want the restaurant to start off with 4 menus, then you could use the afterInsert() trigger to automatically add the 4 menus when a new restaurant record is created.

Another thing that can do wonders for intuitivity is to override the rather bland “view” tab. You can do this with the block__view_tab_content() method in the delegate class.

for example

To override the view tab contents for the Restaurants table your delegate class would look like:

Code: Select all

`function block__view_tab_content(){

echo ‘My new content!’;

Try that example to get it working, but before going too far, I recommend you start incorporating Smarty templates for your output.

e.g.

Create a directory named ‘templates’ in your application folder. Add a file named ‘Hello.html’ inside this folder with the following contents.

Code: Select all: Hello world!!

Now change the view_tab_content() method to:

Code: Select all: function block__view_tab_content(){ $context=array(); df_display($context, 'Hello.html'); }

Now your view tab will say “Hello world!!”.

Now pass some variables to your template:

Code: Select all: function block__view_tab_content(){ $app =& Dataface_Application::getInstance(); $record =& $app->getRecord(); // gets the current record $context = array('restaurant'=>&$record); // makes the restaurant $record available to the template // in the $menu variable df_display($context, 'Hello.html'); }

and you can use the menu from your Hello.html template by:

Code: Select all: # {$restaurant->display('Restaurant_name')} .. print more information about the restaurant.... Menus {foreach from=$restaurant->getRelatedRecordObjects('Menus') item=menu} - {$menu->display('Menu_name')} {/foreach} ...

Hope this gets you started… The Dataface way is incremental development. You have a good frame for your app… now you incrementally add the little bits that will make it suit your needs better.

Best regards

Steve

geller — Fri Sep 08, 2006 4:09 pm

How would I extend the login script so that when user ‘creek’ record 110 in restinfo, logged in all he saw was the details for this record only (110)

Ideally this user should have a role of OWNER as the record can only be edited and not deleted or extra records inserted in this table.
Cheers

geller — Wed Aug 30, 2006 2:44 pm

hi neil

Ok I have done that bit but I am still a bit puzzeled with the structure.

If an owner wants create separate starter, main menu and specials menu can this be acheived with this structure? How would they determine what description went into each?

Have a look

http://www.eatout-iom.co.uk/datafaceapi/

look a record 110 in restinfo

cheers

shannah — Wed Aug 30, 2006 10:41 pm

Hi Graham,

df_init(__FILE__, ‘http://eatout-iom.co.uk/dataface-build’);

I looked at record 110, and it appears as though you have found out how to add multiple menus. Correct me if I’m wrong.

Best regards

Steve

geller — Thu Aug 31, 2006 3:35 pm

Looks much better now. If I could just get the relationships thing right in my head.

I still cannot see the link between Menu and dishes. If I could click on starters and then edit just them directly then that would be logical to me.

Dataface is impressive though…

shannah — Thu Aug 31, 2006 7:06 pm

Hi Graham,

You’re on the right track. Looks like the foundations are in place. You just need to start customizing things a bit to that the flow of control is more to your liking.

If you want the restaurant to start off with 4 menus, then you could use the afterInsert() trigger to automatically add the 4 menus when a new restaurant record is created.

Another thing that can do wonders for intuitivity is to override the rather bland “view” tab. You can do this with the block__view_tab_content() method in the delegate class.

for example

To override the view tab contents for the Restaurants table your delegate class would look like:

Code: Select all

`function block__view_tab_content(){

echo ‘My new content!’;

Try that example to get it working, but before going too far, I recommend you start incorporating Smarty templates for your output.

e.g.

Create a directory named ‘templates’ in your application folder. Add a file named ‘Hello.html’ inside this folder with the following contents.

Code: Select all: Hello world!!

Now change the view_tab_content() method to:

Code: Select all: function block__view_tab_content(){ $context=array(); df_display($context, 'Hello.html'); }

Now your view tab will say “Hello world!!”.

Now pass some variables to your template:

Code: Select all: function block__view_tab_content(){ $app =& Dataface_Application::getInstance(); $record =& $app->getRecord(); // gets the current record $context = array('restaurant'=>&$record); // makes the restaurant $record available to the template // in the $menu variable df_display($context, 'Hello.html'); }

and you can use the menu from your Hello.html template by:

Code: Select all: # {$restaurant->display('Restaurant_name')} .. print more information about the restaurant.... Menus {foreach from=$restaurant->getRelatedRecordObjects('Menus') item=menu} - {$menu->display('Menu_name')} {/foreach} ...

Hope this gets you started… The Dataface way is incremental development. You have a good frame for your app… now you incrementally add the little bits that will make it suit your needs better.

Best regards

Steve

geller — Fri Sep 08, 2006 4:09 pm

How would I extend the login script so that when user ‘creek’ record 110 in restinfo, logged in all he saw was the details for this record only (110)

Ideally this user should have a role of OWNER as the record can only be edited and not deleted or extra records inserted in this table.
Cheers

zopemgr — Fri Sep 08, 2006 11:32 pm

OK.. this would be a 2 step process.

There is a bug in the current version that prevents the -redirect flag from being recognized. Download a fixed version of AuthenticationTool.php at http://framework.weblite.ca/development/issue-tracker/20 and install it.
add the following to the beginning of your application’s index.php file:

Code: Select all: if ( @$_REQUEST['-action'] == 'login' ){ $_GET['-redirect'] = 'index.php?-action=custom_login_redirect'; $_REQUEST['-redirect'] = $_GET['-redirect']; }

What this does is set the -redirect flag when the user logs in so that he will be forwarded to a specific URL.
-action=custom_login_redirect specifies that the user should be sent to a custom action named login_redirect. Next you will create this action.

Create a folder in your application’s directory named “pages”.
Add a file in this folder named “login_redirect.php”

This file will be executed when the url index.php?-action=custom_login_redirect is requested.

in the login_redirect.php file, add the following:

Code: Select all: php<br /$authTool =& Dataface_AuthenticationTool::getInstance(); $user =& $authTool->getLoggedInUser(); // gets the currently logged in user. if ( !isset($user) ) trigger_error("No user is logged in", E_USER_ERROR); header('Location: '.$user->getURL()); exit; ?>

What this does, is obtains the currently logged in user, and forwards to his user record. You could use this action to redirect anywhere you like.

Hope this helps.

Best regards

Steve

geller — Sat Sep 09, 2006 1:37 pm

That is kind of what I am after but I don’t want the ‘user’record from the Users table

I need each user to access their own record in the table ‘restinfo’ ( i.e. the details of his restaurant only) and not have any visibility of any other restaurants records. There is a UserID field in ‘restinfo’ if this helps?

Could this be done by adding a sql statement in the conf.ini or the index.php?

Cheers
Graham

shannah — Sat Sep 09, 2006 10:03 pm

Same instructions apply. Just change the line:

Code: Select all: header('Location: '.$user->getURL());

[code]
header(‘Location: index.php?-table=restinfo&UserID=’.$user->val(‘UserID’));

Assuming the users table also has a field named UserID.

geller — Sun Sep 10, 2006 2:36 pm

That works but if the user accesses another of his tables he will have to login again to get back to his restaurant details. Also you can ‘hack’ the URL and access other records.

Not being familiar with Dataface where/how are the table details retrieved from the DB. Can these be altered on a per table basis? Using a modified template for each user perhaps?

zopemgr — Sun Sep 10, 2006 3:08 pm

Ok.

There are two separate issues here.

User permissions
User navigation

You use user permissions to deal with the hacking of the URL to make sure that users can only do what you want them to do.

To make things easier to navigate you can override the view tab (or any other slot to make things easier).

First let’s discuss the user permissions. All permissions for a table are defined in the delegate class by the getPermissions() method:

Code: Select all

`function getPermissions(&$record){
// first get the currently logged in user
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();

// if no user is logged in, then we give read-only access
if ( !$user ) return Dataface_PermissionsTool::READ_ONLY();

// check if the record is set. If the user performs a search
// that returns an empty set, then this record may be null.
if ( !isset($record) ) return Dataface_PermissionsTool::READ_ONLY();

// If the logged in user has the same UserID as the restaurant, then
// this user is the owner of the restaurant.. he has full permissions.
if ( $record->val(‘UserID’) == $user->val(‘UserID’) ) return Dataface_PermissionsTool::ALL();

// otherwise we give read only access
return Dataface_PermissionsTool::READ_ONLY();
}`

For example… you can define a separate getPermissions() method for each table, each in its delegate class, or you can make a default getPermissions() method in the application delegate class to be used by all tables.

As for part II: navigation.

There are lots of tricks you can use to make the user experience better.
a. You can add your own custom menu with links to appropriate places in the app, that will be different for different users.

b. You can create custom breadcrumbs to display a path to the user’s current location so that it better reflects the heirarchy of the application.

c. You can hide certain parts of the application’s interface to certain users. E.g. you may want to hide the search or the status bar, or the view tabs.

d. You can override the view tab for a table so that it better reflects the information that you want the user to see, and provide navigational links.