CODEWITHSUNDEEP
phpmysql
Haroldo
Haroldo

Reputation: 37377

MySQL: Whole database, convert [empty] values to NULL

I'm working on a database where there is a mix of NULL and '' (empty) values. I would like to convert all empty values to NULL.

Something like

UPDATE table SET col=NULL WHERE col=''

but for the whole database

notes:

Upvotes: 2

Views: 718

Answers (3)

Haroldo
Haroldo

Reputation: 37377

This worked for me:

$tables = {array of table names};

foreach($tables as $t){
    $cols = FALSE;
    $q = "SELECT * FROM $t LIMIT 1";
    $res = mysql_query($q);
    while ($r = mysql_fetch_array($res, MYSQL_ASSOC)){    
        $cols = array_keys($r);
    }
    if($cols){
        foreach($cols as $c){
            $q2 = "UPDATE $t SET $c=NULL WHERE $c = ''";
            mysql_query($q2);
            echo mysql_error();
        }
    } 
}

Upvotes: 2

hummingBird
hummingBird

Reputation: 2555

There probably is a better solution, but you could always do a mysql dump of the base, and then parse the .sql file, replacing '' with NULL. For parsing, you could use custom made php script, or some other way - that part shouldn't be hard :)

Upvotes: 2

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

The quickest way might just be to dump, search/replace using a text editor, then load.

Upvotes: 1

Related Questions