<?php
function get_categoies($pdo)
{
$stmt = $pdo->prepare("SELECT * FROM `categories`");
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
function get_prices_by_category($pdo, $category_id)
{
$stmt = $pdo->prepare("SELECT * FROM `prices` WHERE category_id = ?");
$stmt->execute([$category_id]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$categories = get_categoies($pdo);
foreach ($categories as $category) {
echo '<h3>'.$category['title'].'</h3>
<div class="cblock">';
$prices = get_prices_by_category($pdo, $category['id']);
foreach ($prices as $price) {
echo '<div class="pricelist-row">
<div class="service">' . $price["title"] . '</div>
<div class="price">от ' . $price["price"] . ' руб/' . $price["type"] . '</div>
</div>';
}
echo '</div>';
}SELECT user_id, count(distinct created)
FROM orders
WHERE created > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) -- get last 30 day orders
GROUP BY user_id
HAVING count(distinct created) = 30 -- check user have orders in 30 different days
;select
*
from
orders
where
exists (
select
1
from
order_product
where
order_id = orders.id
and importance = 0
);SELECT * FROM texttable
ORDER BY name REGEXP '^[А-Яа-я]' DESC, name;SELECT
task.id,
task.dc,
task.provider,
task.type,
task.url,
task.cost,
task.cost_bot,
task.last_start_msec,
task.url_id,
task.remains,
task.cat
FROM
Task task
WHERE
task.status = 'active'
AND task.type = 'follow_profile'
AND task.provider = 'insta'
AND task.cat = 3
AND NOT EXISTS(
SELECT 1 FROM TaskFlow flow
WHERE
flow.url_id = task.url_id
AND flow.executor_acc_id = $acc
);create table tbl(id int primary key auto_increment, lang varchar(2));
insert into tbl(lang) values
('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'),
('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU'), ('EN'),('ES'),('RU');
with data as (
select
id, lang,
row_number() over (partition by lang order by rand()) rn
from tbl
)
select id, lang from data
where rn < 11;create table class (
id int primary key auto_increment,
name varchar(64) unique key
);
create table subclass (
id int primary key auto_increment,
class_id int references class(id),
name varchar(64) unique key
);
create table items (
id int primary key auto_increment,
subclass_id int references subclass(id),
name varchar(64) unique key
);
insert into class (name) values ('Fruits'), ('Vegetables');
insert into subclass (class_id, name) values (1, 'Red'), (1, 'Orange'), (2, 'Green'), (2, 'Yellow');
insert into items (subclass_id, name) values
(1, 'Apple'), (2, 'Pineapple'), (3, 'Cucumber'), (3, 'Carrot');<?php
$query = 'select class.name class, subclass.name subclass, items.name item
from items
join subclass on items.subclass_id = subclass.id
join class on subclass.class_id = class.id;';
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$res = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if (!isset($res[$row["class"]])) {
$res[$row["class"]] = [];
}
if (!isset($res[$row["class"]][$row["subclass"]])) {
$res[$row["class"]][$row["subclass"]] = [];
}
$res[$row["class"]][$row["subclass"]][] = $row["item"];
}
print_r(json_encode($res, JSON_PRETTY_PRINT));Вы определили колонку логин в базе данных как INTEGER (Целое число), но пытаетесь запихнуть туда строку
PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect integer value: 'Timpixel2020' for column 'login' at row 1 in C:\OpenServer\domains\localhost\elements\signup.php:36
CREATE TABLE foo (
id VARCHAR(64) DEFAULT (uuid()) PRIMARY KEY,
val varchar(16)
);
INSERT INTO foo (val) VALUES ('Test');
SELECT * FROM foo;CREATE TABLE foo (
id VARCHAR(64) primary key,
val varchar(16)
);
INSERT INTO foo (id, val) VALUES (uuid(), 'Test');
SELECT * FROM foo;delete from news where description like 'Мы печем лучший % хлеб в стране.';SELECT *
FROM table_1 T1
LEFT JOIN table_2 T2 ON JSON_CONTAINS(
array_id,
CAST(T2.`id` AS JSON),
'$'
);SELECT
author,
count(*) books_count
FROM books
GROUP BY author
ORDER BY books_count DESC
;-- using window functions
select distinct
uid, first_value(utm) over (partition by uid order by id desc) last_utm
from t;
-- using join
select t.uid, t.utm last_utm
from t
join (
select max(id) max_id from t group by uid
) max_ids on max_id = id;
-- using sub-query
select t.uid, t.utm last_utm
from t
where id = (select max(id) max_id from t group by uid having max(id) = t.id);select items.id, items.name, group_concat(img) images
from items
join item_images on items.id = item_images.item_id
join images on images.id = item_images.image_id
group by items.id, items.name;select
_date,
sum(case when _ustr = 1 then _metr else 0 end) _ustr_1,
sum(case when _ustr = 2 then _metr else 0 end) _ustr_3,
sum(case when _ustr = 3 then _metr else 0 end) _ustr_3,
sum(case when _ustr = 4 then _metr else 0 end) _ustr_4,
sum(case when _ustr = 5 then _metr else 0 end) _ustr_5
from tbl
group by _date;select medialib.id, category.name
from medialib
join category on category.id = medialib.catalog;WITH records AS (
SELECT
a.title,
s.date,
t.status,
ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY s.date DESC) rn
FROM adverts a
LEFT JOIN sys_advert_statuses s ON a.id = s.ad_id
LEFT JOIN sys_advert_status_types t ON s.status_id = t.id
WHERE
s.status_id IN (3, 5, 6)
AND a.id = 4
) SELECT title, date, status FROM records WHERE rn = 1;