[Gllug] mySQL and PHP

[Wulf Forrester-Barker]

Harry <HJackson at colt-telecom.com> wrote:

> The following is some SQL for mySQL that will create
> a table in my test database. The problem that I am
> getting is with the WorkLog column. Due to its size I
> have had to use TEXT as the type...
>
> Question:
>    How do I format the text in the field.

Congratulations on getting it all up and running. The next thing you need to do is to normalise your data... basically that means separating it out into several tables rather than having it in one big file.

Looking at the dataset you sent, you're talking about support issues, each of which will have multiple updates (ie. a one to many relationship).

At a guess, I'd be looking to break it into at least two fields:

tbl_issues
  key_issue: Auto-incrementing reference number
  XXXX: other fields which hold information that is held once and once only about a given issue.

tbl_updates
  key_updates: Auto-incrementing reference number
  fkey_issue: Link to the numbers in tbl_issues
  datetime: Date Time Stamp for update
  engineer: reference for engineer (a list of engineers would be a good candidate for yet another table in the database)
  comment: field to hold the text of the update.

You'd reconstruct this using an SQL Query, something like:

SELECT datetime, engineer, comment FROM tbl_issues, tbl_updates WHERE fkey_issue = XXX;

XXX is probably picked up from a web page that shows a list of all current issues. If you passed the variable across as issueno, the PHP line would be

$strSQL = "SELECT datetime, engineer, comment FROM tbl_issues, tbl_updates WHERE fkey_issue = $issueno";

(and then pass that SQL string to the database and display the results... each record will have a set of information relating to a single update)

Look up some stuff on the web about relational databases... if you're keen on learning, this is a good set of stuff to learn.

Alternatively, you could store the information in a long string with some HTML markup:

<tr><td>12 March 2001 13:40:32</td><td>jtaylor</td></tr><tr><td colspan="2">This is something for Group IT.</td></tr>

Display this between <table> ... </table> tags and you've got a ready formatted table.

However, my preference would still be for separating out the data into a number of related tables... that way you can also ask questions like 'Are any issues getting neglected?' or 'When was the last time Harry did any work?' ;-)

Wulf




wulf.f-b at uhl.nhs.uk

**********************************************************************
DISCLAIMER:

Any opinions expressed in this email are those of the individual and
not necessarily the Trust. This email and any files transmitted with
it are confidential and intended solely for the use of the individual
or entity to whom they are addressed. Any unauthorised disclosure of
the information contained in this e-mail is strictly prohibited.

The contents of this email may contain software viruses which could
damage your own computer system. Whilst we have taken every
reasonable precaution to minimise this risk, we cannot accept liability
for any damage which you sustain as a result of software viruses.
You should therefore carry out your own virus checks before opening
the attachment.

If you have received this email in error please notify the sender or
postmaster at uhl.nhs.uk. Please then delete this email.

University Hospital Lewisham
Tel: 020 8333 3000
Web: www.uhl.ac.uk
**********************************************************************

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug