storing and retrieving bulleted list items

not sure if this is even a possibilty, but i figured i'd ask anyway. I have a project status database...one of the "desired" fields is Next Steps. The customer wants a short bullet list of Next step/milestone items to appear this column. From the data capture standpoint i was thinking of breaking it out into 4 or 5 text input lines, storing it as bullet1, bullet2, bullet3, etc. fields in the db. When it comes time to retrieve the data i would just add a <li> to each item returned. What im wondering is, without embedding any kind of ActiveX widget to handle the bullets on the input side, is there a way to append a <li> or #8352; tag to each line entered...or whenever a line begins with "-" add a <li>. any help /suggestions is/are appreciated...thx
nriddockAsked:
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.

Richard QuadlingSenior Software DeveloperCommented:
Use a single memo with soft wrapping to get the list from the user.
Store this in the DB with the usual addslashes/htmlentities/etc as you would normally store data.
When you come to render the data ...

$sOutput = '<ul><li>' . str_replace("\n", '</li><li>', $row['Bullets']) . '</li></ul>';

Richard.
0
crimson117Commented:
I would store each Next Step as a row in a table, with a column for the project_id, and a column for the order in which they should appear.  Then, to determine the list of next steps, do "SELECT NextStep FROM NextStepTable WHERE project_id = $theprojectid ORDER BY order_col".  Now output "<ul>", then parse over the results adding <li> before and </li> after each result, then adding a </ul> at the end.

This will make it easier for the user to manage in the future, and easier for you to add features later such as re-ordering, marking items "completed" , etc.
0
Richard QuadlingSenior Software DeveloperCommented:
Yes. Normalize the data. This does make it better for the future.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

nriddockAuthor Commented:
my current version of your suggestion looks like this:

CREATE TABLE `pdb_steps` (
  `stepsID` smallint(2) NOT NULL auto_increment,
  `steps` varchar(255) NOT NULL default '',
  `projectID` smallint(4) NOT NULL default '0',
  PRIMARY KEY  (`stepsID`)
) TYPE=MyISAM AUTO_INCREMENT=5

---------

do i need to modify it to look like this
CREATE TABLE `pdb_steps` (
  `stepsID` smallint(2) NOT NULL auto_increment,
  `steps0` varchar(255) NOT NULL default '',
  `steps1` varchar(255) NOT NULL default '',
  `steps2` varchar(255) NOT NULL default '',
  `steps3` varchar(255) NOT NULL default '',
  `projectID` smallint(4) NOT NULL default '0',
  PRIMARY KEY  (`stepsID`)
) TYPE=MyISAM AUTO_INCREMENT=5

0
Richard QuadlingSenior Software DeveloperCommented:
Fully normalising would result in a steps table consisting of ...

CREATE TABLE `pdb_steps` (
  `stepsID` smallint(2) NOT NULL auto_increment,
  `projectID` smallint(4) NOT NULL default '0',
  `steps` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`stepsID`)
) TYPE=MyISAM

You would split the inputted memo field on \r\n or \r or \n (I do all three as I never know what client I am going to be talking too).

This would give you the lines/steps.

Save each one with a step position.

This will allow you to move steps up and down. ( I do this for an app where you need to have things done in sequence).

Richard
0
crimson117Commented:
CREATE TABLE `pdb_steps` (
  `stepsID` smallint(2) NOT NULL auto_increment,
  `projectID` smallint(4) NOT NULL default '0',
  `steps` varchar(255) NOT NULL default '',
  `order` smallint(4) NOT NULL default '0'
  PRIMARY KEY  (`stepsID`)
) TYPE=MyISAM


Add an order column so you know the order of the steps.

You don't want "step1, step2, step3, step4" columns.  What if there's a fifth step? sixth? tenth? You'll want one row for each step, with the `order` column set to 1, 2, 3, to specify the first, second, third steps, and so forth.
0
Richard QuadlingSenior Software DeveloperCommented:
A technique I use to keep the steps in order is to always update them every time you make a change.

