Home » Forums » General Discussion

Form list from multiple tables

30 Sep 2008 eertl

Hi everyone. I'm pretty new to zoop, and I wanted to know if someone knows how to create a list from multiple tables or a specific sql query, for example to show in the same table data from table users, which is related to the table user_group, which is related to the table groups.

The forms2 component accepts only one table name, and the forms component doesn't look to do that job either. Can anyone help me, please? At least with a little code sample.

Thanks in advance.

‹ How to fix Cache bug? Zoop 1.5 Released! ›

What is the data?

Could you elaborate on the information you are trying to display and how you would like it displayed? How the data relates in the database or the query you are using to get your results will help to better answer your question. Also do you want to display the data as a table, a ul, or ol list?

A simple display will most likely be handled best using Smarty functions in a .tpl file after you get the data, but I will elaborate once I know more.

jmorant@cloud9l... 30 Sep 2008

Elaborating

Let's use another example.

Assuming the following tables (not a real world example, but done so to keep the simplicity):

-----------------          -----------------
|    TABLE_A    |          |    TABLE_B    |
-----------------          -----------------
|table_a_id     | <--¬     |table_b_id     |
|name           |     -->  |table_a_id     |
-----------------          |description    |
                           -----------------

I want to display all the information for every record in TABLE_A and the related info from TABLE_B in an HTML table, and the query would be something like this:

SELECT ta.table_a_id, ta.name, tb.description 
FROM TABLE_A ta, TABLE_B tb 
WHERE ta.table_a_id = tb.table_a_id

So, the table would display like this:

-----------------------------------
| Id |   name   |   description   |
-----------------------------------
|  1 | John Doe | A common guy    |
-----------------------------------
|  2 | Jane Doe | A hot chick     |
-----------------------------------

Thanks

Eric

eertl 01 Oct 2008

Can't list many to one relationships, only one to one.

Your example doesn't work properly in Forms (though it probably will in the Zoop 2.0 release).

The issue is that the foreign key is in Table B, but your listing Table A, resulting in a one to many relationship.

How should Forms know what to do when there are many entries for A1 in Table B?

Forms does well with foreign keys in Table A and your listing table A, or One to One relationships.

Using your example, if A would have table_a_id, table_b_id, name you would use the method setFieldIndexTable . This method sets up the relationships.

----------------------------------
Co-Author of Zoop Framework
http://spf13.com

steve.francia 02 Oct 2008

What If?

What if you used a SQL View for your query then used the View name as the table for your form? Once the form is setup properly with the View data the post function for your Edit Page will have to be customized to do a proper update of the two tables, but that should be all the extra work required.

This is just theory because I have never tried to use forms with a View, but a Select on a View returns the same data as a regular Select which would allow the form to setup properly. Since you can't update a View you will need the custom update function which updates the fields in the tables separately.

CREATE VIEW table_a_b AS 
SELECT ta.table_a_id, ta.name, tb.description 
FROM TABLE_A ta, TABLE_B tb 
WHERE ta.table_a_id = tb.table_a_id

jmorant@cloud9l... 02 Oct 2008

Thanks

Thank you guys for your help, I'll try with the view.

Eric

eertl 08 Oct 2008