Use of support tables with <SELECT> tags

Hi!

first of all, congratulations for this GREAT class! I've been waiting for it for a long time! Really confortable and easy to update mysql tables without any hassle to create several HTML pages with forms.

But there is a drawback: I think this class would improve significantly if it had the ability to handle support tables for fields which have definite value using <SELECT> tags instead of <INPUT type="text"...> tags. Let me illustrate what I mean:

Let's say I have a table called "clients" wich has a mysql INT field called "CityId", which stores the id of the city. And I have another table called "citties", which has 2 columns: "id" of the city and a CHAR field "CityName" with the name of the city.

Well, to insert data in the "clients" table, it would be perfect if in the column related to the field "city" I had a <SELECT> tag with the option values being the "id" and the enclaused text between the option tags would be the city name.

Maybe when creating the Object, this could be an option:

$clients = new mysqlphpgrid("<hostname>", "<username>", "<password>", "<databasename>", "<tablename>");

echo $clients->ShowTable(<editable:true>, <paginationlength (optional)>, <column-headers (optional)>);

Well, the "<column-headers>" part of the ShowTable function could be optional. If not set, all column will be displayed as the real names of the table. If not, it will be possible to choose which fields will be shown and also which are related to other support tables. In our example, it could be something like:

echo $clients->ShowTable(true, 20, "{ClientName|Client}{CityId|City where lives the client|city:id:CityName}");

I'm just creating a coding for the <column-headers> option of the ShowTable function: each field name and label would be enclosed by brackets and seperated by '|' character.

But if the field is related to another table, then we add another '|' character and put, seperated by ':', the following info: <tablename:option-value:option-enclosed-text>, which tells the function to add in this column a <SELECT> tag with options like that (in our above example):
<SELECT name="<CityId>">
<OPTION value="<id>"><CityName></OPTION> iterated through all rows in table "city";
</SELECT>

What do you think?

cheers,

daniel tygel, from brasil

Hi daniel,

Interesting thought. I can understand the wish to apply the grid to a number of cascading tables and it would indeed be a nice feature to add. However, I don't know yet how to add this in such a way that use of the grid is as simple in use as it is now. Adding optional arguments with a syntax as you describe, is possible, but is not the first solution that comes in my mind.
Just as the grid right now discovers which columns are primary fields, it would be better if it automoatically discovers these inter-table relationships on the fly. I don't know if this is possible, but will have a look at it in the future.

thanks, Olav

Hi Olav,

Thanks for the quick answer :^)

I see that you'd like to keep functions simple to the class' user. But I think that it's only possible to retrieve automatically table relationships when they are run by InnoDB Engine and use foreign keys, which allows for relational constructs.

But what if someone wants his tables using the MyIsam engine? (like me :^) )

If we include an optional third argument, it means that the class user can choose if he(she) wants to use or not this feature: if he(she) doesn't use this argument, all fields in the table will be retrieved automatically and converted to columns in the grid, with the headings being exactly the names of the fields. But if the user wants to decide which fields are visible (and so editable) for the final user who uses the grid, then he(she) could use this third argument. And then he(she) could go further, telling the function that a specific column is related to a support table.

Do you think this turns the script too complicated?

daniel

Hi daniel,

I just released a new version of MySQLPHPGrid, where I added support for ENUM fields via drop-down lists. The type of the field is automatically detected from the MySQL database, and for these fields, a <select> form element is used.
In this way the source for the grid, if used in edit mode, is still within one table. If I would extend the functionality of the grid to support multiple tables, it would make a few things more complicated. However, I may add such kind of functionality in future releases.

regards, Olav