#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    Find all typos in mysql database


    Any easy way to find all typos in a MySQL database?
    Tanx
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,887
    Rep Power
    9646
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    Hire a proof readrer.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by Barand
    Hire a proof readrer.
    classic

    Comments on this post

    • salem agrees
    • benanamen agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by English Breakfast Tea
    Any easy way to find all typos in a MySQL database?
    Tanx
    I came up with the following a while back to check for reserved words and the like.

    Code:
    <?php
    if ($_SERVER['REQUEST_METHOD'] == 'POST' && !empty($_POST['database'])) {
    
        $db=parse_ini_file(__DIR__.'/../config.ini',true)['mysql'];
        $pdo=new PDO("mysql:host={$db['host']};dbname={$db['dbname']};charset={$db['charset']}",$db['username'],$db['password'],array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_OBJ));
    
        $error=['tableHelper'=>[],'columnHelper'=>[],'tableMdbReserved'=>[],'columnMdbReserved'=>[],'tableMdbException'=>[],'columnMdbException'=>[]];
        $stmt=$pdo->query('SELECT name FROM mysql.help_keyword');
        $reserved=$stmt->fetchAll(PDO::FETCH_COLUMN);
        $mdbReserved=['ACCESSIBLE','ADD','ALL','ALTER','ANALYZE','AND','AS','ASC','ASENSITIVE','BEFORE','BETWEEN','BIGINT','BINARY','BLOB','BOTH','BY','CALL','CASCADE','CASE','CHANGE','CHAR','CHARACTER','CHECK','COLLATE','COLUMN','CONDITION','CONSTRAINT','CONTINUE','CONVERT','CREATE','CROSS','CURRENT_DATE','CURRENT_TIME','CURRENT_TIMESTAMP','CURRENT_USER','CURSOR','DATABASE','DATABASES','DAY_HOUR','DAY_MICROSECOND','DAY_MINUTE','DAY_SECOND','DEC','DECIMAL','DECLARE','DEFAULT','DELAYED','DELETE','DESC','DESCRIBE','DETERMINISTIC','DISTINCT','DISTINCTROW','DIV','DOUBLE','DROP','DUAL','EACH','ELSE','ELSEIF','ENCLOSED','ESCAPED','EXISTS','EXIT','EXPLAIN','FALSE','FETCH','FLOAT','FLOAT4','FLOAT8','FOR','FORCE','FOREIGN','FROM','FULLTEXT','GENERAL','GRANT','GROUP','HAVING','HIGH_PRIORITY','HOUR_MICROSECOND','HOUR_MINUTE','HOUR_SECOND','IF','IGNORE','IGNORE_SERVER_IDS','IN','INDEX','INFILE','INNER','INOUT','INSENSITIVE','INSERT','INT','INT1','INT2','INT3','INT4','INT8','INTEGER','INTERVAL','INTO','IS','ITERATE','JOIN','KEY','KEYS','KILL','LEADING','LEAVE','LEFT','LIKE','LIMIT','LINEAR','LINES','LOAD','LOCALTIME','LOCALTIMESTAMP','LOCK','LONG','LONGBLOB','LONGTEXT','LOOP','LOW_PRIORITY','MASTER_HEARTBEAT_PERIOD','MASTER_SSL_VERIFY_SERVER_CERT','MATCH','MAXVALUE','MEDIUMBLOB','MEDIUMINT','MEDIUMTEXT','MIDDLEINT','MINUTE_MICROSECOND','MINUTE_SECOND','MOD','MODIFIES','NATURAL','NOT','NO_WRITE_TO_BINLOG','NULL','NUMERIC','ON','OPTIMIZE','OPTION','OPTIONALLY','OR','ORDER','OUT','OUTER','OUTFILE','PARTITION','PRECISION','PRIMARY','PROCEDURE','PURGE','RANGE','READ','READS','READ_WRITE','REAL','RECURSIVE','REFERENCES','REGEXP','RELEASE','RENAME','REPEAT','REPLACE','REQUIRE','RESIGNAL','RESTRICT','RETURN','REVOKE','RIGHT','RLIKE','ROWS','SCHEMA','SCHEMAS','SECOND_MICROSECOND','SELECT','SENSITIVE','SEPARATOR','SET','SHOW','SIGNAL','SLOW','SMALLINT','SPATIAL','SPECIFIC','SQL','SQLEXCEPTION','SQLSTATE','SQLWARNING','SQL_BIG_RESULT','SQL_CALC_FOUND_ROWS','SQL_SMALL_RESULT','SSL','STARTING','STRAIGHT_JOIN','TABLE','TERMINATED','THEN','TINYBLOB','TINYINT','TINYTEXT','TO','TRAILING','TRIGGER','TRUE','UNDO','UNION','UNIQUE','UNLOCK','UNSIGNED','UPDATE','USAGE','USE','USING','UTC_DATE','UTC_TIME','UTC_TIMESTAMP','VALUES','VARBINARY','VARCHAR','VARCHARACTER','VARYING','WHEN','WHERE','WHILE','WITH','WRITE','XOR','YEAR_MONTH','ZEROFILL'];
        $mdbExceptions=['ACTION','BIT','DATE','ENUM','NO','TEXT','TIME','TIMESTAMP'];
    
        $stmt=$pdo->prepare('SELECT UPPER(TABLE_NAME) tn, UPPER(COLUMN_NAME) cn from information_schema.columns WHERE table_schema = ?');
        $stmt->execute([$_POST['database']]);
        while($rs=$stmt->fetch()) {
            if(in_array($rs->tn,$reserved)) {
                $error['tableHelper'][]=$rs->tn;
            }
            if(in_array($rs->cn,$reserved) && !in_array($rs->cn,$error['columnHelper'])) {
                $error['columnHelper'][]=$rs->cn;
            }
            if(in_array($rs->tn,$mdbReserved)) {
                $error['tableMdbReserved'][]=$rs->tn;
            }
            if(in_array($rs->cn,$mdbReserved) && !in_array($rs->cn,$error['columnMdbReserved'])) {
                $error['columnMdbReserved'][]=$rs->cn;
            }
            if(in_array($rs->tn,$mdbExceptions)) {
                $error['tableMdbException'][]=$rs->tn;
            }
            if(in_array($rs->cn,$mdbExceptions) && !in_array($rs->cn,$error['columnMdbException'])) {
                $error['columnMdbException'][]=$rs->cn;
            }
        }
        echo('<pre>'.print_r($error,1).'</pre>');
    }
    else {
        echo <<<EOT
    <form method="post">
      Database Name: <input type="text" name="database"><br>
      <input type="submit">
    </form>
    EOT;
    }

IMN logo majestic logo threadwatch logo seochat tools logo