Xataface Forums

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.

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

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.

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

Steve

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?

(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.

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.

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

Steve