homepage Welcome to WebmasterWorld Guest from 54.197.183.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
mysql - del a specified number of characters from start of field
what mysql command can do this?
snowweb

5+ Year Member



 
Msg#: 3472849 posted 3:53 pm on Oct 9, 2007 (gmt 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

 

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3472849 posted 4:03 pm on Oct 9, 2007 (gmt 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.

coopster

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



 
Msg#: 3472849 posted 5:15 pm on Oct 9, 2007 (gmt 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.

snowweb

5+ Year Member



 
Msg#: 3472849 posted 11:19 pm on Oct 9, 2007 (gmt 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

coopster

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



 
Msg#: 3472849 posted 12:53 am on Oct 10, 2007 (gmt 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.

snowweb

5+ Year Member



 
Msg#: 3472849 posted 12:56 am on Oct 10, 2007 (gmt 0)

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

Reg. pete

snowweb

5+ Year Member



 
Msg#: 3472849 posted 2:54 am on Oct 10, 2007 (gmt 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

snowweb

5+ Year Member



 
Msg#: 3472849 posted 8:08 am on Oct 10, 2007 (gmt 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved