Creation of a case file

gderoover

Hello,

I have been working on database script for a while now, trying to correct errors form a previous person and make it workable. thanks to the help of many in this forum i am almost there by one only.
the db is for a non profit rehab organization to follow up cases. Now at the creation of a new case, not only there is the auto_increment id but we want to have a case number compose by different variables some remain the same some vary in function of the case

US: Remain the same
X: number of time in therapy (1, 2, 3, etc)
XX: Country code of citizenship of the patient
1: Remain the same for economic code
XX: initial of the patient (taken for first and last name)
XX: Birthday month of the patient
XX: Birthday year of the patient
US: Remain the same for country of treatment

Could someone have a idea where to start, I have look at concat and other possibilties but because it seems of mix of various options I am lost

Thnks

Giro

nemonoman

Read this rant first...

gderoover

Could not disagree with you, but this database needs to connect and transfert some data on regular basis to a central external database run with Oracle and this is the system they got. The key would be that idcase that will matcj the upload, so I got not too many choices, i need to create that field.

giro

nemonoman

Ideally you would save the code in different columns (or fields) of the same table, and use some concatenate function to create the case identifier.

In MySQL the function is "CONCAT"
like:

SELECT CONCAT('US', timeintherapy,countryofcitizenshipcode,'1',
SUBSTRING(firstname,1,1),
SUBSTRING(lastname,1,1),
DATE_FORMAT(birthday,'%d%y'),
'US')
FROM patient
WHERE patientID=1234

THe CONCAT statement creates a string from string literals (the 'quoted' items), and the data in the row.

Some dbs use "CONCATENATE(" as the function name.

gderoover

Yes this is what i am intending to do to have it come from separate field, i almost had it last night but i was missing the substring, i will try them.

thanks

Giro

gderoover

Now the query works in sql, and i am trying to integrate it in a php page that process the form user filed to insert the values in the db.
This is what i am trying and it does not work.

$dusnat = @mysql_query("SELECT concat('ES',timeintherapy,citizenship,'1',SUBSTRING(firstname,1,1),SUBSTRING(lastname,1,1),
DATE_FORMAT(birthdate,'%d%y'),'.''ES') as caseidnumber FROM $table_name");

$sql = " INSERT INTO $table_name (id, caseidnumber, timeintherapy,citizenship, firstname,lastname,birthdate) VALUES ('', 'caseidnumber' '$_POST[timeintherapy]', '$_POST[citizenship]', '$_POST[firstname]', '$_POSTlastname]', '$_POST[birthdate]' )";

$result = @mysql_query($sql) or die("Couldn't execute query.");

Any clue. thanks giro

gderoover

My last post was messy, i am trying now a sequence but still no luck and I am exausted by this. Thanks for ANY help you may provide.
//Create the record first
$sql=" INSERT INTO $table_name (id,caseidnumber,timeintherapy,citizenship,firstname,lastname,birthdate)
VALUES('', '', '$POST[timeintherapy]', '$POST[citizenship]', '$POST[firstname]', '$POSTlastname]', '$POST[birthdate]')";_

$result=@($sql,$connection)or_die("Couldn't execute query.");

//Then update the record just created
$sql="UPDATE$table_name_SET_caseidnumber=CONCAT('ES',timeintherapy,citizenship,'1',SUBSTRING(firstname,1,1),SUBSTRING(lastname,1,1),DATE_FORMAT(birthdate,'%d%y'),'.''ES')where_id=$id";_

$result=@($sql,$connection)_or_die("Couldn't execute query.");

gderoover

Got it working the last section has to be.The problem was the lastInsert_ID()

$sql = " update $table_name set idcasodusnat=concat('ES',timeintherapy,citisenship,'1',SUBSTRING(firstname,1,1),SUBSTRING(lastname,1,1),
DATE_FORMAT(birthdate,'%d%y'),'.','ES') where id=LAST_INSERT_ID()";

$result = @mysql_query($sql,$connection) or die("Not Possible.");