Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have quite a few old posts that contain an img tag with height and width with no double quotes around the numbers. For example:

<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>

I am not sure how to write a MySQL query that can find the height and width numbers, and surround them with double quotes.

Although a MySQL query is preferred, if this is not possible then perhaps someone could suggest a regular expression I could use with a PHP script that might solve this issue.

share|improve this question
add comment

2 Answers 2

You are simply going to love this one

First of all, here is a sample table with data loaded:

mysql> use junk
Database changed
mysql> drop table todd;
Query OK, 0 rows affected (0.01 sec)

mysql> create table todd (id int not null auto_increment,url VARCHAR(255),
    -> primary key (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO todd (url) VALUES
    -> ('<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>'),
    -> ('<img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130>'),
    -> ('<img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206>'),
    -> ('<img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498>'),
    -> ('<img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499>');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from todd;
+----+-------------------------------------------------------------------------------------+
| id | url                                                                                 |
+----+-------------------------------------------------------------------------------------+
|  1 | <img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496> |
|  2 | <img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130> |
|  3 | <img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206> |
|  4 | <img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498> |
|  5 | <img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499> |
+----+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

You must run two queries one after the other:

This one puts doublequotes around the number height

UPDATE 
(select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select 'height=' token) w
WHERE LOCATE(CONCAT(token,'"'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;

This one puts doublequotes around the number width

UPDATE 
(select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select 'width=' token) w
WHERE LOCATE(CONCAT(token,'"'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;

Watch what happens when I run these and show table contents:

mysql> UPDATE
    -> (select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
    -> FROM (select id,token,
    -> substr(b.url,1,a.hpos - 1) bftoken,
    -> substr(b.url,a.hpos + length(a.token)) aftoken,
    -> substr(b.url,a.hpos + length(a.token))+0 num,
    -> length(substr(b.url,a.hpos + length(a.token))+0) num_length
    -> from
    -> (select id,token,LOCATE(token,url) hpos
    -> from todd,(select 'height=' token) w
    -> WHERE LOCATE(CONCAT(token,'"'),url)=0) A
    -> INNER JOIN todd B USING (id)) AA) AAA
    -> INNER JOIN todd BBB USING (id)
    -> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> UPDATE
    -> (select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
    -> FROM (select id,token,
    -> substr(b.url,1,a.hpos - 1) bftoken,
    -> substr(b.url,a.hpos + length(a.token)) aftoken,
    -> substr(b.url,a.hpos + length(a.token))+0 num,
    -> length(substr(b.url,a.hpos + length(a.token))+0) num_length
    -> from
    -> (select id,token,LOCATE(token,url) hpos
    -> from todd,(select 'width=' token) w
    -> WHERE LOCATE(CONCAT(token,'"'),url)=0) A
    -> INNER JOIN todd B USING (id)) AA) AAA
    -> INNER JOIN todd BBB USING (id)
    -> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from todd;
+----+-----------------------------------------------------------------------------------------+
| id | url                                                                                     |
+----+-----------------------------------------------------------------------------------------+
|  1 | <img height="319" alt="" src="http://www.example.com/images/myexample.jpg" width="496"> |
|  2 | <img height="329" alt="" src="http://www.example.com/images/myexample.jpg" width="130"> |
|  3 | <img height="339" alt="" src="http://www.example.com/images/myexample.jpg" width="206"> |
|  4 | <img height="349" alt="" src="http://www.example.com/images/myexample.jpg" width="498"> |
|  5 | <img height="359" alt="" src="http://www.example.com/images/myexample.jpg" width="499"> |
+----+-----------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select * from todd;

Give it a Try !!!

CAVEAT

  • If you post the real table structure, I'll write the correct SQL for that table.
  • If you run the queries multiples, it will not change anything additional after the first change.
share|improve this answer
add comment

Here's your started for 10...

You can use the REGEXP operator in a MySQL query, then do an update on the returned results.

The regular expression you need will be something like:

width=([0-9]*)

So you're query will be something like:

SELECT * FROM table WHERE column REGEXP "width=([0-9]*)"

The reason I'm using the terminology "something like" is that i can not test this on a database.

Further reading for you:

http://www.regular-expressions.info/mysql.html

Hope this helps.

share|improve this answer
    
For doing regex replacements in MySQL, also see stackoverflow.com/questions/986826/… –  Geert Apr 12 '12 at 9:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.