You would be filtering on projectID.

Especially if you delete one.

The way I do this is ...

1 - Set an internal count to 0.
2 - SELECT * FROM table WHERE projectID=(your project id) ORDER BY order ASC
3 - Read one row at a time and...

UPDATE table SET order= (internalcounter) WHERE stepsID=(stepsID from the read)

4 - In side the loop, increment the internal counter.
5 - Goto Step 3 until all rows processed.

This will deal with deleting rows.

Moving rows up and down is fun too.

      protected function KPIFormMaintElementTypes_MoveUp()
            {
            if ($this->bInit)
                  {
                  return $this->KPIFormMaintElementTypes_SwapSequence('<=', 'DESC', 'Up');
                  }
            }

      protected function KPIFormMaintElementTypes_MoveDown()
            {
            if ($this->bInit)
                  {
                  return $this->KPIFormMaintElementTypes_SwapSequence('>=', 'ASC','Down');
                  }
            }

      private function KPIFormMaintElementTypes_SwapSequence($sWhereFilter, $sOrderBy, $sDirection)
            {
            $sSQL = 'SELECT TOP 2 UniqueID, ElementTypeExecutionSequence FROM kpi_element_types WHERE ElementTypeExecutionSequence ' . $sWhereFilter . ' (SELECT ElementTypeExecutionSequence FROM kpi_element_types WHERE UniqueID = ' . $this->iFormActionID . ') ORDER BY ElementTypeExecutionSequence ' . $sOrderBy;
            $iSwapID = $GLOBALS['objDB']->Query($sSQL);
            switch($GLOBALS['objDB']->NumRows($iSwapID))
                  {
                  case 2:
                        $aRow1 = $GLOBALS['objDB']->FetchData($iSwapID);
                        $aRow2 = $GLOBALS['objDB']->FetchData($iSwapID);
                        $sSQL = 'UPDATE kpi_element_types SET ElementTypeExecutionSequence = -1 WHERE UniqueID = ' . $aRow1['UniqueID'] . '; ' .
                              'UPDATE kpi_element_types SET ElementTypeExecutionSequence = ' . $aRow1['ElementTypeExecutionSequence'] . ' WHERE UniqueID = ' . $aRow2['UniqueID'] . '; ' .
                              'UPDATE kpi_element_types SET ElementTypeExecutionSequence = ' . $aRow2['ElementTypeExecutionSequence'] . ' WHERE UniqueID = ' . $aRow1['UniqueID'] . '; ';
                        $iSwappedID = $GLOBALS['objDB']->Query($sSQL);
                        $GLOBALS['objDB']->FreeResult($iSwapID);
                        $sResponse = '<tr class="FormColumn_Header_TR"><th class="FormColumn_Header_TH_Success" colspan="' . $this->iTableViewSpanColumns . '"><span class="FormColumn_Header_SPAN">Move ' . $sDirection . '</span></th></tr><tr class="FormColumn_Data_TR_1"><td class="FormColumn_Data_TD_CID_R1" colspan="' . $this->iTableViewSpanColumns . '"><span class="FormColumn_Data_SPAN">Entry was moved ' . strtolower($sDirection) . ' successful.</span></td></tr>';
                        $this->KPIFormMaintElementTypes_ResyncExecutionSequences();
                        $this->AssignDefaultTableActions();
                        return call_user_func(array($this, self::FORM_DEFAULT_ACTION), $sResponse);
                  default :
                        break;
                  }
            }
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
Richard QuadlingSenior Software DeveloperCommented:
NOTE: My elementtypeexecutionsequence column has a unique constraint against it. Which is why I do the -1 bit. If you didn't have it unique, then you could remove that line of the SQL.

And the SQL server I'm using is SQL 2K.
0
nriddockAuthor Commented:
thx for all your help
0
Richard QuadlingSenior Software DeveloperCommented:
Glad to have helped.

Did you improve on the re-sequencing code?

Richard.
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
PHP

From novice to tech pro — start learning today.