asked on

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

Richard Quadling

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.

crimson117

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.

Richard Quadling

Yes. Normalize the data. This does make it better for the future.

nriddock

ASKER

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

Richard Quadling

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

SOLUTION

crimson117

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Richard Quadling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Quadling

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.

nriddock

ASKER

thx for all your help

Richard Quadling

Glad to have helped.

Did you improve on the re-sequencing code?

Richard.