How to separate the result of a mysql query into clusters

Asked

Viewed 88 times

0

AS TABLES:

--
-- Estrutura da tabela `tb_inc`
--

CREATE TABLE `tb_inc` (
  `idinc` int(255) NOT NULL,
  `codi` varchar(255) NOT NULL,
  `origi` varchar(255) NOT NULL,
  `nomei` varchar(255) NOT NULL,
  `emaili` varchar(255) NOT NULL,
  `cpfi` varchar(255) NOT NULL,
  `cnpji` varchar(255) NOT NULL,
  `nasci` varchar(255) NOT NULL,
  `niveri` varchar(255) NOT NULL,
  `generoi` varchar(255) NOT NULL,
  `contati` varchar(255) NOT NULL,
  `celai` varchar(255) NOT NULL,
  `celbi` varchar(255) NOT NULL,
  `telai` varchar(255) NOT NULL,
  `telbi` varchar(255) NOT NULL,
  `codreloadi` varchar(255) NOT NULL,
  `idfunc` varchar(255) NOT NULL,
  `operalti` varchar(255) NOT NULL,
  `dtalti` varchar(255) NOT NULL,
  `hralti` varchar(255) NOT NULL,
  `idfalt` varchar(255) NOT NULL,
  `operai` varchar(255) NOT NULL,
  `dt_inc` varchar(255) NOT NULL,
  `hr_inc` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Extraindo dados da tabela `tb_inc`
--

INSERT INTO `tb_inc` (`idinc`, `codi`, `origi`, `nomei`, `emaili`, `cpfi`, `cnpji`, `nasci`, `niveri`, `generoi`, `contati`, `celai`, `celbi`, `telai`, `telbi`, `codreloadi`, `idfunc`, `operalti`, `dtalti`, `hralti`, `idfalt`, `operai`, `dt_inc`, `hr_inc`) VALUES
(1, '1', 'Site', 'Brendo César Silas', '[email protected]', '00000000000', '', '1972-12-17', '17/12', 'M', '', '(31) 9 9908-2987', '', '', '', '6ff153749d7443d8542b6e4181a48570', '1', 'Infonet', '2019-08-11', '14:35', '3', 'Brendo', '2019-07-15', '12:49'),
(2, '2', 'Loja', 'Manuel santos', '', '', '89899898989898', '1980-12-31', '31/12', 'M', 'Júlia Alves', '', '', '', '', 'ff842aa7fdf37dd9d90d3b677f5d1a17', '1', 'Infonet', '2019-08-08', '10:57', '1', 'Infonet', '2019-07-15', '13:11'),
(3, '3', 'Site', 'Armando Sá de Oliveira', '', '', '', '', '', 'M', '', '', '', '', '', 'fee4b5fe8599a3eeee8394477e2c7ee4', '3', 'Brendo', '2019-08-13', '23:30', '1', 'Infonet', '2019-08-07', '17:34'),
(4, '4', 'Site', 'Amanda Clark', '', '', '', '', '', 'F', '', '', '', '', '', 'a3011ab63ab96c13dc697bb89298acc4', '2', 'Nexobraz', '2019-09-10', '00:28', '1', 'Infonet', '31-12-1969', '18:32'),
(5, '5', 'Site', 'Maria do Carmo', '', '000000000000', '', '1937-04-04', '04/04', '', '', '', '', '', '', 'c38b0a8eab8aee005ec8c36fd9e12bee', '1', 'Infonet', '2019-09-14', '23:41', '3', 'Brendo', '31-12-1969', '18:33'),
(6, '6', 'Site', 'Fausto Monteiro', '', '00000000000', '', '1953-08-22', '22/08', 'M', '', '(31) 9 8505-0000', '', '', '', '3b1a5994f2e160bcddee7a3501d9f48f', '1', 'Infonet', '2019-09-21', '21:22', '1', 'Infonet', '31-12-1969', '18:34'),
(7, '7', 'Site', 'José Aparecido De Souza', '', '', '', '', '', '', '', '', '', '', '', '0c2dc72584d9092c3b8ada7dba6337ed', '1', 'Infonet', '2019-08-13', '00:02', '2', 'Nexobraz', '2019-08-07', '18:35'),
(8, '8', 'Site', 'Carlos Santana', '', '', '', '', '', '', '', '', '', '', '', '135ed1983caa840be92bb839d20e5365', '2', 'Nexobraz ', '2019-08-10', '01:53', '1', 'Infonet', '2019-08-07', '18:40'),
(9, '9', 'Site', 'Virgínia Almeida Silva', '[email protected]', '', '', '', '', '', '', '', '', '', '', 'a7e0c91898de2de8592f3ac618326dfb', '1', 'Infonet', '2019-08-07', '18:44', '1', 'Infonet', '2019-08-07', '18:43'),
(10, '10', 'Flayer', 'Joaquim Gonçalves', '', '', '', '', '', '', '', '', '', '', '', 'bb5fd8916a26dcc958132c3b64fa9a18', '1', 'Infonet', '2019-08-09', '14:36', '1', 'Infonet', '2019-08-09', '14:19'),
(11, '11', '', 'Cláudia Sampaio da Silva', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-08-11', '17:27'),
(12, '12', 'Site', 'Samira Silva Oliveira', '', '', '', '', '', '', '', '', '', '', '', '4ff522f0280730c1a0f4cec21b970f67', '3', 'Brendo', '2019-08-13', '23:37', '3', 'Brendo', '2019-08-13', '23:35'),
(13, '13', '', 'Cliente Teste de Exclusão', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-08-24', '15:42'),
(14, '14', '', 'Clientes 2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-08-24', '15:45'),
(15, '15', 'Site', 'Antônio Carlos Fernandes Junior', '[email protected]', '00000000000', '', '', '', 'M', '', '(31) 9 9151-0000', '', '', '', '25fb06c54d8ed04feb216fc6142c0ee3', '1', 'Infonet', '2019-09-12', '02:48', '1', 'Infonet', '2019-07-18', '15:53'),
(17, '17', 'Google', 'Carlos Antônio da Silva', '', '00000000000', '', '1963-11-01', '01/11', 'M', '', '(31) 9 9762-0000', '', '', '', '22d8a80beb4f4f834a258a0671f54edd', '1', 'Infonet', '2019-09-12', '03:27', '1', 'Infonet', '2019-07-04', '15:57'),
(18, '18', '', 'Natália Costa Salésios', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-05', '14:09'),
(19, '19', '', 'Testa Tudo', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:15'),
(20, '20', '', 'Teste Tudo 02', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:16'),
(21, '21', '', 'Teste Tudo 03', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:31'),
(22, '22', '', 'Clientes 04', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:36'),
(23, '23', '', 'Teste 05', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:38'),
(24, '24', '', 'Teste 06', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:40'),
(25, '25', '', 'Cliente 07', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:47'),
(26, '26', '', 'Cliente 08', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'Infonet', '2019-09-12', '23:49'),
(27, '27', '', 'Cliente 09', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'infonet', '2019-09-13', '00:27');

-- --------------------------------------------------------

--
-- Estrutura da tabela `tb_res`
--

CREATE TABLE `tb_res` (
  `idres` int(255) NOT NULL,
  `idclir` varchar(255) NOT NULL,
  `tpcred` varchar(255) NOT NULL,
  `clsfold` varchar(255) NOT NULL,
  `dtultr` varchar(255) NOT NULL,
  `dtagdr` varchar(255) NOT NULL,
  `nsts` varchar(255) NOT NULL,
  `nstsg` varchar(255) NOT NULL,
  `stsr` varchar(255) NOT NULL,
  `obsr` text NOT NULL,
  `idfuncr` varchar(255) NOT NULL,
  `contr` varchar(255) NOT NULL,
  `reshis` varchar(255) NOT NULL,
  `dt_res` varchar(255) NOT NULL,
  `hr_res` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Extraindo dados da tabela `tb_res`
--

INSERT INTO `tb_res` (`idres`, `idclir`, `tpcred`, `clsfold`, `dtultr`, `dtagdr`, `nsts`, `nstsg`, `stsr`, `obsr`, `idfuncr`, `contr`, `reshis`, `dt_res`, `hr_res`) VALUES
(1, '4', '2', '', '2019-08-01', '2019-08-23', '4', '5', '', 'Teste do res novo 02 sofá', '1', '1', '4', '2019-09-21', '19:28'),
(2, '5', '1', 'A', '2019-08-30', '2019-09-15', '1', '4', '', 'Teste para pasta 022', '1', '3', '5', '2019-09-15', '20:47'),
(3, '17', '4', 'A', '2019-07-22', '2019-08-25', '1', '4', '', 'Aguardando migração da proposta', '1', '', '17', '2019-09-12', '02:41'),
(4, '5', '3', '', '', '', '', '', '', '', '', '', '', '', ''),
(5, '3', '2', '', '', '', '', '', '', '', '', '', '', '', ''),
(6, '6', '1', '', '2019-02-23', '2019-09-03', '2', '2', '', 'Enviar solicitação de pagamento ', '1', '1', '6', '2019-09-15', '22:02'),
(7, '6', '2', '', '2019-09-05', '2019-09-05', '4', '', '', 'teste', '1', '', '6', '2019-09-05', '14:06'),
(8, '6', '3', '', '2019-09-05', '2019-09-05', '4', '', '', 'teste 02', '1', '', '6', '2019-09-05', '14:06'),
(9, '15', '5', 'A', '2019-09-12', '2019-09-15', '6', '4', '', 'Proposta rejeitada ', '1', '', '15', '2019-09-12', '02:51'),
(10, '14', '1', '', '2019-09-12', '2019-09-12', '4', '', '', 'saddsd', '1', '', '14', '2019-09-12', '22:51'),
(11, '13', '1', '', '2019-09-12', '2019-09-12', '5', '', '', 'teste de exclusão', '1', '', '13', '2019-09-12', '15:19'),
(12, '19', '1', '', '2019-09-12', '2019-09-12', '4', '', '', 'teste tudo', '1', '', '19', '2019-09-12', '23:15'),
(13, '20', '1', '', '2019-09-12', '2019-09-12', '3', '', '', 'teste tudo 02', '1', '', '20', '2019-09-12', '23:17'),
(14, '21', '1', '', '2019-09-12', '2019-09-12', '4', '', '', 'teste tudo 03', '1', '', '21', '2019-09-12', '23:31'),
(15, '22', '1', '', '2019-09-12', '2019-09-12', '3', '', '', 'tsete 04', '1', '', '22', '2019-09-12', '23:37'),
(16, '23', '2', '', '2019-09-12', '2019-09-12', '4', '4', '', 'teste 05', '1', '', '23', '2019-09-12', '23:38'),
(17, '24', '1', '', '2019-09-11', '2019-09-11', '5', '', '', 'teste 06', '1', '', '24', '2019-09-12', '23:41'),
(18, '25', '2', '', '2019-09-14', '2019-09-14', '4', '4', '', 'teste 07', '1', '', '25', '2019-09-15', '18:18'),
(19, '26', '3', '', '2019-09-12', '2019-09-12', '4', '4', '', 'teste 08', '1', '', '26', '2019-09-15', '21:49'),
(20, '27', '5', '', '2019-09-13', '2019-09-13', '3', '', '', 'opopop', '1', '', '27', '2019-09-13', '00:28');

-- --------------------------------------------------------

--
-- Estrutura da tabela `tb_users`
--

CREATE TABLE `tb_users` (
  `iduser` int(255) NOT NULL,
  `mac` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `login` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `nome` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `email` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `cpf` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cnpj` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `senha` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `photo` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
  `cidade` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `uf` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `niver` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `nasc` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `genero` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `nivel` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT '1',
  `nivelb` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `nivelc` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `niveld` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `nivele` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `validation` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT '0',
  `alert` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `motivo` longtext CHARACTER SET latin1 COLLATE latin1_general_ci,
  `dt_log` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `hr_log` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `dt_cad` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `hr_cad` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Extraindo dados da tabela `tb_users`
--

INSERT INTO `tb_users` (`iduser`, `mac`, `login`, `nome`, `email`, `cpf`, `cnpj`, `cel`, `tel`, `senha`, `photo`, `cidade`, `uf`, `niver`, `nasc`, `genero`, `nivel`, `nivelb`, `nivelc`, `niveld`, `nivele`, `validation`, `alert`, `motivo`, `dt_log`, `hr_log`, `dt_cad`, `hr_cad`) VALUES
(1, '', 'infonet', 'Infonet', '[email protected]', '', '', '', '', 'MTIzNDU2', 'Y', 'Belo Horizonte', 'MG', '17/12', '1972-12-17', 'M', '2', '', '', '', '', '1', 'ON', '', '2019-09-15', '17:55', '2019-01-01', '15:10'),
(2, NULL, 'nexobraz', 'Nexobraz', '[email protected]', '', '', '', '', 'MTIzNDU2', 'Y', 'Belo Horizonte', 'MG', '17/12', '1972-12-17', 'M', '2', NULL, NULL, NULL, NULL, '1', 'ON', NULL, '', '05:04', '2019-04-20', '02:00'),
(3, NULL, 'brendocs', 'Brendo César Silas', '[email protected]', '', '', '', '', 'MTIzNDU2', 'N', 'Belo Horizonte', 'MG', '17/12', '1972-12-17', 'M', '1', 'B', NULL, NULL, NULL, '1', '', NULL, '', '23:06', '2019-04-20', '03:05'),
(4, NULL, 'mariac', 'Maria do Carmo', '[email protected]', '', '', '', '', 'MTIzNDU2', 'N', 'Belo Horizonte', 'MG', '17/12', '1937-04-04', 'F', '1', NULL, NULL, NULL, NULL, '1', '', NULL, '2019-04-20', '03:30', '2019-04-20', '03:28');

ALTER TABLE `tb_inc`
  ADD PRIMARY KEY (`idinc`);

ALTER TABLE `tb_res`
  ADD PRIMARY KEY (`idres`);

ALTER TABLE `tb_users`
  ADD PRIMARY KEY (`iduser`);

ALTER TABLE `tb_inc`
  MODIFY `idinc` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

ALTER TABLE `tb_res`
  MODIFY `idres` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

ALTER TABLE `tb_users`
  MODIFY `iduser` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;COMMIT;

THE CONSULTATION:

date_default_timezone_set('America/Sao_Paulo');
$dtHoje = date("Y-m-d");

$sqlres = "SELECT r.*, i.idinc,i.nomei,u.nome, LPAD( @a := @a + 1, 2, '0' ) 'sequencial' FROM (SELECT @a := 0) AS nada, tb_res r RIGHT JOIN tb_inc i ON r.idclir = i.idinc 
LEFT JOIN tb_users u ON r.contr = u.iduser WHERE r.nsts !='5' AND r.nsts !='2' AND r.nsts !='6' AND r.dtagdr != '' AND r.clsfold != 'A' ORDER BY r.dtagdr ASC";
$query = $conn->query($sqlres);

while($row = $query->fetch_assoc()) {
    $rdtagdr = $row["dtagdr"];

    $seq = $row["sequencial"];
    $iidinc = $row["idinc"];
    $inomei = $row["nomei"];
    $unome = explode(' ', $row["nome"]);
    $user = array_shift($unome);

    $diferenca = date(strtotime($rdtagdr)) - date(strtotime($dtHoje));
    $timed = floor($diferenca / (60 * 60 * 24));

if ($timed > 1) { $situacao = "<div class='box-ag-ger agend'>Agendado</div>"; }
if ($timed == 1) { $situacao = "<div class='box-ag-ger amanhad'>Amanhã</div>"; }
if ($timed == 0) { $situacao = "<div class='box-ag-ger emdia'>Hoje</div>"; }
if ($timed == -1) { $situacao = "<div class='box-ag-ger ontemd'>Ontem</div>"; }
if ($timed == -2 && $timed > -3) { $situacao = "<div class='box-ag-ger doisd'>Há 2 dias</div>"; }
if ($timed <= -3 && $timed >= -7) { $situacao = "<div class='box-ag-ger aguarda'>Aguardando</div>"; }
if ($timed <= -8 && $timed > -31) { $situacao = "<div class='box-ag-ger atrasa'>Atrasado</div>"; }
if ($timed <= -31) { $situacao = "<div class='box-ag-ger trintad'>Há 30+ dias</div>"; }
if ($timed <= -32) { $situacao = "<div class='box-ag-ger semove'>Sem movimento</div>"; }

<style>
.emdia { background:#6AF0FB;color:#000; }
.amanhad { background:#8CD9DD;color:#000; }
.ontemd { background:#FFFF00;color:#000; }
.doisd { background:#FFF582;color:#000; }
.aguarda { background:rgba(255,215,0, 0.8);color:#000; }
.atrasa { background:rgba(255, 1, 66, 0.5);color:#fff; }
.trintad { background:#000;color:#fff; }
.semove { background:rgba(255, 1, 66, 0.8);color:#fff; }
.agend { background:#66B3FF;color:#fff; }
.box-ag-ger { display:inline-block;padding:5px; }
</style>

I need the result to be separated by $SITUATION to be +/- like this. Oh! and now, who can help me (laughs):

ATRASADOS__________ (Todos que se encaixarem neste if - mais de uma semana até 30 dias)
Fulano <br/>
Beltrano <br/>
Ciclano <br/>

AGUARDANDO__________ (Todos que se encaixarem neste if - de ontem para trás até 1 semana)
Fulano <br/>
Beltrano <br/>
Ciclano <br/>

HOJE__________ (Todos que se encaixarem neste if)
Fulano <br/>
Beltrano <br/>
Ciclano <br/>

AGENDADO__________ (Tudo que estiver agendado para datas futuras - a partir de amanhã)
Fulano <br/>
Beltrano <br/>
Ciclano <br/>

I am sending the Tables, but as it is past the dates, I always do a test putting the Windows date on 12/09/2019, so print several situations.
Sorry about the "will" (kkkk), but it was the way I found to ask my question. I tried a hill, a thousand galaxies... and I couldn’t group!
Thank you, my brothers

HOW THE OUTPUT IS CURRENTLY (12/09/2019) Saída atual


Attending to requests (rsrs), this is how I imagine quickly, because I did in Corel kind of running, that was: (I apologize for the bad art) Thank you! [! important-> DATE: 12/09/2019]

inserir a descrição da imagem aqui

php mysql

  • Are the results coming correctly? The problem is in grouping the results to display, that’s it?

  • That’s right buddy... Exactly

  • See the image I added of how the output is currently. This is what I need to separate...

  • Yes... My answer groups the values. That’s not what you wanted?

  • Example: Take a look at the image I posted... well, Clients 09 and 07 are two situations, respectively: "Tomorrow" and "Scheduled"... then, 'How would you put these two situations in "your" grouping "Scheduled"?'

  • Wow.... I huh... Kd the code of the "rabbit" that was down here?... Ué... I will do a tribute in my profile procê "rabbit"... after you look! Friendly hug!

  • Hi friend, I withdrew the answer because I thought it was not helping. I really did not understand the question right.

  • It did help... I hammered these last 24 hours on top of that code of yours... that’s good for me... learning more... Thank you and may God enlighten you!

  • Ah! Glad I could help! Hug!

  • Could someone please help me group this consultation!

  • Inserts an image of how you want the final result. It will be easier to visualize... Up there you have the output of how you are currently. Put down an image of how you would like it to look.

  • Hello Mr. Rabbit! Thank you for the initiative...

  • Nothing in the "hat" Mr. Rabbit? chuinf... (rs)

  • I’ll take a look tomorrow, we’ll see... =)

  • Oba! Thank you!!! ;)

Show 10 more comments
No answers

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.