select по столбцу, и insert

WMix · 19 Окт 2020

@shiyri а зачем ты эту мантру повторяешь?

shiyri написал(а):
PHP:

mysqli_query($conn,"SET CHARACTER SET 'utf8'"); date_default_timezone_set('Asia/Yekaterinburg');

и почему бы не сложить оба запроса в один

Код:

INSERT INTO menu_table (date, m_array)
SELECT $date_t, m_array FROM menu_table WHERE date = '$date'

Фанат · 19 Окт 2020

ну ты спросил!
понятно что в примере, из которого он копировал, перед запросом была мантра.
ну он же не знает, что к запросу относится, а что нет. есть некий шаблон выполнения запроса - вот он его старательно и заполняет

shiyri · 20 Окт 2020

Добрый день, покажите как использовать переменную в mysql запросе, по итогу нули.....

Код:

SEC_TO_TIME(IF(@x5:=
SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05
 then ROUND(TIME_TO_SEC(pr.time_ar)) end))> 32400, 32400,  @x5) as d5,

если пишу так:

Код:

SEC_TO_TIME(IF(
SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05
then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400,
SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05 
then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d5,

[CODE]
то все работает как надо :))))

Глядя на это глава кровоточат

))))

Код:

select pr.propusk, ft.fio,
    SEC_TO_TIME(IF(@x1:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 01 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 01 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d1,
    SEC_TO_TIME(IF(@x2:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 02 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 02 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d2,
    SEC_TO_TIME(IF(@x3:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 03 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 03 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d3,
    SEC_TO_TIME(IF(@x4:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 04 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 04 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d4,
    SEC_TO_TIME(IF(@x5:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d5,
    SEC_TO_TIME(IF(@x6:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 06 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 05 then ROUND(TIME_TO_SEC(pr.time_ar)) end)))) as d6,
    SEC_TO_TIME(IF(@x7:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 07 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 07 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d7,
    SEC_TO_TIME(IF(@x8:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 08 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 08 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d8,
    SEC_TO_TIME(IF(@x9:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 09 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 09 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d9,
    SEC_TO_TIME(IF(@x10:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 10 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 10 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d10,
    SEC_TO_TIME(IF(@x11:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 11 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 11 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d11,
    SEC_TO_TIME(IF(@x12:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 12 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 12 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d12,
    SEC_TO_TIME(IF(@x13:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 13 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 13 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d13,
    SEC_TO_TIME(IF(@x14:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 14 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 14 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d14,
    SEC_TO_TIME(IF(@x15:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 15 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 15 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d15,
    SEC_TO_TIME(IF(@x16:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 16 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 16 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d16,
    SEC_TO_TIME(IF(@x17:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 17 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 17 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d17,
    SEC_TO_TIME(IF(@x18:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 18 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 18 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d18,
    SEC_TO_TIME(IF(@x19:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 19 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 19 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d19,
    SEC_TO_TIME(IF(@x20:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 20 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 20 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d20,
    SEC_TO_TIME(IF(@x21:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 21 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 21 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d21,
    SEC_TO_TIME(IF(@x22:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 22 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 22 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d22,
    SEC_TO_TIME(IF(@x23:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 23 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 23 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d23,
    SEC_TO_TIME(IF(@x24:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 24 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 24 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d24,
    SEC_TO_TIME(IF(@x25:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 25 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 25 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d25,
    SEC_TO_TIME(IF(@x26:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 26 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 26 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d26,
    SEC_TO_TIME(IF(@x27:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 27 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 27 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d27,
    SEC_TO_TIME(IF(@x28:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 28 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 28 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d28,
    SEC_TO_TIME(IF(@x29:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 29 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 29 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d29,
    SEC_TO_TIME(IF(@x30:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 30 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 30 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d30,
    SEC_TO_TIME(IF(@x31:=SUM(CASE when DATE_FORMAT(pr.data, '%d') = 31 then ROUND(TIME_TO_SEC(pr.time_ar)) end)> 32400, 32400, SUM(CASE when DATE_FORMAT(pr.data, '%d') = 31 then ROUND(TIME_TO_SEC(pr.time_ar)) end))) as d31
    from fio_table as ft left join prohod as pr on ft.propusk=pr.propusk
    where pr.propusk = '$propusk' and pr.data BETWEEN '$date1' AND '$date2' GROUP BY pr.propusk";

WMix · 20 Окт 2020

shiyri написал(а):
как использовать переменную в mysql запросе

Мне кажется у тебя уровень низковатый для таких заклинаний

select по столбцу, и insert

WMix

герр M:)ller

Фанат

oncle terrible

shiyri

Новичок

WMix

герр M:)ller