MySQL combine & append table question

I have a table that I would like to combine into another table.  The `properties` table that will contain the new values has an iEntityId field along with newly created and NULL columns like so:
`iEntityId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`feature_short` varchar(50) DEFAULT NULL COMMENT 'Feature List (Short)',
`features` text COMMENT 'Feature List (Long)'

Open in new window


The table that has the values I want to move into the table above looks like so:
CREATE TABLE IF NOT EXISTS `tACB_EntityInfo` (
  `iEntityId` int(11) NOT NULL,
  `iInfoIndex` int(11) NOT NULL,
  `sInfoLongDescription` varchar(1024) DEFAULT NULL,
  `sInfoShortDescription` varchar(50) NOT NULL,
  `iSortOrder` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`iEntityId`,`iInfoIndex`)
) ENGINE=InnoDB;

Open in new window


The values from sInfoLongDescription from tACB_EntityInfo I would like appended to `features` column in the `properties` table and wrapped inside an <li></li> tag.  Then I need to add <ul> tag to the beginning and </ul> tag to the end of the `features` column if it is not NULL.

Then the  values from sInfoShortDescription from tACB_EntityInfo I would like appended to `short_features` column in the `properties` table followed by a comma.  Then I need to remove the last comma from the `short_features` column if it is not NULL.

The iSortOrder column from tACB_EntityInfo defines how the data should be sorted.

Here is what I have so far, it works, except only for the first entry.  I don't know if it is possible to add the additional tag and remove the comma here or if that needs to be a separate query.
UPDATE `properties` AS target
LEFT OUTER JOIN (
		SELECT iEntityId, sInfoShortDescription, sInfoLongDescription
		FROM tACB_EntityInfo
		ORDER BY iEntityId, iSortOrder
	) AS source
	ON target.`iEntityId`= source.`iEntityId`
SET
target.`feature_short` = IFNULL(CONCAT(target.`feature_short`, source.sInfoShortDescription, ','), CONCAT(source.sInfoShortDescription, ',')),
target.`features`      = IFNULL(CONCAT(target.`features`, '<li>', source.sInfoLongDescription, '</li>'), CONCAT('<li>', source.sInfoLongDescription, '</li>'))

Open in new window


Here is some sample data from the tACB_EntityInfo table:
INSERT INTO `tACB_EntityInfo` (`iEntityId`, `iInfoIndex`, `sInfoLongDescription`, `sInfoShortDescription`, `iSortOrder`) VALUES
(2, 1, 'throughout kitchen and dining room.', 'Distressed Maple Flooring', 0),
(2, 2, 'Stained Maple in kitchen.', 'Cabinetry', 0),
(2, 3, 'with Stained maple doors.', 'Custom Painted Trim', 0),
(2, 4, '3 bedrooms on main level and 1 bedroom on the lower level.', '4 Bedrooms', 0),
(2, 5, '2 Baths on the main level including custom tile shower in master bath with body spray system and 1 bath on the lower level. ', '3 Bathrooms', 0),
(2, 6, '1700 finished sq.ft.', 'Main Floor', 0),
(2, 7, '1400 finished sq.ft.', 'Lower Level', 0),
(2, 8, '92% Lennox furnace.', 'Heating System', 0),
(2, 9, '13 seer Lennox central air conditioner.', 'Cooling System', 0),
(2, 10, 'Five-Star energy efficient home with 2x6 exterior walls.', 'Energy Efficiency', 0),
(2, 11, 'with pan vac off of the woodfloor.', 'Central Vacuum System', 0),
(2, 12, 'with Home Audio System.', 'Structured Wiring', 0),
(2, 13, 'with stone accents in basement.', 'Wet Bar ', 0),
(2, 14, 'kitchen and bath faucets and hardware.', 'Oil Rubbed Bronze', 0),

Open in new window


Thanks in advance!
LVL 1
bdhtechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Hi, I think yo will find GROUP_CONCAT() to be immensely useful here:
select
       iEntityId
     , concat('<ul>' , group_concat(concat('<li>',sInfoLongDescription,'</li>') ORDER BY iInfoIndex) , '</ul>')
     , group_concat(sInfoShortDescription ORDER BY iInfoIndex SEPARATOR ',')
from tACB_EntityInfo
group by
       iEntityId

Open in new window

0
PortletPaulfreelancerCommented:
You may have to extend the length of feature_short (50 wasn't long enough for the following) - or arbitrarily cut-off the concatenated string.
UPDATE `properties` AS target
LEFT OUTER JOIN (
        SELECT
               iEntityId
             , concat('<ul>' , group_concat(concat('<li>',sInfoLongDescription,'</li>') ORDER BY iInfoIndex) , '</ul>') as sInfoLongDescription
             , group_concat(sInfoShortDescription ORDER BY iInfoIndex SEPARATOR ',') as sInfoShortDescription
        FROM tACB_EntityInfo
        GROUP BY
               iEntityId
	) AS source
	ON target.`iEntityId`= source.`iEntityId`
SET
     target.`feature_short` = source.sInfoShortDescription
   , target.`features`      = source.sInfoLongDescription
;

Open in new window

see: http://sqlfiddle.com/#!2/01945/2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
by the way, embedding html markup into data, then forming pages from that, opens up the possibility of malicious javascript etc.

are you certain you want to go down that road?
0
bdhtechnologyAuthor Commented:
Thanks again that worked great with one minor correction, I had to add SEPARATOR ' ' to the li code so that it presents valid HTML.
concat('<ul>', group_concat(concat('<li>', sInfoLongDescription, '</li>') ORDER BY iInfoIndex SEPARATOR ' '), '</ul>') as sInfoLongDescription

Open in new window


I do understand the risk of embedding HTML code into the database.  They want to be able to edit the data using ckeditor control so I am trying to make that easy for them to do.  The database is full of HTML code already, I am just cleaning up the UI so it's easier to use.
0
PortletPaulfreelancerCommented:
:)  I had to add SEPARATOR ' ' to the li code so that it presents valid HTML.

(can I claim it as the "deliberate error" ?)
excellent, well done, you now know the advantages of group_concat

Thanks for the grading, and, again, good luck with this endeavour. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.