DELETE FROM table2
WHERE NOT EXISTS (SELECT id FROM table1 WHERE table2.id = table1.id);SELECT city.name
FROM city
WHERE EXISTS (
SELECT city FROM ad_cars WHERE ad_cars.city = city.name
);<?php
function param_val_add($pdo, $param_id, $obj_id, $value) {
if ($value) {
$query =
"INSERT INTO vals (
`param_id`, `obj_id`, `val`, `is_del`
) VALUES (
:param_id, :obj_id, :value, 0
) ON DUPLICATE KEY UPDATE
`is_del` = VALUES(is_del),
`val` = VALUES(val)";
$stmt = $pdo->prepare($query);
return $stmt->execute([
':param_id' => $param_id,
':obj_id' => $obj_id,
':value' => $value
]);
} else {
$query = "UPDATE vals SET `is_del` = 1 WHERE param_id = :param_id AND obj_id = :obj_id";
$stmt = $pdo->prepare($query);
return $stmt->execute([
':param_id' => $param_id,
':obj_id' => $obj_id
]);
}
}
param_val_add($pdo, 1, 1, 'value') ;<?php
$data = [
['a' => 1, 'b' => 2, 'c' => 3],
['a' => 1, 'b' => 2, 'c' => 3],
['a' => 1, 'b' => 2, 'c' => 3],
['a' => 1, 'b' => 2, 'c' => 3],
['a' => 1, 'b' => 2, 'c' => 3]
];
$query="INSERT INTO t (a, b, c) VALUES " . implode(
', ',
array_fill(
0,
count($data),
'(' . implode (', ', array_fill(0, count($data[0]), '?')) . ')'
)
);
echo $query;
$values = array_reduce(
$data,
function($ac, $el) {
return array_merge($ac, array_values($el));
},
[]
);
var_export($values);
$q=$pdo->prepare($query);
$q->execute($values);<?php
$phone = '1234567';
$sql = "INSERT INTO sportusers (userdate, phone)
SELECT NOW(), :userphone
WHERE NOT EXISTS (
SELECT 1 FROM sportusers s2
WHERE s2.phone = :userphone
AND userdate > date_sub(now(), interval 12 HOUR)
)";
$stmt = $pdo->prepare($sql);
$res = $stmt->execute([':userphone' => $phone]);
if($stmt->rowCount() > 0 ){
echo "Запись сделана";
} else {
echo "Try later";
}select
o.id, o.name, o.price,
group_concat(case when attrib_name = 'цвет' then attrib_value end) color,
group_concat(case when attrib_name = 'размер' then attrib_value end) size,
group_concat(case when attrib_name = 'дата' then attrib_value end) date
from orders o
left join order_attributes oa on o.id = oa.order_id
group by o.id, o.name, o.price;SELECT *
FROM tbl
ORDER BY ...
LIMIT 40, 10SELECT *
FROM tbl
ORDER BY ...
LIMIT 10 OFFSET 40select *
from blocks
join (
select min(b.id) id
from blocks b
join links l on b.link_id = l.id
where l.id = 1
) fb on fb.id = blocks.id;CREATE TABLE species (
species_id INTEGER PRIMARY KEY,
type_id INTEGER,
species_name VARCHAR(255) NOT NULL,
species_amount INTEGER,
date_start DATE,
species_status ENUM ('active', 'absent', 'fairy') NOT NULL DEFAULT 'active'
);CREATE TABLE species_statuses (
id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
INSERT INTO species_statuses VALUES (1, 'active'), (2, 'absent'), (3, 'fairy');
CREATE TABLE species (
id INTEGER PRIMARY KEY,
type_id INTEGER,
name VARCHAR(255) NOT NULL,
amount INTEGER,
date_start DATE,
status_id INTEGER DEFAULT 1,
FOREIGN KEY (status_id) REFERENCES species_statuses(id)
);WITH countries_links AS (
SELECT JSON_OBJECT('name', `name`, 'iso2', `iso2`, 'links', COUNT(`link_id`)) d
FROM `links`
JOIN `countries` ON `countries`.`iso2` = `links`.`country`
GROUP BY `name`, `iso2`
HAVING COUNT(`link_id`) > 0
) SELECT JSON_ARRAYAGG(d) FROM countries_linksinsert into products (product_id, name, variant, price)
select
coalesce (
min(case when name = 'мышь' then product_id end ),
coalesce(min(product_id), 0) + 1
) product_id
,'мышь', 'красная', 150
from products;SELECT * ....
EXCEPT
SELECT * ....;$sql = "INSERT INTO `users` (`phone`,`name`,`fio`,`user_email`,`user_password`,`activity`,`twath`,`actvml`,`vmlstr`,`code`,`group`,`city`,`DateReg`,`LastEnter`,`user_ip`,`user_login`,`skin`,`sex`,`money`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$params = [$phone, $name, '-', $mail, $pasw, 1, 0, 0, 1, $code, 1, $city, $dreg, $dreg, $ip, $login, 0, $sex, 0];
$stmt->bind_param("ssssssssssssssssssi", ...$params);
$res = $stmt->execute();select city,
json_object(
'sum1', sum(data->>"$.sum1"),
'sum2', sum(data->>"$.sum2")
) data
from cities
group by citySELECT * FROM `dle_post` WHERE CONCAT('|', `xfields`, '|') LIKE '%|key|123|%'