Checking referential integrity of a database schema

Question

I am using PHP PDOs to parse the results I am receiving from MySQL queries against a database. I am now running into an issue with running out of allocated memory. Are there any suggestions on improving the efficiency of my code or another more efficient way to handle query results other than parsing PDOs?

This will return whether or not a DB has referential integrity:

function generateDSN($host, $dbname)
{
    return 'mysql:host='. $host . ';dbname=' . $dbname; 
}


$dbName = $argv[2];
//Used for query construction

$dsn = generateDSN($argv[1], $argv[2]); 
//create DSN

$link = new PDO($dsn, "username", "password"); 
//Connect to the database
//PDO($dsn, username, password)

if ($link->connect_error) 
{
    die("Connection failed: " . $link->connect_error);
}
else
{
    echo "Connected successfully\n";    
}
//validate database connection


$query = "SELECT TABLE_CONSTRAINTS.TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS RIGHT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME <> 'PRIMARY' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA = '".$dbName."';"; 
//Create query to return list of tables with foreign keys 
/* Query return format: 
+--------------+------------------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME            | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+------------------------+-----------------------+------------------------+
*/

$result = $link->query($query);
//Get query results 

$x = 0; 
$tableA = [];
$tableB = [];
//Create arrays to hold data from FK queries

while($tables = $result->fetch(PDO::FETCH_ASSOC))
{
    $test[] = $tables; 
    $assoc = $test[$x]; 
    //assign assoc[] the value of $query (will iterate through query table row by row)

    $tableName = $assoc['TABLE_NAME']; 
    $columnName = $assoc['COLUMN_NAME']; 
    $refTableName = $assoc['REFERENCED_TABLE_NAME'];
    $refColumnName = $assoc['REFERENCED_COLUMN_NAME']; 
    //get data values for each column

    $fkQueryA = "SELECT DISTINCT " . $columnName . " FROM " . $dbName . "." . $tableName . " ORDER BY ".$columnName." ;"; 
    $fkQueryB = "SELECT DISTINCT " . $refColumnName . " FROM " . $dbName . "." . $refTableName . " ORDER BY ".$refColumnName." ;"; 
    //A -- Table with column that is the foreign key
    //B -- Table with column that the foreign key references

    $resultA = $link->query($fkQueryA);
    $resultB = $link->query($fkQueryB);
    //Get query results 

    while($var = $resultA->fetchColumn())
    {   
        $tableA[] = $var;
    }
    //Push query results to table


    while($vari = $resultB->fetchColumn())
    {
        $tableB[] = $vari; 
    }
    //Push query results to table

    $x++;
    //increment counter to move through $tables 
}

$resultCompAB = array_diff($tableA, $tableB); 
//return array with all values of A that are not in B


if(empty($resultCompAB))
{
    echo "Database ".$dbName." has referential integrity."; 
} 
else
{
    echo "Orphan Values in database ".$dbName.": \n"; 
    array_diff($tableB, $resultCompAB); 
}
//print the results

Answer 1

This code does not work properly. Basically you build two enormous arrays containing all values of foreign keys in your database. That's probably a lot.

1: The $x and $test variables serve no purpose and should be removed.

2: The comparison of tablet A and B should be moved inside the while() loop.

I also suggest some unset's and closures of resources that are no longer needed.