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.
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
