Hi! Could we please enable some services and cookies to improve your experience and our website?
Online Sandbox for SQL and PHP: Write, Run, Test, and Share SQL Queries and PHP Code
CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` int(10) unsigned NOT NULL,
`customer_id` int(10) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` int(10) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `rental` ADD INDEX `idx_return_date_func` ((DATE_FORMAT(return_date, '%Y-%m')));
show indexes from rental where Key_name = 'idx_return_date_func';
explain select * from rental where DATE_FORMAT(return_date, '%Y-%m') = '2025-12';
<?php
$query = "show indexes from rental where Key_name = 'idx_return_date_func';";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_NUM);
$columns = [];
for ($i = 0; $i < $stmt->columnCount(); $i++) {
$col = $stmt->getColumnMeta($i);
$columns[] = [
'header'=>$col['name'],
'pdo_type'=>$col['pdo_type']
];
}
echo json_encode([[
'headers' => $columns,
'data' => $rows
]],
JSON_PRETTY_PRINT
);