Take the 2-minute tour ×

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 )


$lookup = mysql_query("select * from lookup");
while($getlookup = mysql_fetch_array($lookup))
$find[] = $getlookup; 
share|improve this question
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 Dec 11 '12 at 20:39
@MikeBrant Yes you are right –  soft genic Dec 11 '12 at 20:41
add comment

3 Answers

up vote 1 down vote accepted

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_field (the field you are trying to change)
... some other fields


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.

share|improve this answer
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 Dec 11 '12 at 20:46
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... –  soft genic Dec 11 '12 at 20:48
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 Dec 11 '12 at 21:03
Kindly take a look at my updated question –  soft genic Dec 11 '12 at 21:16
@softgenic Take a look at revised answer. –  Mike Brant Dec 11 '12 at 21:58
show 2 more comments

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
   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);
share|improve this answer
so this technique will run in O(1)? cause i am dealing with ~50K rows and i have to lookup again and again.. –  soft genic Dec 11 '12 at 21:18
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. –  Wrikken Dec 11 '12 at 21:28
+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 –  soft genic Dec 11 '12 at 21:45
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 Dec 11 '12 at 21:58
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. –  Wrikken Dec 11 '12 at 22:03
add comment

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

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');
share|improve this answer
add comment

Your Answer


