homepage Welcome to WebmasterWorld Guest from 184.72.91.94
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Pubcon Website
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library : Charter : Moderators: physics & whoisgregg

Databases Forum

    
mySQL newbie(1 day) concatenate across result rows
Update a field from multiple rows in another table
CDNQuilter




msg:3803894		 1:28 am on Dec 10, 2008 (gmt 0)

Inventory of storage units.
One owner can own all or part of one or more units.
Therefore, a unit can have one or more owners.

Currently, the owners file contains a list of units associated with that owner.
I need to update the units file with the owner_key and names of all owners.
So far, I have:

SELECT unit_no, units_owned,
TRIM( ',' FROM CONCAT_WS( ",",lastname,firstname,org_name)) AS Owner_Names
FROM units, owners
WHERE INSTR( units.unit_no, owners.units_owned )>0 0 LIMIT 0 , 300

gives me:

unit_no units_owned Owner_Names
100 100 Smith,John
200 200 Doe,John
200 200 Black,Jane
300 300 XYX Corporation

See that unit_no 200 is owned by both Doe,John and Black,Jane

I need to update units.owners with Doe,John;Black,Jane where units.unit_no=200

This seems very basic. I cannot find any examples, though. I need a WHILE but the WHILE examples I can find don't show how to incorporate this into a result set or an update
Thanks

 

phranque




msg:3803915		 1:58 am on Dec 10, 2008 (gmt 0)

what you really want is a separate table that associates owners and units.
you have a many-to-many relationship there that will be difficult to manage using a list column in both tables.
CDNQuilter




msg:3803951		 3:01 am on Dec 10, 2008 (gmt 0)

Well, right now they have an input form that allows them to select multiple unit numbers to put in the owners.units_owned field.

They also have an input form for the units table that allows them to put whatever they like in the units.owners field but, of course, they ended up out of sync so they gave up on it. Now they want the system to update this for them.
I guess I will just do the query as above in php and loop through the results to get the string of values and then create an update transaction.
Somehow, I thought that this should be do-able in SQL but SQL seems to require a different way of thinking than I am used to.

Thanks, anyway

CDNQuilter




msg:3804031		 6:52 am on Dec 10, 2008 (gmt 0)

I wrote a php routine that did the update.
If they keep the units_owned in the owners file accurate and run the routine I wrote regularly, it should be OK for now.
The system is a bit of a dog's breakfast in a few areas.
I will add your suggestion to their list of to-do's for when they reorganize the system.
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved