MySQL Cross database foregin keys

Here is a small query to display all foreign keys across different databases. May come in very handy when deciding which database to restore first :)

USE information_schema;

SELECT * 
    FROM 
        KEY_COLUMN_USAGE 
    WHERE 
        CONSTRAINT_SCHEMA != REFERENCED_TABLE_SCHEMA;

CakePHP locking tables

Here are my two cents on the issue.

The code below is a function from a behaviour that tries to create an additional unique key on a field named code, by counting the number of records created this year. The important part in the locking procedure is that we must specify the AS clause in the LOCK TABLES statement or otherwise the $model->find() function will not work complaining that the table is locked.

    public function getNextCode(&$model)
    {
        $thisYear = date('Y');
        $dbo = $model->getDataSource();
        $dbo->execute(
            sprintf('LOCK TABLES %s AS %s WRITE;',
                $model->table,
                $model->alias
            )
        );
        $recordsThisYear = $model->find(
            'count',
            array(
                'recursive' => -1,
                'conditions' => array(
                    $model->alias .'.code LIKE' => $thisYear.'%'
                )
            )
        );
        $dbo->execute('UNLOCK TABLES');
        return sprintf('%d-%06d', $thisYear, $recordsThisYear + 1);
    }

The original idea for the post and function cake from a doWeb posting available through here.

PHP: Number of rows from query

The idea comes from the developers of Oracle ADF, I have seen it work on JDeveloper and I thought that I can use it in PHP. The trick here is to place the entire query inside a SELECT count(*) from ($query) statement.

Once you know what to do implementation is as easy as witting the code below. The only comment is that the function references an already initialized external mysqli class variable that connects to the database, which if you feel like it may also be passed as a function parameter.

    /**
      * returns the number of records  of the query passed as parameter 
      */
    function numRecordsOfQuery( $a_query) 
    {
        global $mysqli;

        $count_query = " 
                   SELECT count(*) as TotalLines 
                     FROM ($a_query) MyQuery";
        $result = $mysqli->query($count_query);
        // echo $count_query;
        $row = $result->fetch_array( MYSQLI_ASSOC);
        $numRecords = $row['TotalLines'];
        $result->close();
        
        return $numRecords;
    }

Note The MyQuery references is mandatory. MySQL will return an error if the enclosed query is not referenced.