- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
$db = new Db();
$db->prepare('SELECT `ID`, `Title`, `Date`, `Active` FROM `videos` WHERE `UserID` = :userId AND `is_temporary` = 0 ORDER BY `Date` DESC')
->bindInt(':userId', $userId)
->execute();
$dbResult = $db->fetchAll();
if (empty($dbResult)) return array();
$orderDir = strtoupper($orderDir);
if (!in_array($orderDir, array('DESC', 'ASC'))) $orderDir = 'DESC';
if (empty($filter) && $orderDir == 'DESC') return ArrayHelper::extract($dbResult, 'ID', ArrayHelper::INT);
$db->prepare('CREATE TEMPORARY TABLE `videos_tmp` (
`id` INT(11),
`title` VARCHAR(256),
`date` DATETIME,
`active` VARCHAR(1)
) ENGINE = MEMORY
CHARACTER SET utf8
COLLATE utf8_general_ci')
->execute();
foreach ($dbResult as $row) {
$db->prepare('INSERT INTO `videos_tmp` (`id`, `title`, `date`, `active`) VALUES (:id, :title, :date, :active)')
->bindInt(':id', $row['ID'])
->bindStr(':title', $row['Title'])
->bindStr(':date', $row['Date'])
->bindStr(':active', $row['Active'])
->execute();
}
$sql = 'SELECT `id` FROM `videos_tmp` WHERE 1';
if (isset($filter['active'])) $sql .= ' AND `Active` = :active';
if (isset($filter['search'])) $sql .= ' AND `title` LIKE :search';
if (isset($filter['fromDate'])) $sql .= ' AND `Date` >= :fromDate';
if (isset($filter['toDate'])) $sql .= ' AND `Date` <= :toDate';
$sql .= ' ORDER BY `Date` ' . $orderDir;
$stmt = $db->prepare($sql);
if (isset($filter['active'])) $stmt->bindStr(':active', $filter['active']);
if (isset($filter['search'])) $stmt->bindStr(':search', '%' . $filter['search'] . '%');
if (isset($filter['fromDate'])) $stmt->bindStr(':fromDate', $filter['fromDate']);
if (isset($filter['toDate'])) $stmt->bindStr(':toDate', $filter['toDate']);
$stmt->execute();
$dbResult = $db->fetchAll();
$db->prepare('DROP TABLE `videos_tmp`')->execute();
Извиняюсь, что так много, но какого слоника надо было делать выборку, создавать временную таблицу и делать выборку снова - загадка.
Follow us!