select
m.match_id,
min(opponent_id) as op1, max(opponent_id) as op2
from match_opponents op
inner join matches m on op.match_id = m.match_id
where m.status = "started"
group by m.match_id;
<?php
function split_name($name) {
return mb_ereg_replace_callback(
'([а-яa-z])([А-ЯA-Z])',
function($m) {
return $m[1] . ' ' . $m[2];
},
$name
);
}
echo split_name('Семенова ИринаВикторовна') . PHP_EOL;
echo split_name('Иванова ОльгаВикторовна') . PHP_EOL;
echo split_name('InessaIvanovna Oliynichenko') . PHP_EOL;
$result = mysqli_query($link, "SELECT acc_get_current_balance('@account_id') AS current_balance");
while ($row = mysqli_fetch_assoc($result)) {
echo "Current Balance : {$row['current_balance']} <br>";
}
with recursive free_icons (id) as (
select 1
union all
select id+1 from free_icons where id <= 40
)
select * from free_icons
where not exists (
select icon_id from icons where icon_id = free_icons.id
)
limit 1;
select icons.icon_id + 1
from icons
left join icons as next_icon on icons.icon_id + 1 = next_icon.icon_id
where next_icon.icon_id is null and icons.icon_id between 2 and 40
limit 1;
create table users (
id int primary key auto_increment
);
create table books (
id int primary key auto_increment,
created timestamp,
index(created)
);
create table read_books (
user_id int,
book_id int,
primary key (user_id, book_id)
);
select * from books
where UNIX_TIMESTAMP() - created < 600 and
not exists (
select book_id
from read_books
where read_books.book_id = books.id and read_books.user_id = :user_id
)
limit 1;
SELECT
expire_at,
last_check,
DATEDIFF(expire_at, last_check)
FROM tbl
WHERE DATEDIFF(expire_at, last_check) < 7;
<?php
$update = 'UPDATE `bes`
SET
`many` = IF (`many` >= :cena, `many` - :cena, `many`),
`activation` = IF (`many` >= :cena, 1, 0)
WHERE `activation` = 1';
$stmt = $pdo->prepare($update);
$stmt->execute([':cena'=>3]);
<?php
$query = "SELECT MAX(id) AS max FROM test";
$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$max_id = $row['max'];
$query = "SELECT * FROM test WHERE id >= ? LIMIT 1";
$stmt = $pdo->prepare($query);
$stmt->execute([rand(0, ($max_id-1))]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
SELECT user_id
FROM some_table
WHERE num IN (878, 925, 242)
GROUP BY user_id
HAVING COUNT(DISTINCT num) = 3 -- 3 количество вариантов
;
SELECT some_table.user_id
FROM some_table
JOIN some_table st1 ON st1.user_id = some_table.user_id AND st1.num = 925
JOIN some_table st2 ON st2.user_id = some_table.user_id AND st2.num = 242
WHERE some_table.num = 878
;
SELECT user_id, GROUP_CONCAT(DISTINCT num ORDER BY num)
FROM some_table
WHERE num IN (878,925,242)
GROUP BY user_id
HAVING GROUP_CONCAT(DISTINCT num) = '242,878,925'
;
SELECT `question`.*
FROM `question`
WHERE
`question`.`deleted` = 0 AND
`question`.`moderated` = 0 AND
`question`.`published_date` > 0 AND
NOT EXISTS (SELECT `id` FROM `comment` WHERE `question`.`id` = `comment`.`material_id`)
ORDER BY `question`.`published_date` DESC
LIMIT 15;
<?php
$sql = "SELECT id FROM chatsystem WHERE user1 = ? or user2 = ?";
$stmt = mysqli_stmt_init($connect);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, "ss", $username, $username);
mysqli_stmt_execute($stmt);
$resultData = mysqli_stmt_get_result($stmt);
mysqli_stmt_close($stmt);
while ($row = mysqli_fetch_assoc($resultData)) {
var_export($row);
}
<?php
$request = [
87=>'AMD',
86=>'8'
];
$filter = implode(' or ', array_fill(0, count($request), '(pv.property_id = ? and pv.value = ?)'));
$filter_values = [];
foreach($request as $key=>$val) {
$filter_values[] = $key;
$filter_values[] = $val;
}
$query = 'select p.* from products p
join property_values pv on p.id = pv.product_id
where ' . $filter . '
group by p.id, p.name
having count(distinct pv.id) = ' . count($request);
SELECT MAX(fieldname) FROM table;
SET @t = '528223,528224';
SELECT * FROM t
WHERE FIND_IN_SET (t.id, @t );
INSERT INTO aqf_news (
`title_az`, `date`, `short_text_az`, `full_text_az`, `category_id`, `gallery`, `gallery_narative_az`, `video1`, `show_slider`, `info_source`
) VALUES (
'test', NOW(), 'short_text_az', 'full_text_az', 'category_id', 'gallery', 'gallery_narative_az', 'video1', COALESCE(show_slider, 0), 'info_source'
)
select
* ,
case
when role = 'boss' then 3
when role = 'admin' then 2
else 1
end as weight
from gamers order by weight desc;
select *
from gamers
order by role = 'boss' desc, role = 'admin' desc;