|
Viewing Bug Advanced Details |
[ Jump to Bugnotes ]
[ Bug History ]
[ Print ]
|
|
ID |
Category |
Severity |
Reproducibility |
Date Submitted |
Last Update |
|
0000220 |
core / field option |
feature |
always |
2004-04-10 22:47 |
2004-11-07 20:34 |
|
|
|
Reporter |
brycen |
View Status |
public |
|
|
Assigned To |
hbernard |
|
Priority |
normal |
Resolution |
open |
Platform |
|
|
Status |
assigned |
Duplicate ID |
|
OS |
|
|
Projection |
none |
|
Version |
|
|
ETA |
none |
|
Product Version |
|
|
|
Product Build |
|
|
|
Votes |
1 |
|
|
|
Summary |
Editing JOIN tables |
|
Description |
I'd very much like to be able to edit joined tables. For example, if I have the following two tables:
mysql> select * from stuff;
+----+--------+----------------------------+
| id | loc_id | description |
+----+--------+----------------------------+
| 1 | 1003 | Colored boxes |
| 2 | 1003 | Office supplies |
| 3 | 1004 | Junk! |
| 4 | 1003 | Beanie Babies |
+----+--------+----------------------------+
4 rows in set (0.00 sec)
mysql> select * from shelves;
+--------+----------------------+
| loc_id | shelf_name |
+--------+----------------------+
| 1003 | Top shelf in hallway |
| 1004 | Rack in basement |
+--------+----------------------+
2 rows in set (0.00 sec)
I'd like to edit them all at once, on a single phpMyEdit page:
mysql> select id,stuff.loc_id,description,shelf_name from stuff,shelves WHERE stuff.loc_id=shelves.loc_id;
+----+--------+-----------------+----------------------+
| id | loc_id | description | shelf_name |
+----+--------+-----------------+----------------------+
| 1 | 1003 | Colored boxes | Top shelf in hallway |
| 2 | 1003 | Office supplies | Top shelf in hallway |
| 4 | 1003 | Beanie Babies | Top shelf in hallway |
| 3 | 1004 | Junk! | Rack in basement |
+----+--------+-----------------+----------------------+
4 rows in set (0.00 sec)
|
|
Steps To Reproduce |
|
|
Additional Information / Solution |
Can phpMyEdit edit such tables? A year or so ago I tried to add this to the prior version of phpMyEdit. I recall getting close, and submitting patches. Is this now possible? |
|
Attached Files |
stupidsample.sql (1,623 bytes) 2004-04-20 22:48
joinupdate.TUA.php (247 bytes) 2004-04-21 10:17
stuff.php (5,539 bytes) 2004-04-21 10:18 |
|
|
|
Bug Notes |
ajh
2004-04-11 08:26
|
You certainly can't edit joined tables like this (and there's an argument that it could be dangerous to do so). However I've just posted an extension (see bug report 218) which allows you to include fields from joined tables read only. |
|
|
nepto
2004-04-11 18:35
|
I cannot imagine how fields should be updated in JOINed table(s).
Basically something like this is quite impossible to implement.
phpMyEdit won't know what to update if value for "shelf_name" in your example has changed.
edited on: 2004-04-11 18:37 |
|
|
brycen
2004-04-11 20:53
|
As of mySQL 4.0.4 you can update such a join with something like this:
UPDATE stuff,shelves SET shelves.shelf_name='Green shelf in hallway' WHERE stuff.loc_id=shelves.loc_id;
you see how readily that follows from:
SELECT stuff.id,stuff.loc_id,stuff.description,shelves.shelf_name FROM stuff,shelves WHERE stuff.loc_id=shelves.loc_id;
See http://dev.mysql.com/doc/mysql/en/UPDATE.html |
|
|
brycen
2004-04-19 20:18
|
See
http://platon.sk/projects/bug_view_advanced_page.php?f_bug_id=218
for a potential partial solution. |
|
|
hbernard
2004-04-19 21:11
|
Well #218 is already implemented in PME for the join part, this is just a matter of API for the rest.
However new API (see #223) could handle this.
About this bug :
- As said on forum, I'm not sure this is a good thing (tm) (especially with current Mysql)
- I suggest you wait while #218 a #223 are not resolved.
- main work will be in do_change_record
- But, Insert is another thing, currently not easily doable, I think.
- You will have to deal with duplicate, maybe
Well, these were some thoughts on the fly ;-) |
|
|
brycen
2004-04-19 21:16
|
I'm sure it is a "good thing (tm)" because otherwise I can't use phpMyEdit for my project. Many, if not most, database schemas involve joins. Duplicates are fine, that's what the JOIN statement is all about. |
|
|
hbernard
2004-04-20 18:23
|
Oh, I didn't know that there were joins in many database schemas ;-) !
BTW : your update sql sample seems to be wrong : where is stuff.id defined. It seems to me that your sample will update shelves.shelf_name='Green shelf in hallway' for all values of the joined the table, if defined in main table.
What I m trying to says in my bad and poor english, is :
- yes, it is possible to make such update, however, I have needed in the past such feature only for m-n bindings, not really for basic joins.
- About the 'good thing', what I wanted to say is that joins are mainly used for restricting user choice and avoiding to have free and not normalized CHAR fields in database. If user can change directly the joined value, my personal experience says that it will be a mess.
- for example, what happen if you set for loc_id= 1004 the same shelf_name as for 1003 ? Or if you set it to blank string ?
Again, I'm not saying that it is not useful nor possible. What is your real use case ? |
|
|
brycen
2004-04-20 20:16
|
Joins are used very often, any time you might have multiple items attached to a record. For example:
create table names
(
id int auto_increment not null,
first_name varchar(150),
last_name charchar(150),
primary key (id)
);
create table email
(
id int not null,
email varchar(150),
primary key (id)
);
Now you can have more than one email address per record. You can search by email, or by name. If you search for an email, and update the name, you update all the joined records.
>BTW : your update sql sample seems to be wrong : where is stuff.id defined. It >seems to me that your sample will update shelves.shelf_name='Green shelf in >hallway' for all values of the joined the table, if defined in main table.
That's exactly what I want to happen. If the user edits fields from the item database it changes the item database. If they edit fields from the shelf database, it changes the shelf database.
My real use case is very similar to this. We have a complicated database to define each "place". Then we have another complicated database to define each "object". Objects can then be assigned to places. This only works well with joins. |
|
|
hbernard
2004-04-20 22:57
|
I'm sorry to see that you persist to think I need an SQL manual.
(maybe my joke in my previous post was not obvious, and I shouldn't try to be ironic in English.)
I'm sorry to see that you even don't read my message and don't believe me when I say your UPDATE statement posted before is wrong, or, at least, silly.
Maybe I need to say your query should be :
UPDATE stuff,shelves SET shelves.shelf_name='Green shelf in hallway'
WHERE stuff.loc_id=shelves.loc_id
AND id = 1;
(please, note the 'AND', whatever the 'id' is)
Else, you will set all the records in shelves that are joined against stuff (whatever the key value they are joined with) to 'Green shelf in hallway'
(so, here, all the shelves, because all the shelves have a corresponding stuff)
Maybe I need to say you just have to test the uploaded sql file to see that I'm right.
That said, I fully understand your need, and I agree this would be fine, not really a must have, but that this would be fine.
But, as said, other issues already listed must be fixed before.
Now, can you explain me how, when you will add a new stuff, will you make the join ? Will you, only for inserting have a dropdown for shelves ? Will you test if the NAME of the shelf doesn't exist, and then create one ? Is your current request only related with updating, or did you already have some thought about inserting on joined table ? These are concrete questions.
If you still think I don't understand anything, please upload a real not hardcoded patch (not like the one year before one).
Sorry, I m a little angry to have someone explaining to me that there are joins in database. |
|
|
brycen
2004-04-21 00:02
|
You are right, my SQL was bad, I did not test it. You seemed to be saying that the idea of the update statement was wrong, I did not understand that you simply meant it was incorrectly written.
My current request is related only to updating. That has some value.
Inserting is harder, but possible. The user might select a row in the joined table, and say "add stuff to the same shelf". They would insert only into the stuff database. Or they could "Add", and have a dropdown for which shelf to join to (provided the number of "shelves" is small). |
|
|
brycen
2004-04-21 02:08
|
My real use case is a bus stop database, with bus stops, and a list of which routes service those stops. The field data entry personel need to constantly shift between the two databases, updating both. |
|
|
hbernard
2004-04-21 10:17
|
After a good night sleeping, I finally think this is doable using current API.
Only PME class fix is to allow Virtual field not to be Readonly, so change readonly function in line 74 to :
function readonly($k) { return stristr($this->fdd[$k]['input'],'R');}
Then, you may use :
- stuff/shelves database schemas and data (uploaded yesterday)
- stuff.php (just uploaded)
- joinupdate.TUA.php (just uploaded)
and make some test, but I think this is what you want.
Can you confirm this ? |
|
|
brycen
2004-04-22 19:27
|
Wow! Amazing use of the current API.
Unfortunately I won't be able to test this right now, as I realize my target installation is restricted to mySQL 3.52. Right now I'm using the bug #218 approach to get most of what I wanted. |
|
|
pilavdzic
2004-11-06 06:22
|
I am now trying to do something very much like this as well. I have 3 tables, need to join them and be able to view and update. Adding, and deleting and other stuff like that I can do manually outside of phpmyedit if I have to. I changed the readonly function as you suggested above, but it didnt' seemt o work, when I go to the edit mode the fields from the other table don't show up at all.
This may be because I don't understand what you mean by those files like stuff.php and joinupdate.TUA.php.. What does TUA stand for? I'm trying to learn the more advanced stuff and I am slowly progressing. Could you help me out a little further? If I can get this working like you envisioned I will test it and let you know how well it works.
I know expanding support of phpMyEdit to handle this kind of stuff is not a small thing, but I think there is a lot of need for this if it can be done somehow. If some have managed to achieved this through a mod to the class file, I would like to as well.
Are there any other relevant threads I should read? I am using what you taught me in bug #272 to join the tables. Thanks.
edited on: 2004-11-06 07:10 |
|
|
hbernard
2004-11-07 20:34
|
Hello,
TUA mean :
Trigger Update After.
There is a dedicated trigger section in the documentation.
Have you tried the files that have been uploaded in this bug report (see above the bugnotes thread) ? |
|
|
|
Bug History |
|
Date Modified |
Username |
Field |
Change |
|
2004-04-10 22:47 |
brycen |
New Bug |
|
|
2004-04-10 22:49 |
brycen |
Bug Monitored: brycen |
|
|
2004-04-11 08:26 |
ajh |
Bugnote Added: 0000887 |
|
|
2004-04-11 18:35 |
nepto |
status |
new => feedback |
|
2004-04-11 18:35 |
nepto |
Bugnote Added: 0000891 |
|
|
2004-04-11 18:37 |
nepto |
Bugnote Edited: 891 |
|
|
2004-04-11 19:22 |
hbernard |
Bug Monitored: hbernard |
|
|
2004-04-11 20:53 |
brycen |
Bugnote Added: 0000896 |
|
|
2004-04-19 20:18 |
brycen |
Bugnote Added: 0001004 |
|
|
2004-04-19 21:11 |
hbernard |
Bugnote Added: 0001007 |
|
|
2004-04-19 21:16 |
brycen |
Bugnote Added: 0001008 |
|
|
2004-04-20 18:23 |
hbernard |
Bugnote Added: 0001037 |
|
|
2004-04-20 20:16 |
brycen |
Bugnote Added: 0001042 |
|
|
2004-04-20 22:48 |
hbernard |
File Added: stupidsample.sql |
|
|
2004-04-20 22:57 |
hbernard |
Bugnote Added: 0001043 |
|
|
2004-04-21 00:02 |
brycen |
Bugnote Added: 0001044 |
|
|
2004-04-21 02:08 |
brycen |
Bugnote Added: 0001045 |
|
|
2004-04-21 10:17 |
hbernard |
Bugnote Added: 0001046 |
|
|
2004-04-21 10:17 |
hbernard |
File Added: joinupdate.TUA.php |
|
|
2004-04-21 10:18 |
hbernard |
File Added: stuff.php |
|
|
2004-04-21 10:18 |
hbernard |
handler_id |
=> hbernard |
|
2004-04-21 10:18 |
hbernard |
status |
feedback => assigned |
|
2004-04-21 12:38 |
hbernard |
File Added: configuration.general.opprefix.sgml.diff |
|
|
2004-04-21 12:38 |
hbernard |
File Deleted: configuration.general.opprefix.sgml.diff |
|
|
2004-04-22 19:27 |
brycen |
Bugnote Added: 0001091 |
|
|
2004-11-06 06:22 |
pilavdzic |
Bugnote Added: 0001222 |
|
|
2004-11-06 07:10 |
pilavdzic |
Bugnote Edited: 1222 |
|
|
2004-11-07 20:34 |
hbernard |
Bugnote Added: 0001225 |
|
|
|
Login
Registration
Slovak
