CODEWITHSUNDEEP
php
soft genic
soft genic

Reputation: 2056

If in array replace with corresponding column

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);

Upvotes: 0

Views: 116

Answers (3)

Wrikken
Wrikken

Reputation: 70460

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);

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

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.

Upvotes: 1

Wing Lian
Wing Lian

Reputation: 2418

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');

Upvotes: 0

Related Questions