update question

siobhan

Hi,

I have a database column called handset which houses a list of handsets like so: nokia 7200 (12938 bytes), nokia 3600 (59192 byes) et etc

I woud like to do an update on the column so that I can replace all of the (xxxxx bytes) with a comma

However, it is not so straightforward because the number of characters between each parenthesis varies.

How do I do this?

Thanks!

nemonoman

What do you mean replace by a comma? Like, 1,234,567
Will you include or not include the word 'bytes'?
Is every entry consistently ending with the phrase "bytes)"?
If so, and if the numbers are all between 1000 and 999999 your problem is easy to fix. Can you figure out why?

siobhan

thanks for your reply! Sorry wasn't very clear!

I need to make it so that the data that looks like this:

nokia 7200 (12938 bytes), nokia 3600 (59192 byes)

ends up looking like this:

nokia 7200, nokia 3600

Thanks

bubblenut

Well, if you're using MySQL, there's no regex_replace type function. I've come accross this problem before. You're going to have to pull the data out, change it in PHP and then update each record individually (a loop of some kin 😉). I can't speak for other databases.

nemonoman

actually there sort of is a regex function in mysql.

expr REGEXP pat
expr RLIKE pat
Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in section F MySQL Regular Expressions. Returns 1 if expr matches pat, otherwise returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL uses the C escape syntax in strings (for example, \n' to represent newline), you must double any\' that you use in your REGEXP strings. As of MySQL 3.23.4, REGEXP is not case sensitive for normal (not binary) strings.

nemonoman

So:

does this go record by record:

record 1: nokia (123)
record 2: nokia (456)
etc.

Or do you just have all this stuck into a single text field of a single record?

If record by record
this should do it:

UPDATE table
SET searchtext=SUBSTRING( searchtext,
1,
position( ' (' IN searchtext )-1 )

bubblenut

Originally posted by nemonoman
actually there sort of is a regex function in mysql.

expr REGEXP pat
expr RLIKE pat
Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in section F MySQL Regular Expressions. Returns 1 if expr matches pat, otherwise returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL uses the C escape syntax in strings (for example, \n' to represent newline), you must double any\' that you use in your REGEXP strings. As of MySQL 3.23.4, REGEXP is not case sensitive for normal (not binary) strings.

I know of MySQL's REGEXP capabilities, however there is no regex replace function. Ths means that the altering of the data has to happen in PHP.

siobhan

Thanks! Right now sadly the handsets are all in one field (there are 400 records). The data comes from a drop down menu list of the handsets.

I'm just a bit confused about how to add in the search and replace bits into the update statement you've provided

If I want to get rid of the (182832 bytes) can I search for
"( bytes)"? Is this what I would put in instead of "searchtext"? Sorry I am quite confused about how to add my search and replace terms to the query below. I've looked at the mysql manual but can't figure it out.

UPDATE table
SET searchtext=SUBSTRING( searchtext,
1,
position( ' (' IN searchtext )-1 )

nemonoman

Here's how the statement works:

Let's say the name of your table is MyPhoneTable

Let's say the name of the field in question is MyBadField

Let's say the way the data is set up is
Nokia 123 (12345 bytes)

Let's say you wanted to remove everything to the right of the parenthesis, leaving 'Nokia 123'

You'd modify the statement to

UPDATE MyPhone
SET MyBadField=SUBSTRING( searchtext,
1,
position( '(' IN searchtext )-1 )

The statement says 'Go through ALL THE RECORDS in the MyPhone Table.
Look at MyBadField data
See if there's a left paren IN the Field (i.e., is it like Nokia 123 (12345 bytes)'
Find the position of the left paren (in this case it's character 11)
Make a substring of everything up to that character
Save the substring.

siobhan

Thanks! I understand now, sort of... the only thing is this gets rid of everything to the right of the left parentheses. I need it to just take out the (XXXX bytes) bit in the entire list in each record.

So I end up with a list of handsets relevant to that record e.g. Nokia 3210, Nokia 6100, Nokia 7210 etc.

I had to change the SQL you gave to get it to work in phpmyadmin, because I kept getting an error message when I left search text in the substring, but maybe I shouldn't have done this???

UPDATE MyPhone SET MyBadField=SUBSTRING( MyBadField, 1, position( '(' IN MyBadField )-1 )

Thanks

siobhan

Originally posted by bubblenut
I've come accross this problem before. You're going to have to pull the data out, change it in PHP and then update each record individually

Bubblenut can you explain how to change the data in PHP, I'm not quite sure how to do this.

Thanks

nemonoman

My SQL will do the trick for you and is simple to use. If you're having trouble making it work, post the data structure for this table using the 'export' command on phpMyAdmin, and I'll help you out.

siobhan

Okay I've exported the structure below and the first two records.

Thanks!

CREATE TABLE javagames4 (
gameID int(11) NOT NULL auto_increment,
code varchar(255) default NULL,
valid longtext NOT NULL,
name longtext,
starrating longtext,
genre longtext,
unlicencedterritories longtext,
comments longtext,
handsets longtext,
group longtext,
PRIMARY KEY (gameID)
) TYPE=MyISAM AUTO_INCREMENT=481 ;

Dumping data for table `javagames4`

INSERT INTO javagames4 VALUES (1, '2233', '', '2004 Gymnastics', '2', 'Sports', 'NONE', 'NONE', 'Nokia 3100 (49532 bytes)Nokia 3100b (49532 bytes)Nokia 3108 (49532 bytes)Nokia 3120 (49532 bytes)Nokia 3200 (49532 bytes)Nokia 3220 (49532 bytes)Nokia 3300 (49532 bytes)Nokia 3510i (50259 bytes)Nokia 3585 (50259 bytes)Nokia 3590 (50259 bytes)Nokia 3595 (50259 bytes)Nokia 5100 (49532 bytes)Nokia 5140 (49532 bytes)Nokia 6010 (50259 bytes)Nokia 6100 (49532 bytes)Nokia 6108 (49532 bytes)Nokia 6200 (49532 bytes)Nokia 6220 (49532 bytes)Nokia 6225 (49532 bytes)Nokia 6230 (49532 bytes)Nokia 6260 (69264 bytes)Nokia 6585 (49532 bytes)Nokia 6600 (69264 bytes)Nokia 6610 (49532 bytes)Nokia 6610i (49532 bytes)Nokia 6620 (69264 bytes)Nokia 6630 (69264 bytes)Nokia 6800 (49532 bytes)Nokia 6810 (49532 bytes)Nokia 6820 (49532 bytes)Nokia 7200 (49532 bytes)Nokia 7210 (49532 bytes)Nokia 7250 (49532 bytes)Nokia 7250i (49532 bytes)Nokia 7610 (69264 bytes)Nokia 8910i (50259 bytes)Nokia N-Gage (69264 bytes)Samsung E700 (62183 bytes)Samsung X100 (61961 bytes)', 'B');
INSERT INTO javagames4 VALUES (2, '2306', '', '2x2', '1', 'Logic/Strategy', 'NONE', 'NONE', 'Motorola T720 (25313 bytes)Nokia 3100 (33838 bytes)Nokia 3100b (33838 bytes)Nokia 3108 (33838 bytes)Nokia 3120 (33838 bytes)Nokia 3200 (33838 bytes)Nokia 3220 (33838 bytes)Nokia 3300 (33838 bytes)Nokia 3510i (23049 bytes)Nokia 3585 (23049 bytes)Nokia 3590 (23049 bytes)Nokia 3595 (23049 bytes)Nokia 3650 (70860 bytes)Nokia 3660 (70860 bytes)Nokia 5100 (33838 bytes)Nokia 5140 (33838 bytes)Nokia 6010 (23049 bytes)Nokia 6100 (33838 bytes)Nokia 6108 (33838 bytes)Nokia 6200 (33838 bytes)Nokia 6220 (33838 bytes)Nokia 6225 (33838 bytes)Nokia 6230 (33838 bytes)Nokia 6310i (11912 bytes)Nokia 6585 (33838 bytes)Nokia 6610 (33838 bytes)Nokia 6610i (33838 bytes)Nokia 6800 (33838 bytes)Nokia 6810 (33838 bytes)Nokia 6820 (33838 bytes)Nokia 7200 (33838 bytes)Nokia 7210 (33838 bytes)Nokia 7250 (33838 bytes)Nokia 7250i (33838 bytes)Nokia 7650 (70860 bytes)Nokia 8910i (23049 bytes)Nokia N-Gage (70860 bytes)', 'C');

nemonoman

I'm sorry. I didn't understand at all how you were storing your data, which frankly is a little bit loopy. You will have to use a php loop to fix it.

siobhan

Yes the data is stored in a really loopy way! But not my first choice, just have to live with it for a while.

So, I've got the php code sorted for updating but just have a few questions about the database side.

I need to edit each of the records in the handsets column (code below) and then repopulate each record with the appropriate edited handset list.

1) How do I put the handsets column in the $string below instead of typing in an actual string?

2) How do I get the edited string back into the database? I'm not sure what the best way to write the update statement would be??

3) How do I loop this through each of the 400 records making sure that the edited data matches the appropriate record

<?php
$string = "Nokia 3100 (49532 bytes)Nokia 3100b (3723 bytes)Nokia 3108 (12004 bytes) ";
$pattern = "/ (.+?)/";
$replacement = ",";
echo preg_replace($pattern, $replacement, $string);
?>

Thanks!
Siobhan