<?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";
}
with d as (
select
*,
row_number() over (partition by user_login order by issued_at) rn
from tbl
) select * from d where rn = 1;
select
coalesce(type::text, 'total by type') type,
coalesce(cost::text, 'total') cost,
sum(cost) as balance
from expenses e
where plan_date = '2023-12-04'
group by rollup (type,cost);
create extension pgcrypto;
with a as (
select
house_id,
doc_id,
json_agg(json_build_object('doc_name', doc_name, 'file_id', file_id, 'file_path', file_path)) j
from tbl
group by house_id, doc_id
) select house_id, json_agg(j)
from a
group by house_id;
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;