MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jeff Shapiro Date:March 20 2003 4:10am
Subject:Re: appending to longtext field
View as plain text  
At 1:50 +0000 3/20/03, Mr Orange wrote:
>GV wrote:
>>  I think is better to have an additional record each time a user
>>  inserts
>>  new information
>>
>>  Mr Orange wrote:
>>>  Hello all,
>>>
>>>  I have a database with a type "longtext" called "notes".
>>>
>>>  Say I wanted to append some text to this field, what command would I
>>>  use?
>>>
>>>  I have tried the following sql..
>>>    update clients set notes=notes+"text to append" where id=1;
>>>
>>>  But this doesn't seem to do the job.
>>>
>>>  I am new to MySQL so sorry if I've asked an obvious question!
>>>
>>>  Many thanks in advance,
>>>
>>>  Steve.
>>>
>>>
>>>  PS.  What is needed is a database to store notes on each client
>>>  which can be added to as necessary.  If anyone has a better idea of
>>>  a way to do this, I'd be very grateful for any help!  Cheers.
>
>Hi GV,
>
>What do you mean by adding an additional record?
>
>At the moment, say I have a table set up as follows:
>   id int(6) not null auto_increment,
>   notes longtext,
>
>I have no idea at the beginning how many notes a user will enter on a
>particular client, so how would I create the table?  Obviously I
>couldn't have,
>   int int(6) not null auto_increment,
>   note1 longtext,
>   note2 longtext, etc.
>
>Is it possible to create a 'dynamic' table where the fields in there could
>grow to accomodate any inputted notes?
>
>If you could offer any help on this, by way of email, or links to webpages
>or whatever, I'd be really grateful,
>
>Cheers,
>
>Mr O.

I think what GV is suggesting is that every time a user adds a note 
to your system, you use the INSERT INTO ... command to add a new 
record into the database. I would add a timestamp column so that you 
can sort the notes by their order of entry (of course there are 
several other ways to accomplish this as well). Actually, this is 
probably the best way to handle the set up. You mention that these 
notes are being entered for a particular client. I assume that you 
also have a client table as well as the notes table. This is an ideal 
situation for a parent/child table relational set up (or one-to-many 
relationship). When you need notes for a particular client, you 
SELECT on the client identifier in the notes table and you will have 
a listing of the all notes for the client.

Here's basically the set up that I would use:

Parent table: (the client information)
--------------------------------------
client_id 	int(6) not null auto_increment,
name, address, and all the other important client contact stuff

Child table: (the note information)
------------------------------------
note_id 	int(6) not null auto_increment,
client_id 	int(6),
entry_date 	timestamp,
note 		longtext,
index 		idx_client_id (client_id),  <=== you may not need this index
foreign key references client_table (client_id)

Of course to use the foreign keys you will need to use InnoDB table type.

With this type of set up, a client may have anything from 0 to your 
free hard disc space of notes.

I'm sure that if I made some glaring error here, some one will point 
it out and correct me (which I woudl greatly appreciate :-).

Back to your original question:

You didn't mention what you are using to access MySQL. PHP? C++? Perl?

If I *really*, *really* wanted to keep only one note record for each 
client (or to have the notes in the client table), I would create an 
"update" form and populate the form with the proper client 
information and use the my language's functions to concatenate the 
old and new information. After that use an UPDATE statement to 
replace the existing note with the new note.

Depending on what type of system you are creating, doing this 
"update" method may bring in all kinds of headaches down the road. 
You may get people trying to change information that some one else 
put in the tables.

The best way to go is the parent/child relationship.

just my $0.02.
jeff


-- 

Jeff Shapiro, Colorado Springs, CO, USA

At work I *have* to use a Windows machine, at home I *get* to use a Mac.
Thread
appending to longtext fieldMr Orange19 Mar
  • Re: appending to longtext fieldGV19 Mar
  • Re: appending to longtext fieldMr Orange20 Mar
    • Re: appending to longtext fieldJeff Shapiro20 Mar