Updating field with multiple entries

Posted: Sun Sep 17, 2006 4:42 am

I'm a bit stumped on this one.
I have a table that contains a field with multiple entries seperated by carriage returns and need to update the feild content by appending data to the list.
Assuming the table name is 'names' and the field is named 'salutation', the field type is 'longtext'.
The content of the field in phpmyadmin looks like this

Quote:

Mr
Mrs
Miss

I am using a php form to update the field but its the actual SQL syntax I cannot figure out, would I use INSERT (seems unlikely as I am not actually adding a 'record' or UPDATE?

registered · Site Admin Joined: Jun 04, 2004 Posts: 6437

You could export the data to see what the insert looks like. The update SQL would be similar.

Posted: Sun Sep 17, 2006 7:27 am

Interesting idea, I had not thought of that!!

Posted: Sun Sep 17, 2006 7:48 am

The field shows me its contents are actually

Code:

Mr\r\Mrs\r\Miss

Hmm,that has completely dumbfounded me lol

registered · Posted: Sun Sep 17, 2006 8:03 am

I will assume that you already have a string variable where you have concatenated the additional string data to it and all you are needing to do is the UPDATE. A simple update might look like this:

$sql = 'UPDATE names SET salutation = \'.$salutation.'\' WHERE <<whatever will get you that ONE row to be updates>>';

Edited: Sorry, I forgot the most important piece of information in how to restrict the update to just the one row!

Posted: Sun Sep 17, 2006 9:39 am

I figured out how to retrieve the data from the DB, I'm using

Code:

$result = mysql_query("SELECT * FROM `".$prefix."_names` WHERE `salutation` = '3'");


// saultation has a row id of 3  


   $row = mysql_fetch_array( $result );


   echo "Salutation: ".$row['salutation'];

Obviously I don't actually need the echo in there, it was just for testing purposes.
So all I need to do now is assign $something = $row['salutation'] and concatenate the additional data to the end and hope I do not have to do something weird with addslashes to get the \r into the DB as well.
I'll certainly test your code out Montego, thanks for posting it.
I wasn't sure which way to go (UPDATE v INSERT) as I'm only updating one field in the row and havent used UPDATE enough to be familiar with its use.

Posted: Sun Sep 17, 2006 10:38 am

Just a quick update, yep, that UPDATE statement worked a treat, I tested it by getting the data then using the update to put it straight back in.
All I need to now is get a carriage return inbetween the two concatenated strings and I'm cooking with gas!

Posted: Sun Sep 17, 2006 10:55 am

All done - thanks guys!

Posted: Tue Sep 19, 2006 5:32 am

Well, that didn't take long to figure out! Laughing

Good job!