<?php
function getOne($conn) {
$result = mysqli_query($conn,"
SELECT *
FROM `stat`
JOIN `order` ON `order`.`id` = `stat`.`order_id`
WHERE `delivery`='Почта' AND `cost`='0'"
);
if (mysqli_num_rows($result)>0) {
while ($row = mysqli_fetch_assoc($result)) {
echo json_encode($row);
}
}
else {
echo "Ошибка в getOne";
}
mysqli_close($conn);
}$result = mysqli_query($conn, "SELECT * FROM `country` WHERE `city`='Москва'");
$row = mysqli_fetch_assoc($result);
printf('Country ID is: %s', $row['id']);select *
from events
where
date > current_timestamp - interval '1 hour'
and type in ('open', 'open_page_1')
and not exists (
select type from events e
where e.operation_id = events.operation_id
and type not in ('open', 'open_page_1')
)
;$query = "insert into tbl (name) values (?);";
$stmt = $pdo->prepare($query);
$stmt->execute(['jfjd']);
print 'lastInsertId: ' . $pdo->lastInsertId();SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'image_src', images.image_src,
'image_alt', images.image_alt
)
) image,
product.*
FROM product
LEFT JOIN images ON images.prod_id = product.id
GROUP BY
product.id,
product.name;create table questions (
id int primary key auto_increment,
question varchar(255)
);
create table question_options (
id int primary key auto_increment,
question_id int,
answer varchar(255)
);
select
q.question,
json_arrayagg(answer) answers
from questions q
join question_options qo on qo.question_id = q.id
group by q.id, q.question
;+=============+============================+
| question | answers |
+=============+============================+
| Question 1? | ["Answer 1?", "Answer 2?"] |
+-------------+----------------------------+
| Question 2? | ["Answer 3?", "Answer 4?"] |
+-------------+----------------------------+select book_code, count(distinct bilet_number) lends
from lends
group by book_code
having count(distinct bilet_number) = (select count(distinct bilet_number) from abonents)select
i.name,
min(ru) filter (where fi.id = 218) key1,
min(ru) filter (where fi.id = 219) key2,
min(ru) filter (where fi.id = 220) key3
from items i
left join item_fields fi on i.id = fi.item_id and fi.id in (218, 219, 220)
group by i.name, item_id+=======+========+========+========+
| name | key1 | key2 | key3 |
+=======+========+========+========+
| Item1 | Поле 1 | Поле 2 | Поле 3 |
+-------+--------+--------+--------+SELECT `e`.*
FROM `event` `e`
LEFT JOIN `event` ON `e`.`event_id` = `event`.`id`
ORDER BY `e`.`position` = 0, `e`.`position`
LIMIT 10;ORDER BY IFNULL(`e`.`position`, 0) = 0, `e`.`position`SELECT * FROM (
SELECT
IDBrewery,
ProductCode,
ProductPrice,
PeriodDate,
ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY PeriodDate DESC) RN
FROM Price
JOIN Period ON Period.PeriodID = Price.PeriodID
) LastPrice
WHERE RN = 1
;SELECT
device_name,
COUNT(*) cnt
FROM users
WHERE
YEAR(created_date) = 2020 AND
year_of_birth < DATE_SUB(CURDATE(), INTERVAL 25 YEAR)
GROUP BY device_name;PRIMARY KEY AUTO_INCREMENT для поля `id`, и пофиксить определение поля `text`CREATE TABLE `textlog` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`chat_id` int(111) NOT NULL,
`text` text NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;UPDATE tbl
SET link = CONCAT(
'<p>[playerjs file:\"',
MID(LEFT(link, LOCATE('?', link, LOCATE('src="', link))-1), LOCATE('src="', link)+5),
'\"]</p>'
);select posts.id, posts.body, sum(
case direction
when 'UP' then 1
when 'DOWN' then -1
else 0
end) likes
from posts
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;select posts.id, posts.body, (
count(nullif(direction, 'DOWN')) - count(nullif(direction, 'UP'))) likes
from posts
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;select * from tbl
where from_id + to_id = 20 + 32 and abs(from_id - to_id) = abs(20 - 32);select tbl1.*
from tbl tbl1
join tbl tbl2 on tbl1.num1 = tbl2.num2 and tbl1.num2 = tbl2.num1
where tbl2.id < tbl1.id
;+====+======+======+
| id | num1 | num2 |
+====+======+======+
| 6 | 30 | 10 |
+----+------+------+ADD COLUMN IF NOT EXISTSреализован только в MariaDB, но не существует в MySQL 5-8.
SELECT VERSION();
SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
LEFT JOIN `photo` ON `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
WHERE `article_id` IS NULL
GROUP BY `article`.`id`SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
WHERE NOT EXISTS (
SELECT `article_id`
FROM `photo`
WHERE `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
);select
users.id,
users.name,
sum(money.value) as user_value
from users
join money on users.id = money.user_id
where value > 0
group by users.id, users.name
order by user_value desc;