Welcome to WebmasterWorld Guest from 54.167.86.211

Forum Moderators: open

Message Too Old, No Replies

mysql - del a specified number of characters from start of field

what mysql command can do this?

     
3:53 pm on Oct 9, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


Hi, I need to delete a specific number of characters from a LONGTEXT type field in mysql 4.0.

I've searched the manual and can't find quite what I'm looking for. Any ideas please?

Regards

peter

4:03 pm on Oct 9, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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.

5:15 pm on Oct 9, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.
11:19 pm on Oct 9, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


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

12:53 am on Oct 10, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.
12:56 am on Oct 10, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


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

Reg. pete

2:54 am on Oct 10, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


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

8:08 am on Oct 10, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


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