Zane Posted November 16, 2006 Share Posted November 16, 2006 I need some help...I have a list of around 800 college classes,with the description, course name, the hours, etc[color=orange]-created from REGEXing the hell out of these straight HTML pages ... btw (ouch)http://www.southwesterncc.edu/acadprog/desc/aca-bus.htm#aca[/color]This is the way the courses table looks[code]mysql> select id,coursenum, name from courses limit 10;+----+-----------+-------------------------------------+| id | coursenum | name |+----+-----------+-------------------------------------+| 1 | aca115 | Success and Study Skills || 2 | aca118 | College Study Skills || 3 | aca120 | Career Assessment || 4 | aca220 | Professional Transition || 5 | acc115 | College Accounting || 6 | acc121 | Principles of Managerial Accounting || 7 | acc129 | Individual Income Taxes || 8 | acc131 | Federal Income Taxes || 9 | acc150 | Acct Software Appl || 10 | acc175 | Hotel and Restaurant Accounting |+----+-----------+-------------------------------------+[/code]If you look at the coursenum field you notice it says the major a lot...[quote]accaccaccacc....[/quote]I also have a table full of these things with their keys[code]mysql> select * from curriculums limit 10;+----+--------+---------------------+| id | prefix | name |+----+--------+---------------------+| 1 | aca | Academic Related || 2 | acc | Accounting || 3 | ant | Anthropology || 4 | art | Art || 5 | aut | AUTOMOTIVE || 6 | baf | Banking and Finance || 7 | bio | BIOLOGY || 8 | bpr | Blueprint Reading || 9 | bus | Busniess || 10 | car | Carpentry |+----+--------+---------------------+[/code]maybe someone's caught on by now, but I can't figure how I would do this;I want to create new field in courses.....easygo through every record's coursenum field...add the corresponding curriculum id to the new field leaving just the numbers.....WHAT how..I'm clueless here....anyone have any advice or suggestions...I know I could make a PHP script to do this, with an arraybut that would take quite a bit of processing powerMy goal is to get it to look like this[code]+----+------------+-----------+-------------------------------------+| id | curriculum | coursenum | name |+----+------------+-----------+-------------------------------------+| 1 | 1| 115 | Success and Study Skills || 2 | 1| 118 | College Study Skills || 3 | 1| 120 | Career Assessment || 4 | 1| 220 | Professional Transition || 5 | 2| 115 | College Accounting || 6 | 2| 121 | Principles of Managerial Accounting || 7 | 2| 129 | Individual Income Taxes || 8 | 2| 131 | Federal Income Taxes || 9 | 2| 150 | Acct Software Appl || 10 | 2| 175 | Hotel and Restaurant Accounting |+----+------------+-----------+-------------------------------------+[/code] Quote Link to comment Share on other sites More sharing options...
Zane Posted November 16, 2006 Author Share Posted November 16, 2006 Alright, I use the DUMP feature in phpmyadmin to get a textlistand used Dreamweaver's regex feature to do close to what I needed..I have it looking like this now[code]+------+-----------+-------------------------------------+| curr | coursenum | name |+------+-----------+-------------------------------------+| aca | 115 | Success and Study Skills || aca | 118 | College Study Skills || aca | 120 | Career Assessment || aca | 220 | Professional Transition || acc | 115 | College Accounting || acc | 121 | Principles of Managerial Accounting || acc | 129 | Individual Income Taxes || acc | 131 | Federal Income Taxes || acc | 150 | Acct Software Appl || acc | 175 | Hotel and Restaurant Accounting || acc | 193 | Selected Topics-Forensic Accounting || aca | 215 | Ethics in Accounting || acc | 220 | Intermediate Accounting I || acc | 221 | Intermediate Accounting II || acc | 225 | Cost Accounting || acc | 269 | Audit and Assurance Services || acc | 292 | Selected Topics in Accounting || ant | 210 | General Anthropology || ant | 220 | Cultural Anthropology || ant | 221 | Comparative Cultures |+------+-----------+-------------------------------------+[/code]Still trying to figure out to convert them to their respective indecesany thoughts Quote Link to comment Share on other sites More sharing options...
Zane Posted November 16, 2006 Author Share Posted November 16, 2006 um.....bump Quote Link to comment Share on other sites More sharing options...
fenway Posted November 16, 2006 Share Posted November 16, 2006 Easiest way would be to add a curr_id column, then issue a multi-table UPDATE where you pull the ID associated with the name and put in into this new column, and then drop the old varchar column. Quote Link to comment Share on other sites More sharing options...
Zane Posted November 16, 2006 Author Share Posted November 16, 2006 I hate asking for code but what would that look likeI knew I'd have to use a combination of UPDATE and SELECT but I couldn't get the syntax rightwhen I first tried it..I got the errorthat there was more than one row returned.a query like thisUPDATE course2 a SET curr = (SELECT b.id from curriculums b where b.prefix = a.curr) Quote Link to comment Share on other sites More sharing options...
fenway Posted November 16, 2006 Share Posted November 16, 2006 Not a problem... I was thinking of something like:[code]UPDATE courses AS co LEFT JOIN curriculums AS cu ON ( co.curr = cu.prefix ) SET co.currnum = cu.id[/code] Quote Link to comment Share on other sites More sharing options...
Zane Posted November 16, 2006 Author Share Posted November 16, 2006 Yes....genious....it worked perfectthanksquestion....if I had used inner join, right join, or just joinwould it had made any difference Quote Link to comment Share on other sites More sharing options...
fenway Posted November 16, 2006 Share Posted November 16, 2006 No one should ever use right join; just join = inner join, and if you did that, any non-matches wouldn't be set to NULL explicitly. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.