Joseph Webber Joseph Webber - 2 months ago 9
MySQL Question

How can I search all records from all tables in a database for dates that contain the current month?

I have a database named

db_contacts
, and it contains tables for each account to store their individual contacts. Each table is formatted the same way, with one date column being called
birthday
.

What I want to be able to do is search every record from every table in
db_contacts
for the dates whose month is equal to the current month.

Is there a way to do this using only one query, or will I have to query each table separately? I'm really at a loss for how to do this.

I was thinking something like this, but this doesn't work at all:

$connection = new PDO("mysql:host=localhost", "foo", "bar");

$month = date("m");

$db = $connection->prepare("
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'birthday'");
$db->execute();

foreach ($db as $table) {
for ($i = 0; $i < sizeof($table); $i++) {
$db = $connection->prepare("
SELECT birthday FROM $table[$i] WHERE MONTH('birthday') = $month;");
$db->execute();

foreach ($db as $row) {
echo $row[0];
}
}
}

Answer

I finally got it working by using the following:

$db = $connection->prepare("
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE 'birthday'");
$db->execute();

$month = date("m");

foreach ($db as $table) {
    $db = $connection->prepare("
        SELECT birthday 
        FROM db_contacts.$table[0] 
        WHERE MONTH(birthday) = $month;");
    $db->execute();

    foreach ($db as $record) {
        echo $record[0];
    }
}
Comments