DFParker
Posts: 4
|
| Posted: 04/01/2004, 10:38 AM |
|
CC Newbie (Day 2 ) here with a neat solution I puzzled out.
My goal was to update across a join so that I have a list of text values and a checkbox list or drop down list of corresponding info (basically equivalent to a foreign key lookup between a normalized column value and the text equivalent for the dependent list boxes).
Text Equiv Form Goal
Values List ||Checkboxes
Text1 X
Text2
... X | null ...
1) New Page, add editable grid
2) Enter connection src and sql/build sql with joins
3) Add List Box or Checkbox list
4) Enter connection src and sql/build sql (simple select on corresponding values from step (2), i.e., no join). Alias the identity column to the name of the corresponding column in the join sql (step 2).
5) set the Default value, etc
Automagically, the lookup from the join sql is related to the unique id of the list/check box value.
This beats hand coding PHP/SQL! - kudos to CodeCharge team & tool.
Environment:
MySQL 4.0.18
CC 2.2.3
PHP 4.2.3
This is not as complicated as the Many-to-Many Checkbox example but pretty hard for a newbie to figure out and I didn't see any example to help me out when I started.
Hope this helps others.
David
_________________
----
Dave Parker
firstcoastresearch/Y!
dfparker_SPAM-Choke_removeThis2EmailMe@ecoastresearch.com |
 |
 |
peterr
Posts: 5971
|
| Posted: 04/01/2004, 2:05 PM |
|
The SQL doesn't support updating multiple tables (using joins in updates) therefore this simply cannot be done. You should not use joins in Editable Grid unless you use Custom Insert and Custom Update, but even then you can update only one table.
All additional tables would need to be updated via custom event code like in our checkbox example that you mentioned.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
DFParker
Posts: 4
|
| Posted: 04/01/2004, 2:45 PM |
|
Right are. 
I am at a loss why this seemed to work (update/delete/add) for a time and now fails.
Anyway, would you describe or point out examples or resources that accomplishes my aim (simple editable grid with a lookup description column)?
David
_________________
----
Dave Parker
firstcoastresearch/Y!
dfparker_SPAM-Choke_removeThis2EmailMe@ecoastresearch.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 04/01/2004, 3:56 PM |
|
Actually, I've dealt with updating multiple tables via joins with some databases (either SQL Server or MS Access) but I thought that MySQL doesn't support this. If this worked for you then possibly it can work with MySQL. I'll have to check into this.
Still, there are just too many cases where such query may not be updateable. Joining tables for SELECT is one thing but it may not always work with UPDATE. And when using many-to-many checkboxes (chexkbox list) the issue definitely becomes more complex.
Generally everyone's database and SQL statements are different and I probably cannot offer a good advice since each case/project is different.
I would recommend that you start with the simplest design (for example the simplest join that works for you) then add the checkbox list or other features and find out when and what stops working.
If there are error messages then they will probably describe what the problem is. If the database is not updated with some information then possibly you may need to add manual code - almost certainly for the multiple checkboxes.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
DFParker
Posts: 4
|
| Posted: 04/01/2004, 4:17 PM |
|
The thing is, updates on joins are not supposed to work.
As I play with this solution, I realize that it is not a true join. Rather, the PHP is rendering and conflating a key from one SELECT with another SELECT. It's a join but not in the usual (SQL) sense rather, in via code.
Chalk it up to serendipity b/c it is not ez to reproduce.
David
_________________
----
Dave Parker
firstcoastresearch/Y!
dfparker_SPAM-Choke_removeThis2EmailMe@ecoastresearch.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 04/01/2004, 4:26 PM |
|
In MS Access it is possible to create a saved Query that then is treated as a Table and can be used just like a Table. Many times such queries can be updated, but not always. Some queris are simply structured in such way that are not updateable.
I just checked MySQL docs at http://www.mysql.com/doc/en/UPDATE.html and they read:
=================================
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.
Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.
Before MySQL 4.0.18, you needed the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.
=================================
Therefore looks like this can work only starting with MySQL 4.0.4.
Well, in any case we're coming back to the question of specific implementation. Possibly with little testing you can find the exact situation where you page stops working and then describe that specific problem area.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
