LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 03-30-2007, 10:26 AM   #1
SimonT
Member
 
Registered: Oct 2004
Posts: 33

Rep: Reputation: 15
MY SQL Clean up a field


Once again I need help with a little MYSQL I have a column that holds some thing like this

Code:
<A href="http://www.mysite.com/blog/category/home-diy/"><IMG class="" height=149 src="http://www.mysite.com/blog/images/home.png" width=225></A>
There are about 900 records and I want to just extract the category and enter it in to the next column. The category changes every now and again there are about 20 in total is there a easy way to do this ?
 
Old 03-30-2007, 05:12 PM   #2
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,052

Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
I think it is easy what you ask, but you'd need to post a better description of you problem. For example the related tables and table layout that you are using.

The link that you posted tells absolutly nothing about your SQL problem.

jlinkels
 
Old 03-30-2007, 06:51 PM   #3
SimonT
Member
 
Registered: Oct 2004
Posts: 33

Original Poster
Rep: Reputation: 15
Lightbulb Database layout and more info

Hi jlinkels thank you for the reply this database is for a legacy blog application that I want to try and convert to wordpress. Before I try and do any converting I need to try and get the basic fields sorted out, as this blog application did not hold a field with the category. I created the CATEGORY my self and now need to some how extract the "category word" from the SUMMARY field and insert it in to the CATEGORY field

Here is a copy of the database all fields are populated but not the CATEGORY field as I created that. The html code that I pasted to you in the first post is what is held in the SUMMARY field. I need to try and extract that and pull out the "category word" and insert it in to the CATEGORY field.

The first example I posted would need to try and extract "category word" home-diy.

I have a list of all the possible categories if that would helps



Code:
CREATE TABLE `BLOG` (
`SUMMARY` varchar(100) default NULL,
`CATEGORY` varchar(20) default NULL,
`TITLE` varchar(110) default NULL,
`CONTENT` text,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
Old 03-31-2007, 07:30 AM   #4
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,052

Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
Well, that is a little bit less easy, I thought that you wanted just to update a column. I have not completely succeeded either, but maybe I have found something useful.

First I created a table which contains all categories. I called that 'categories'. It has only one column: 'category'.

Then I created another table which I called 'export', with two colums, 'summary' and 'category'. You might want to add some more columns yourself like 'title' etc.

Now to populate the 'exports' table, I combined all rows in 'blog' with one row in 'categories' using the condition that the word in 'categories' is part of the string in 'blog'.

Code:
INSERT into EXPORT SELECT blog.summary,categories.category FROM blog
 INNER JOIN categories ON summary REGEXP categories.category;
Now the contects of the blog table is:
Code:
mysql> select * from blog;
+-------------------------------------------------------+----------+
| summary                                               | category |
+-------------------------------------------------------+----------+
| This is something which belongs in category technical | NULL     |
| Another something which belongs in category technical | NULL     |
| This is something which belongs in category social    | NULL     |
| Another social event description                      | NULL     |
| technical                                             | NULL     |
+-------------------------------------------------------+----------+
5 rows in set (0.01 sec)
The categories
Code:
mysql> select * from categories;
+-----------+
| category  |
+-----------+
| technical |
| social    |
+-----------+
2 rows in set (0.00 sec)
And finally the combined result in exports:
Code:
mysql> select * from export;
+-------------------------------------------------------+-----------+
| summary                                               | category  |
+-------------------------------------------------------+-----------+
| This is something which belongs in category technical | technical |
| Another something which belongs in category technical | technical |
| technical                                             | technical |
| This is something which belongs in category social    | social    |
| Another social event description                      | social    |
+-------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
Maybe there are smarter ways to perform this, and even write the category column back into the blog table, but this what I could come up with. After all it is just a one time use I assume.

jlinkels
 
Old 03-31-2007, 08:36 PM   #5
SimonT
Member
 
Registered: Oct 2004
Posts: 33

Original Poster
Rep: Reputation: 15
jlinkels you are a genius thank you for this I will give it a shot my only other idea was to maybe clean up the field with a Regular expression any thing from the left of "mysite.com/blog/" remove any thing from the right of "/"><IMG class=" remove.

Of cause I would first have to make a copy of the column
 
  


Reply

Tags
clean, extract, help, mysql, query


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Sql - stored procedures, views, and dynamic sql generation LXer Syndicated Linux News 0 08-17-2006 06:33 AM
SQL: old sql dump not loading into new db BrianK Linux - Software 1 07-25-2006 11:55 PM
Migrating from MS-SQL server to My-SQL emailssent Linux - Networking 2 02-07-2005 02:20 PM
Which SQL is suitable for EMbedded SQL on C? hk_michael Programming 4 01-10-2005 05:07 PM
[SQL] Generic Field Specifier? WeNdeL Linux - Software 0 04-12-2004 09:55 AM


All times are GMT -5. The time now is 11:20 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration