Do you have a scripting language available to you?

It may be easier for you to write a script to select data then do the string parsing in code and then write it back to the DB as an update within a loop.

You don't need a MySQL-specific command as you can perform the task using standard SQL. You could run an UPDATE command to SET the value of the field to a SUBSTRING of itself.

Yes, sorry.. forgot to mention that I'm coding in PHP5.

The field in question is for recording customer notes and will be automatically updated, onBlur. We're using AJAX to perform the update.

onBlur, a header, '*** time/date ***' is added to the top of the field and it is appended to the cust_notes field, in the db.

(there's a bunch of JS to prevent duplicate entries, null entries or a bunch of whitespace)

Then we check using JS to see whether the field has already been saved (this is client side only), if it has, then add a parameter to the URL to inform insert.php that the field's already been saved, so we need to find what was saved previously, and replace it with the updated field contents, appending it to the previous notes (possibly from months before).

I have worked out he appending query, which is currently..

$query = ' UPDATE `' . $table_name . '` SET `' . $field_name . '` = CONCAT("' . $_GET["data"] . '","\n\n\n",' . $field_name . ') ';

That bit seems to work ok.

Next we identify the previous 'partial entry' by finding the 2nd occurrence of '*** ' (from the head of the note) and return the length of the previous note.

The query I'm using for that is..

$query = "SELECT SUBSTRING_INDEX($field_name, '*** ', 2) AS 'Note' FROM $table_name WHERE $_GET[where]= $_GET[record_id] " ;

Eg. '132' is returned.

That's the background of the problem. Now I need to replace the first 132 characters with my 'more complete' note, or just delete those characters, then append my note using my CONCAT query above.

I'm conscious that the field could get pretty big over time, so I'm trying to avoid reading the entire field and then writing it all back again.

I don't suppose it would be possible to enclose the entire process in one transaction?

Regards, pete

Use a combination of POSITION and SUBSTRING functions to determine where and what to replace. You may even be able to use a CASE structure to get the logic down in one statement.

OK thanks Coopster. All new statements to me. I'll need to read up, have a go and report back.

Reg. pete

I'm very confused here. The syntax of SUBSTRING does not appear to allow me to manipulate text based purely on positions and lengths. All the examples in the manual include a 'str' parameter.

I have ways to find (and have done) the length of the text at the beginning of the field that I want to lose. I also know the lenght of the text that should remain.

Is there a way that I can say either:

delete the first X characters of the field, or

SELECT the last X characters of the field?

How I wish the mysql manual was as good as the PHP one with plenty of real life examples!

Please help! Thanks in advance :)

pete

I think I've finally found a way that works :)

It's not exactly elegant but for the lack of any other suggestions, this is what I have:

 // find length of previous entry and get the field from db
 $query = "SELECT SUBSTRING_INDEX($field_name, '*** ', 2) AS 'Note', `$field_name` FROM `$table_name` WHERE $_GET[where]= $_GET[record_id] " ;
 // execute the query
 $result = mysql_query($query) or die(sql_failure_handler($query, mysql_error()));
 
 while ($row = mysql_fetch_assoc($result))
 {
  $sniplen = strlen($row['Note']) ;
  $db_version = $row[$field_name] ;
 }
 // end find length of previous entry and get the field from the db
 
 
 // replace unwanted text and add form contents.
 $final_text = addslashes($data) . '\n\n\n' . addslashes(substr_replace ("$db_version", "", 0, $sniplen )) ;
 // replace unwanted text and add form contents.
 
 // save final_text back to db
 $query = " UPDATE `$table_name` SET `$field_name`= '$final_text' WHERE `$where` = '$record_id' ";
 
 
 // save the info to the database
  $result = mysql_query($query) or die(sql_failure_handler($query, mysql_error()));

I'm sure there is much scope for improvement here. Feel free to educate me!

Regards, pete