help storing large text into mysql please..

hi, what's the best way to save large texts into mysql?
i know there's a mysql table field called TEXT wich allows up to 65000 bytes of text but that probably won't be enough for a hospital software that will have to insert information into that field each time a patient visits his doctor.
also, i have to give some format to that text, because every new visit (every new entry of text) there will have to be a break line (\n?) and the date of the visitation.

thanks a lot.

I'm not sure if that what you ask for but there are bigger text containers then "text", check the manual about further descriptions:
http://dev.mysql.com/doc/refman/5.0/en/data-types.html

line breaks:
if your text is holding a "\n" this will be stored is in mysql too

Thanks olaf!!
Now I'm doing my .sql file but I'm stucked again. I don't know how convenient it is to hold this amount of columns in a single table.. I haven't finished capturing the fields and so far I got about 50 columns..


CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL auto_increment,
  `id_usuario` int(11) NOT NULL default '',
  `nombres` varchar(70) NOT NULL default '',
  `apellido1` varchar(50) NOT NULL default '',
  `apellido2` varchar(50) NOT NULL default '',
  `sexo` varchar(1) NOT NULL default '',
  `fechadenacimiento` date NOT NULL default '',
  `escolaridad` varchar(50) NOT NULL default '',
  `direccion` varchar(100) NOT NULL default '',
  `codigopostal` int(10) NOT NULL default '',
  `ciudad` varchar(50) NOT NULL default '',
  `pais` varchar(50) NOT NULL default '',
  `telefono` int(15) NOT NULL default '',
  `fax` varchar(50) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `motivodeconsulta` varchar(255) NOT NULL default '',
  `padecimientoactual` longtext NOT NULL default '',
  `heredofamiliares` longtext NOT NULL default '',
  `patologicos` longtext NOT NULL default '',
  `nopatologicos` longtext NOT NULL default '',
  `presionarterialbd` int(7) NOT NULL default '',
  `presionarterialbi` int(7) NOT NULL default '',
  `frecuenciacardiaca` int(3) NOT NULL default '',
  `frecuenciarespiratoria` int(3) NOT NULL default '',
  `temperatura` int(2) NOT NULL default '',
  `peso` int(3) NOT NULL default '',
  `estatura` int(3) NOT NULL default '',
  `cabezaycuello` longtext NOT NULL default '',
  `torax` longtext NOT NULL default '',
  `abdomen` longtext NOT NULL default '',
  `extremidades` longtext NOT NULL default '',
  `neurologico` longtext NOT NULL default '',
  `hb` varchar(15) NOT NULL default '',
  `cmhg` varchar(15) NOT NULL default '',
  `vcm` varchar(15) NOT NULL default '',
  `leucocitos` varchar(15) NOT NULL default '',
  `neut` varchar(15) NOT NULL default '',
  `linfos` varchar(15) NOT NULL default '',
  `bhcotros` mediumtext NOT NULL default '',
  `plq` varchar(15) NOT NULL default '',
  `glucosa` varchar(15) NOT NULL default '',
  `creat` varchar(15) NOT NULL default '',
  `acidourico` varchar(15) NOT NULL default '',
  `colesteroltotal` varchar(15) NOT NULL default '',
  `trigliceridos` varchar(15) NOT NULL default '',
  `hdl-c` varchar(15) NOT NULL default '',
  `ldl-c` varchar(15) NOT NULL default '',
  `vldl-c` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `users_id` (`id_usuario`)
) TYPE=MyISAM;


I'm thinking of doing some correlational tables based on the users ID (not the ID field), as a matter of fact I will have to. But as a matter of fact, too, I think I will have to make this table as big as it is growing.
Do you know if this will cause poor performance or something?
I will only show between 1 and 10 rows per time anyway.
Greetings man!

For the usage you are talking about, I don't think storing the complete medical record of a patient into one large field is the best way to do this. A better way would be to add a new record to a table on each visit of the patient. To view the complete patients history you can select all the records from that table based on the patientID.

I can't make much sense out of your table, because I just can't read the field names. I think I understand a few fields though and think you have to redesign your database. A good database should have every piece of data only stored only once.

In your table I see fields to store the patients contact data (e-mail address, telephone number, fax etc.) and fields to store values that can change on every visit (temperature, cholesterol, etc.) These two types of data should be stored in sepperate tables. The patients e-mail address doesn't change when a cholesterol reading is done...

So I would make at least 2 tables:
1.Patient info: Unique patient ID, Name, address, email, date of birth, etc.
2.Patient Visit: Visit ID, patient ID, temp reading, cholesterol reading, text field, etc.

You can then match the two tables on the patient ID and in this way get all the visits to show up in the details page of the patient.

The data should probably be divided down into even more tables, but I hope you get the basic idea!

Hey Mind, thanks for your comments!!
Actually, I have two tables.
This other one (posted above) is called "consultas" consult.
The one I posted before it should have been called MEDICAL HISTORY because it's the first data we will have on patients expedient. You know, it's very important to have the first information (including cholesterol, gulcose, etc) on a non-editable by the user table because this information is very important for a Doctor to be protected because if someone edits this information later on it could be used for fraud or something.
The table "users" or "usuarios" will be editable with a PHP script very similar to the one Olaf used on the Access_User Class (the one that will show the results into form fields for easier editation (sp?))
So, here's the other (editable) table (called consultas)..


CREATE TABLE `consultas` (
  `id` int(11) NOT NULL auto_increment,
  `id_usuario` int(11) NOT NULL,
  `fechadeconsulta` date NOT NULL,
  `motivodeconsulta` varchar(255) NOT NULL default '',
  `s` longtext NOT NULL default '',
  `o` longtext NOT NULL default '',
  `ecg` longtext NOT NULL default '',
  `ttorax` longtext NOT NULL default '',
  `laboratorio` longtext NOT NULL default '',
  `a` longtext NOT NULL default '',
  `p` longtext NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_usuario` (`id_usuario`)
) TYPE=MyISAM;

[Guest]

[62.870]

Msnbot, adsense, hobbit, Yahoo crawler, AndyPR, hirewebdesigner