Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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
0
nriddock
Asked:
nriddock
  • 6
  • 2
  • 2
2 Solutions
 
Richard QuadlingSenior Software DeverloperCommented:
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 DeverloperCommented:
Yes. Normalize the data. This does make it better for the future.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 DeverloperCommented:
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 DeverloperCommented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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 DeverloperCommented:
Glad to have helped.

Did you improve on the re-sequencing code?

Richard.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now