[Gllug] mySQL and PHP

[Paul Brazier]

Long term it would be best not to have structured data within a single
field, but to "normalise" and perhaps have the data in the Worklog colum
split up into other tables/columns.

Short term I guess you're stuck with the format in the .csv file.
Although once it's in the database you could use SQL scripts to split up
the column, using LEFT, TRIM, SUBSTRING etc (not sure of the exact
syntax for mySQL), either permanently into a normalised table design, or
temporarily for each query.

I've not used php much but I'm sure it would also have functions like
LEFT, TRIM, etc. to do the same processing at the web layer rather than
the database layer.

-----Original Message-----
From: HJackson at colt-telecom.com 
Sent: 10 July 2001 12:49
To: colt-telecom.com.HJackson; linux.co.uk;.gllug
Subject: [Gllug] mySQL and PHP


Hi all

 In the last two days I have managed to get Apache mySQL and PHP
working. They are in no means secure or set up properly yet but the I am
able to view the data on the internal network and my was that
impressive.
Never done anything like that before in my snuff and am waiting to
demonstrate it to the people who will decide if we can use this tool or
not.


 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. This means that when it
is
displayed it appears as a long String with no formatting at all and this
is
quite hard to read. As you can see from the title this is one of the
fields
that people will want to have access to because it holds the most
useable
data about the case in question.

Question:
 How do I format the text in the field.

Ideas:
 I am pretty sure you can use Perl to do this sort of thing but I am
not sure if it would need to be done before I load it into the database
or
if it can be done after it goes in. I am also not sure how to use Perl
in
both cases. My preferred option would be to use something internal to
mySQL
to avoid having scripts all over the place. I also do not want to have
to
format the field every time I call a page but when I load the data so
using
PHP seems out of the question unless there are Guru's out there who know
how
to do it.


use remedy_harry

truncate table remedy_base;

CREATE TABLE remedy_base 
(CASEID VARCHAR(20) ,
Arrived VARCHAR(20) , 
Type VARCHAR(20) , 
Name VARCHAR(30) ,
Short VARCHAR(150),
Details VARCHAR(255),
WorkLog TEXT,
Individual VARCHAR(20),
Status VARCHAR(30),
Pending VARCHAR(30));


LOAD DATA LOCAL INFILE "remedy.csv" INTO TABLE remedy_base 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"'
            LINES TERMINATED BY '\n';


Any help or pointers would be appreciated.

Regards;
Harry Jackson.


     



**********************************************************************
COLT Telecommunications
Registered in England No. 2452736
Registered Office: Bishopsgate Court, 4 Norton Folgate, London E1 6DQ
Tel. 020 7390 3900

This message is subject to and does not create or vary any contractual
relationship between COLT Telecommunications, its subsidiaries or 
affiliates ("COLT") and you. Internet communications are not secure
and therefore COLT does not accept legal responsibility for the
contents of this message.  Any view or opinions expressed are those of
the author. The message is intended for the addressee only and its
contents and any attached files are strictly confidential. If you have
received it in error, please telephone the number above. Thank you.


**********************************************************************

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



**********************************************************************
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. If you have received this email in error please notify
the originator.

This footnote also confirms that this email message has been checked
for the presence of computer viruses.

**********************************************************************

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