If in array replace with corresponding column

Question

Update: What I am actually doing:

I am making an ETL tool and writing its script in php: ETL is being designed for specific domain, the provided data is consistent with its structure but there is some dirtyness in the data which is needed to be removed like H.NO , H#, H.NO. should be = HNO : I am using approach of stored procedure. In which i created a lookup table for wrong values and replacing them with right values so the final stored data should be perfect.

I have to detect the wrong values in the db table and replace them with the right one's thats all.

I have created an array $find[] that is storing values coming from DB, Then there is a variable $exist , which i want to check if it exists in the $find[wrong] array (column) or not. If It does then its value gets replace with the correspnding cloumn value $find[right]. Kindly let me know how can i do such sort of replacement. Thanks,

Note : My DB table contains only two columns named as wrong and right

When I dump my $find array it gave me the following output: Array ( [0] => Array ( [0] => FSc [wrong] => FSc [1] => FSC [right] => FSC ) [1] => Array ( [0] => Fsc [wrong] => Fsc [1] => FSC [right] => FSC )

CODE:

$lookup = mysql_query("select * from lookup");
while($getlookup = mysql_fetch_array($lookup))
{
$find[] = $getlookup; 
}
print_r($find);

What are you actually trying to do? There is definitely a right and wrong way to approach this problem and I fear you have chosen the wrong way. Are you just trying to get the value for right that exists in the database for a given value in wrong that equals $exists? — Mike Brant
– Mike Brant, Commented Dec 11, 2012 at 20:39

Mike Brant · Accepted Answer · 2012-12-11 21:57:29Z

1

OK you need to simply change your query to this

"SELECT right FROM lookup WHERE wrong = '" . $exists . "'"

This will return 0 rows if there is no match. Or one (or more rows depending on how table is setup) if there is a match.

You should have an index on your wrong field to optimize the query. If there should only be one specific value of right for any value of wrong (i.e. wrong is unique on each row), then make wrong a unique index to enforce it's uniqueness.

Updated answer to match your revised question. What I would do is simply use an SQL query to update your bad data from your mapping table.

So I am assuming you DB structure could look something like this:

the_table
---------
the_field (the field you are trying to change)
... some other fields

mapping_table
---------
wrong
right

Then the query you would need to run to update the table with sanitized values would like this

UPDATE the_table AS t
INNER JOIN mapping_table AS mt ON t.the_field = mt.wrong
SET bt.the_field = mt.right

This would update the table in place to the new values. Obviously, if you didn't want to change the table in place, you could make a copy of the table and then run this query against that table copy.

edited Dec 11, 2012 at 21:57

answered Dec 11, 2012 at 20:43

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

soft genic Over a year ago

actually i am performing data cleansing so i have created a lookup table there are various other fields which i wanted to be replaced so i thought if i store them to array and then check so i may be able to get results more quickly , cause i am dealing with ~50K rows coming from the DB and needed to clean the dirty data

soft genic Over a year ago

Kindly let me know in your point of view am i doing it right way or i should use some other trick, Currently What i am doing is uploading a file and dumping that to my DB, Then getting values from the Db and performing cleansing on them...

Mike Brant Over a year ago

If this is for a one time data migration, then this is definitely the wrong approach. You will likely be bale to do whatever you are needing to with even involving PHP. If you can clarify what you are trying to do in your question, then I am sure help can be provided.

Mike Brant Over a year ago

@softgenic Take a look at revised answer.

soft genic Over a year ago

kindly take a look at , you can test sqlfiddle.com/#!2/975c6/1 , I tested the query but H. no is not replacing

|

Wrikken · Accepted Answer · 2012-12-11 22:01:32Z

1

Solve in MySQL itself. When retreiving:

 SELECT right, IF(wrong='$exist',right,wrong) as wrong from lookup

Of course, use PDO & prepared statements rather then mysql_*, or be very careful with escaping variables.

However, if the 'wrong' data needs to go:

 UPDATE data_to_fix
 LEFT JOIN lookup l1
 ON data_to_fix.some_field1 LIKE l1.wrong
 LEFT JOIN lookup l2
 ON data_to_fix.some_field2 LIKE l2.wrong
 SET 
   data_to_fix.some_field1 = IFNULL(l1.right, data_to_fix.some_field1),
   data_to_fix.some_field2 = IFNULL(l2.right, data_to_fix.some_field2);

edited Dec 11, 2012 at 22:01

answered Dec 11, 2012 at 20:42

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

5 Comments

soft genic Over a year ago

so this technique will run in O(1)? cause i am dealing with ~50K rows and i have to lookup again and again..

Wrikken Over a year ago

If you need to update them permanently... Just run an UPDATE query under cron (I'll add the answer in), should be fast enough. If 'wrong' data keeps coming, a BEFORE INSERT / BEFORE UPDATE trigger in MYSQL itself may be in order, dispensing with the 'periodical' fix altogether.

soft genic Over a year ago

+1, your update is quite good, actually there are some strings in the fields like H. No. address... so from such string i have to replace the H. No. part to HNO ONLY so for that what should be the query in that case, also i am not dealing with one field i am dealing with 7 fields of the table so i would appreciate if you update the query accordingly (atleast 3 fields). Thanks

Wrikken Over a year ago

You can us a LIKE, so, in your wrong column there would be 'H. No.%', and the ON clause would be ON data_to_fix.some_field LIKE lookup.wrong. For every field you need a seperate JOIN on the lookup table, so use an alias for that.

Wrikken Over a year ago

Or you could run 7 seperate queries of course. I've altered the example with 2 columns instead of 3, because I don't like to be told what to do while helping out, but finding juuust enough sympathy to actually provide the answer rather then stopping right there and then.

Wing Lian · Accepted Answer · 2012-12-11 20:47:17Z

0

Try this. You'll also want to use PDO or mysqli as mysql_* will soon be gone...

<?php
function check_wrong_right($exist) {
    $lookup = mysql_query("select right from lookup WHERE wrong = '" . mysql_real_escape_string($exist). "'");
    if (mysql_num_rows($lookup) == 1) {
        $row = mysql_fetch_array($lookup);
        return $row['right'];
    }
    return $exist;
}

echo check_wrong_right('FSc');

answered Dec 11, 2012 at 20:47

Wing Lian

2,41818 silver badges14 bronze badges

Collectives™ on Stack Overflow

If in array replace with corresponding column

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related