Slava Rozhnev

$stmt = $mysqli->prepare("UPDATE req 
	SET balance = balance - ? 
	WHERE card =? AND code = ? ;");
$stmt->bind_param("sss", $cost, $card, $code);

// set parameters and execute
$cost = $_GET['cost'];
$card = $_POST['card'];
$code = $_POST['code'];

$stmt->execute();

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00'
AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND `v`.`office` = 131

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND  EXISTS (SELECT 1 FROM  `visits` `v` WHERE  `p`.`visit` = `v`.`id` AND  `v`.`office` = 131)

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type`  = 'cash'
        AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type` =  'cashless'
        AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type`  =  'advance'
        AND `v`.`office` = 131

SELECT userid
FROM tbl 
GROUP BY userid
HAVING COUNT(userid) = 1;

select u.name, sum(amount)
from account a
join user u on u.id = a.user_id
join operation o on o.account_id = a.id
where a.currency = 'EUR'
group by u.id, u.name
having sum(amount) > (
	select avg(amount)
  	from account a
	join operation o on o.account_id = a.id 
	where a.currency = 'EUR'
)

select name, avg(rate) rate
from users u
left join replies r on r.user_id = u.id  
group by u.name
order by rate desc

SELECT 
	`tokens`.* 
FROM `tokens` 
ORDER BY REPLACE(SUBSTRING_INDEX(s_short,';',3), SUBSTRING_INDEX(s_short,';',2), '') DESC;

SELECT 
  `tokens`.* 
FROM `tokens` 
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(s_short,';',3),';',-1) DESC;

SELECT posts.id_post, posts.text_post, posts.photo, posts.created_at, 
users.id_user, users.login, users.name, users.surname, users.avatar
COALESCE(COUNT(`likes`.`id_post`), 0) AS `likes`
FROM `posts` 
INNER JOIN `users_connections` ON posts.id_user = users_connections.id_followed 
INNER JOIN `users` ON users.id_user = posts.id_user
LEFT JOIN `likes` ON `likes`.`id_post` = `posts`.`id_post`
 WHERE users_connections.id_follower = :id_follower 
AND `created_at` BETWEEN '$prewweek' AND '$nowdate' ORDER BY `created_at`
GROUP BY posts.id_post, posts.text_post, posts.photo, posts.created_at, 
users.id_user, users.login, users.name, users.surname, users.avatar

CREATE TABLE book (
    id INTEGER PRIMARY KEY, 
    title VARCHAR(128), 
    year INTEGER, 
    author_id INTEGER, 
    FOREIGN KEY(author_id) REFERENCES author(id)
);

SELECT 
	`messages`.`id` AS `messages_id`, `money_box`.`id`  AS `money_box_id`
FROM `messages`,`money_box`;

select 
	row.user_id, count(*) matches_row_count
from users user_row
join users row on 
	(user_row.a_id = row.a_id) and 
	(user_row.b_id = row.b_id) and 
	(user_row.id != row.user_id)
where user_row.user_id = 1
group by row.user_id;