CREATE table `promos` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL,
`first_day` DATE NULL,
`last_day` DATE NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
;
INSERT INTO `promos` (`name`, `first_day`, `last_day`) VALUES (
'ON: no start, no end',
NULL,
NULL
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'OFF: no start, already ended',
NULL,
'2010-01-01'
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'ON: no start, not yet ended',
NULL,
'2050-01-01'
);
INSERT INTO `promos` (`name`, `first_day`, `last_day`) VALUES (
'ON: already started, no end',
'2000-01-01',
NULL
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'OFF: already started, already ended',
'2000-01-01',
'2010-01-01'
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'ON: already started, not yet ended',
'2000-01-01',
'2050-01-01'
);
INSERT INTO `promos` (`name`, `first_day`, `last_day`) VALUES (
'OFF: not yet started, no end',
'2040-01-01',
NULL
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'OFF: not yet started, already ended',
'2040-01-01',
'2010-01-01'
);
INSERT into `promos` (`name`, `first_day`, `last_day`) VALUES (
'OFF: not yet started, not yet ended',
'2040-01-01',
'2050-01-01'
);
INSERT into `promos` VALUES ();
ALTER TABLE `promos`
RENAME COLUMN `name` TO `label`;
SELECT *
FROM promos
WHERE (
first_day IS NULL
OR first_day <= CURRENT_DATE
) AND (
last_day IS NULL
OR last_day >= CURRENT_DATE
)
<?php
use Carbon\Carbon;
$inserts = [
[
'label' => '#1',
'first_day' => '2025-04-08',
'last_day' => NULL,
],[
'label' => '#2',
'first_day' => NULL,
'last_day' => '2025-04-08',
],[
'label' => '#2',
'first_day' => NULL,
]
];
$do_insert = array_to_prepared_insert($mysqli, 'promos', $inserts[0]);
foreach ($inserts as $i) $do_insert($i);
$results = $mysqli->query("SELECT * FROM promos");
while ($r = $results->fetch_assoc()) print_r($r);
function array_to_prepared_insert(mysqli $db, string $table, array $data)
{
$columns = array_keys($data);
$check_columns = fn($arr)=> array_reduce(
array_map(
fn($a,$b)=> $a === $b,
$columns,
array_keys($arr)
),
fn($a,$b)=> $a*$b,
1
);
$colString = join(', ', array_map(fn($a)=>"`$a`", $columns));
$valString = join(', ', array_map(fn($a)=>"?", $columns));
$stmt = $db->prepare("INSERT INTO `$table` ($colString) VALUES ($valString)");
return fn($insert_data)=> (
$check_columns($insert_data) or
new Exception("Insert structure mismatch")
) and (
$stmt->execute($insert_data) or
new Exception("Insert statement failed")
);
}