Simple List Example

Archived from the Xataface Users forum.

awalk — Sat Jul 09, 2011 1:03 pm

I’ve had quick success getting Xataface setup to perform simple data entry against a new mysql database. But I’m having trouble understanding how to do many to many relationships without manually having to remember and enter numbers. While I’ve read the documentation, I must not be understanding it because my interpretation of the directions don’t work. And I’m frustrated that the downloadable examples don’t work for me either. So suppose I have the following

CREATE TABLE art
(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
artist_id
PRIMARY KEY (id)
) TYPE = INNODB;

and

create TABLE artist
(
id INT NOT NULL AUTO_INCREMENT,
lastname VARCHAR(30),
PRIMARY KEY(id)
) TYPE = INNODB;

I already have the application accepting artists names. But how to make the artist_id field populated from a pull down list of names from artist?

So it seems that I need to make some directories and files:

{webapp root}/tables/art/fields.ini
{webapp root}/tables/art/relationships.ini

In fields.ini, I can put things like

[title]
widget:label = “Title”
widget:description = “Enter the common name for this work.”

and that works. It seems that I should encode the relationship between the two tables in art/relationships.ini? or artist/relationship.ini using the __sql__ entry on the first line (not in any kind of a section). But what should this sql say? http://xataface.com/wiki/relationships.ini_file doesn’t give any kind of examples. The multimedia web application doesn’t appear to have any relationships.ini files.

Looking at the web auction files, it looks like I should say
[artist]
__sql__ = “select * from artist where art_id=’$art_id’”
in the tables/art/relationships.ini file

but this does nothing? Is this incorrect? Or is it incomplete? Clearly I am missing something. artist_id remains a text entry box instead of a list.

Thanks for any and all help.

shannah — Sun Jul 10, 2011 6:55 am

You need to either use the lookup widget on your artist_id field, or you need to create a valuelist for artists, and use the select widget on the artist_id field, setting the vocabulary directive to your valuelist.

i.e. read up on valuelists.

_Steve

awalk — Sun Jul 10, 2011 10:03 am

The way I see it there are 4 categories of data input: 1) text field 2) pick from a static list 3) pick from a list generated using 2 tables (1 to many relationship) 4) pick from a list generated from using 3 tables (many to many).

I had success getting the text field to work without doing any configuration. Now I have case 2) done and I’d like to summarize it for the next stuck newbie. Then I’ll describe what I’ve done on case 3 and hope that someone will see that is wrong or missing since 3) is not working yet.

I had two tables. Art lists paintings and has a foreign key relationship with Artist. When viewing the Art table, to get the name of the artist to show in the display list. I am using the following configuration:

{web application root}/tables/art/fields.ini

[artist_id]
widget:label=”Artist”
vocabulary = Artist

{web application root}/tables/art/valuelists.ini

[Artist]
1 = Manet
2 = Monet

This configuration doesn’t get me a list when I try to input a new record, but one problem at a time.

Now what I really want is to get to case 3), but looking at 2) the difference is getting the list from the database instead of from valuelists.ini. So i make

{web application root}/tables/art/relationships.ini

[artist]
__sql__ = “select * from artist where art_id=’$art_id’”

and I should remove vocabulary = Artist from fields.ini. But does it get replaced with something else? because what I’ve got now doesn’t work.

shannah — Sun Jul 10, 2011 12:48 pm

This case is covered in the getting started tutorial in the valuelists section.

Steve

awalk — Sun Jul 10, 2011 4:10 pm

Ok. So I saw it work and then I added entries into the database and then when I reloaded the page, I was back to looking at numbers. Very strange. Any advice?

silma — Mon Jul 11, 2011 1:43 am

(Sorry for the english…)
I’m not sure i understand everything you need, but here the thing i would do :

Value list from a static list :

{web application root}/tables/art/fields.ini

Code: Select all: [artist_id] widget:label="Artist" widget:type = select vocabulary = Artist

{web application root}/tables/art/valuelists.ini

Code: Select all: [Artist] 1 = Manet 2 = Monet

pick from a list generated from a table

{web application root}/tables/art/fields.ini

Code: Select all: [artist_id] widget:label="Artist" widget:type = select vocabulary = Artist

{web application root}/tables/art/valuelists.ini

Code: Select all: [Artist] __sql__="select id, lastname from artist order by lastname"

Relationship :

By the way, this cant work :

awalk wrote:[artist]
__sql__ = “select * from artist where art_id=’$art_id’”

There’s no “art_id” in your “artist” table.

awalk — Mon Jul 11, 2011 9:16 pm

Thanks for trying to help me out! I’m still stuck but … in fields.ini I have
[artist_id]
widget:label=”Artist”
widget:type=select
vocabulary=Artist

adding the widget:type=select makes the field a pull down list when you go to add a new record. But still there are no rows showing.

I tried
[Artist]
__sql__=”select id, lastname from artist order by lastname”
in valuelists.ini
but that didn’t make any difference … and I thought the sql went into relationships.ini so I tried that too but neither one helped. I’m beginning to think something else is wrong and php is bombing out silently. As for art_id, that’s the primary key of the art_table. I keep wanting to write something that looks like a join statement. Clearly I don’t understand how the framework is structured.

What tells xataface that you want a static list versus one from the database? Is it just whether you use __sql__ versus defining your own list?

And thanks again for your assistence.

shannah — Tue Jul 12, 2011 12:48 am

Please read the getting started tutorial up to and including the section on value lists. All of your questions are covered there.

Steve