header('Content-Type: text/html; charset=utf-8');<meta charset="utf-8">ALTER DATABASE ... CHARACTER SET ... COLLATE ...select a.*
from (select m.*, u.*, count(select *
from messages as m2
join users as u2 on u2.id = m2.author
where m2.recipient = 1
and u2.id = u.id -- имитация клаузы partition by
and m2.id > m.id -- имитация клаузы order by ... desc
) as row_num, -- аналог row_number через оконную функцию:
-- row_number() over(partition by u.id order by m.id desc) as row_num
from messages as m
join users as u on u.id = m.author
where m.recipient = 1
) as a
where a.row_num = 1 -- берем 1 строку по ранжированному счетчику в пределах каждого id пользователяНо будет 2 вычисления MD5
where value = reverse_md5(:hache) if(isset($_POST['ref_button']) )
{
$send_ref = $_POST["send_ref"];
$stmt = msqli_prepare($con, "SELECT * FROM usertable WHERE referral= ?");
mysqli_stmt_bind_param($stmt, 's', $send_ref); // s - тип данных строка, i - число, если не тот тип данных - нужно исправить
$result = mysqli_stmt_execute($stmt);
if(mysqli_num_rows($result) > 0)
{
$stmt = msqli_prepare($con, "UPDATE usertable SET owner = ? WHERE email = ?");
mysqli_stmt_bind_param($stmt, 'ss', $send_ref, $email); // s - тип данных строка, i - число, если не тот тип данных - нужно исправить
$result = mysqli_stmt_execute($stmt);
}
}select psc.*,
pt1.`value` as `picture`,
pt2.`value` as `sort_pos`
from `pref_site_content` as psc
inner join `pref_site_tmplvar_contentvalues` as pt1 on pt1.`contentid` = psc.`id` and pt1.`tmplvarid` = 37
inner join `pref_site_tmplvar_contentvalues` as pt2 on pt2.`contentid` = psc.`id` and pt2.`tmplvarid` = 111
where psc.`id` not in (898, 899, 900, 902)
and (`template` = 40 and `published` = 1) -- тут, возможно, тоже можно добавить, если в таблицах поля одинаково названыSELECT distinct p1.`id`,
p1.`name`,
p1.`serial`,
case
when (select count(*)
from `products` as p2
join `spent` as s2 on p2.`id` = s2.`product`
and s2.`issued` = 1 -- какое значение в качестве некачественного продукта ?
where p2.`serial` = p1.`serial`) > 0
then 1
else 0
end as `spent`
from `products` as p1каким методом можно моментально/быстро найти нужную запись с данными?
В основную записываем допустим имя, а в сводную то что придёт в формате ключ - значение.
2. Сохранять данные в json
товары, корзины и пользователи
SELECT
posts.id,
posts.name,
count(case when s_vk.social_name = 'vk' then 1 end) as count_vk,
count(case when s_vk.social_name = 'tg' then 1 end) as count_tg,
count(case when s_vk.social_name = 'ok' then 1 end) as count_ok,
count(case when s_vk.social_name = 'tw' then 1 end) as count_tw
FROM posts
LEFT JOIN socials as s_vk on s_vk.post_id = posts.id
GROUP BY posts.id, posts.nameselect le.id, le.locationid, le.eventid,
led.event_date
from location_event le
join location_event_dates led on led.location_event_id = le.id
where led.event_date between :begin_date and :end_dateselect n.*
from news as n
join news_relation as nr on (:id = nr.news_id_1 and n.id = nr.news_id_2) -- выявляем связь в одну сторону
or (:id = nr.news_id_2 and n.id = nr.news_id_1) -- выявляем связь в другую сторону
order by n.id desc