Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql removing text from column entry and placing removed text in different column

Posted on 2010-09-03
10
Medium Priority
?
504 Views
Last Modified: 2013-12-13
OK Folks,

Before I start I'm still learning mysql/php and EE is helping a lot! thanks to everyone for their past and indeed future assistance.

I have a database application written in php/mysql, I need to do some conversion on the customer name entry which has been used to hold the following information "loan - <customer name> - <ticket number>"

I need to remove "loan -" from `customer_name` and need to move <ticket number> to `contract`

Example data for customer_name is;
   "loan - acme computers - 0029319382"
   "loan - freds place - 0038227718"
   "loan - big dave's hair salon - 093828232"

I need customer_name to be;
   "acme computers"
   "freds place"
   "big dave's hair salon"

and contract_number to be;
   "0029319382"
   "0038227718"
   "093828232"

If this can be done in sql then thats great, if not then I'll need a way of doing it in php, I can list the entries from the DB ok, I'll just need the conversion code.
0
Comment
Question by:jools
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Expert Comment

by:rubin83
ID: 33594699
you will have to select the required data first using sql query and then insert the data fetched using insert in sql.
0
 
LVL 2

Accepted Solution

by:
Maverickerko earned 2000 total points
ID: 33594708
i think thi should work
$sql = "SELECT * FROM loans";
$result = mysql_query($sql);

while($row=mysql_fetch_array($result)) {
$line = $row["YOUR_NAME_OF_ROW"];

$data = explode($line," - ");

$name = $data[1];
$ticket = $data[2];

$sql = "INSERT INTO new_table (name,ticket) values ('".$name."','".$ticket."')";
mysql_query($sql);

}

Open in new window

0
 
LVL 2

Expert Comment

by:Maverickerko
ID: 33594715
you have to replace YOUR NAME OF ROW with name of row where are the loans saved and new_table is name of your new created table
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Author Comment

by:jools
ID: 33594732
@Maverickerko:
Thanks, I'll give it a go.
0
 
LVL 2

Expert Comment

by:Maverickerko
ID: 33594985
did you try it ???
0
 
LVL 19

Author Comment

by:jools
ID: 33595047
having issues, it's turning into a data conversion exercise, I was told the data was in a specific format and there were a few entries that wernt but, as is the way of the world, the data is all over the place this is all interspersed with data that is not related to the conversion and does not need to be altered, I can filter this off in the original sql.

I have, by way of an example;
   "loan acme computers -0029319382"
   "loan - freds place Ref 0038227718"
   "loan big dave's hair salon 093828232"
   "Pink fluffy dice"
   "Rays Music Exchange"

And all combinations thereof, I may have to read up on preg_replace rather than explode...

give me a little while, I should be able to test soon.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33596831
This link might give you some valuable ideas.
http://lmgtfy.com?q=Normalize+my+Data+Base

And this book will help you with a lot of the basics.
http://www.sitepoint.com/books/phpmysql4/

I don't think you need a REGEX - you need to redesign the tables so that each column holds only one kind of information.  Is this already inserted into a data base?  Or is it coming from an external data feed?  If it were my task, I would spew the data out into a flat text file and go over it with a text editor, then put it into the data base in a more reasonable organization.
0
 
LVL 19

Author Comment

by:jools
ID: 33597420
@Ray_Paseur:
> This link might give you some valuable ideas.
lol, like it. however, it's all about time, I'm juggling about 5 projects today, all of which I'm told are really important so I don't have the time to google/research so EE really helps, on the other hand, like your style... :-)

The modification is required to move some historical (or is it hysterical) data into relevant columns, the loan - customername - ticket is being split, loan is being moved to a booking_type column , customername stays where it is (in the customername column) and ticket moves to the contract_ticket col.

There are a lot of entries so I'd prefer to do most of it automatically, however, running mysqldump and using vim was originally high on the list, I just don't think I have the time to do it all, if I can convert some of the data before manual editing it will help.


0
 
LVL 19

Author Comment

by:jools
ID: 33600401
@Maverickerko:
Sorry, ran out of time, I was having issues with the explode but that may be due to the actual data now being in the right format.

I'll have another look again on Monday.
0
 
LVL 19

Author Closing Comment

by:jools
ID: 33626957
I got it

The explode was the wrong way round, I eventually used a combination of preg_replace and explode to sort out most of the data, the remaing data will be edited from a dump in vim but I hope there wont be that much left.

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question