List two distinct tables and filter by Mysql data - PHP

Asked

Viewed 558 times

1

My problem is that I need to list values from two different tables, and I’m using a filter to add conditions in the query. For example:

if(!empty($mes_especifico)) {
   $sql .= "AND YEAR(d.data_pagamento) = :ano AND MONTH(d.data_pagamento) = :mes ";
   $insertData[":ano"] = $ano;
   $insertData[":mes"] = $mes;
}

I wanted to know how to merge in the same query and list the tables and sort by the number of installments.

Tables: https://pastebin.com/rexcXTKw

Separated:

SELECT FORMAT(r.valor_receita,2,'de_DE') AS valor_receita,
       DATE_FORMAT(r.data_vencimento, '%d/%m/%Y') AS data_vencimento,
       r.recebido,
       DATE_FORMAT(r.data_recebimento, '%d/%m/%Y') AS data_recebimento,
       r.tipo_repeticao,
       r.parcelas,
       r.numero_parcela,
       r.recorrente,
       r.observacoes,
       tr.tipo_receita,
       re.tipo_recebimento
FROM receitas AS r
INNER JOIN tipo_receita AS tr ON r.id_tipo_receita = tr.id_tipo_receita
INNER JOIN tipo_recebimento AS re ON r.id_tipo_recebimento = re.id_tipo_recebimento
WHERE r.id_usuario = 1;

SELECT FORMAT(d.valor_despesa,2,'de_DE') AS valor_despesa,
       DATE_FORMAT(d.data_vencimento, '%d/%m/%Y') AS data_vencimento,
       d.pago,
       DATE_FORMAT(d.data_pagamento, '%d/%m/%Y') AS data_pagamento,
       d.importante,
       d.tipo_repeticao,
       d.parcelas,
       d.numero_parcela,
       d.recorrente,
       d.observacoes,
       td.tipo_despesa,
       c.nome_categoria,
       tp.tipo_pagamento
FROM despesas AS d
INNER JOIN tipo_despesa AS td ON d.id_tipo_despesa = td.id_tipo_despesa
INNER JOIN tipo_pagamento AS tp ON d.id_tipo_pagamento = tp.id_tipo_pagamento
INNER JOIN categorias AS c ON td.id_categoria = c.id_categoria
WHERE d.id_usuario = 1;
php mysql

  • in its code Pastebin.com/rexcXTKw , the column tipo_despesa is the type varchar, and in the expenditure table, id_tipo_despesa is INT, to FK won’t work

  • In the table_type there are the columns id_typ_expense and_typeexpense. One is INT and another VARCHAR

  • I tried to put your code in Sqlfiddle, and it was an error, possibly because the name of a column is equal to the name of a table

2 answers

2


If all columns are of the same type, just use the UNION:

SELECT FORMAT(r.valor_receita,2,'de_DE') AS valor_receita,
       DATE_FORMAT(r.data_vencimento, '%d/%m/%Y') AS data_vencimento,
       r.recebido,
       DATE_FORMAT(r.data_recebimento, '%d/%m/%Y') AS data_recebimento,
       null as importante,
       r.tipo_repeticao,
       r.parcelas,
       r.numero_parcela,
       r.recorrente,
       r.observacoes,
       tr.tipo_receita,
       null as categoria,
       re.tipo_recebimento
FROM receitas AS r
INNER JOIN tipo_receita AS tr ON r.id_tipo_receita = tr.id_tipo_receita
INNER JOIN tipo_recebimento AS re ON r.id_tipo_recebimento = re.id_tipo_recebimento
WHERE r.id_usuario = 1

UNION 

SELECT FORMAT(d.valor_despesa,2,'de_DE') AS valor_despesa,
       DATE_FORMAT(d.data_vencimento, '%d/%m/%Y') AS data_vencimento,
       d.pago,
       DATE_FORMAT(d.data_pagamento, '%d/%m/%Y') AS data_pagamento,
       d.importante,
       d.tipo_repeticao,
       d.parcelas,
       d.numero_parcela,
       d.recorrente,
       d.observacoes,
       td.tipo_despesa,
       c.nome_categoria,
       tp.tipo_pagamento
FROM despesas AS d
INNER JOIN tipo_despesa AS td ON d.id_tipo_despesa = td.id_tipo_despesa
INNER JOIN tipo_pagamento AS tp ON d.id_tipo_pagamento = tp.id_tipo_pagamento
INNER JOIN categorias AS c ON td.id_categoria = c.id_categoria
WHERE d.id_usuario = 1;

obs. Use null to hit the missing columns in the other table, so you can execute the command.

If there is conflict of types, you can convert the values.

  • I’ve tried it this way, but it returns the following: SELECT commands used have different number of columns

  • 1

    added more information, see

  • It worked on the side... the filter is not working well.

  • For example... when I filter to list only type of specific expense or revenue, on a specific date

  • 1

    you will have to add the command "AND YEAR(d.data_pagamento) = :ano AND MONTH(d.data_pagamento) = :mes " in the first part of the query as well

  • I tried it like this: https://pastebin.com/QukRKL4B ...

  • what went wrong ? try using Sqlfiddle

  • The same thing as the friend code above... tries to add the following: AND tipo_despesa = 'Água' at the expense WHERE... I need you to return only the water expenses, ignoring the types of revenue, unless you were informed on the filter as well.

Show 3 more comments

2

The solution already presented is right, you should use the same UNION, matching the field types in both SELECTs, consider that the fields exist in both "Selects", when they do not exist, you create them as being "NULL" and name them with "AS". On the other hand, it’s good that you name the fields identico so that they are recognized, so you won’t have the problem you say you have. Based on this, I remodeled your script as follows:

SELECT valor,
       data_vencimento,
       estatus,
       data_movimento,
       importante,
       tipo_repeticao,
       parcelas,
       num_parcelas,
       recorrente,
       observacoes,
       tipo_origem,
       categoria,
       tipo_movto,
       identifica_origem
  FROM (
        SELECT FORMAT(r.valor_receita,2,'de_DE') AS valor,
               DATE_FORMAT(r.data_vencimento, '%d/%m/%Y') AS data_vencimento,
               r.recebido as estatus,
               DATE_FORMAT(r.data_recebimento, '%d/%m/%Y') AS data_movimento,
               NULL as importante,
               r.tipo_repeticao as tipo_repeticao,
               r.parcelas as parcelas,
               r.numero_parcela as num_parcelas,
               r.recorrente as recorrente,
               r.observacoes as observacoes,
               tr.tipo_receita as tipo_origem,
               NULL as categoria,
               re.tipo_recebimento as tipo_movto,
               'Receita' AS identifica_origem
          FROM receitas AS r
               INNER JOIN tipo_receita AS tr ON (r.id_tipo_receita = tr.id_tipo_receita)
               INNER JOIN tipo_recebimento AS re ON (r.id_tipo_recebimento = re.id_tipo_recebimento)
        WHERE r.id_usuario = 1
        UNION 
        SELECT FORMAT(d.valor_despesa,2,'de_DE') AS valor,
               DATE_FORMAT(d.data_vencimento, '%d/%m/%Y') AS data_vencimento,
               d.pago AS estatus,
               DATE_FORMAT(d.data_pagamento, '%d/%m/%Y') AS data_movimento
               d.importante AS importante,
               d.tipo_repeticao as tipo_repeticao,
               d.parcelas as parcelas,
               d.numero_parcela as num_parcelas,
               d.recorrente  as recorrente,
               d.observacoes as observacoes,
               td.tipo_despesa as tipo_origem,
               c.nome_categoria as categoria,
               tp.tipo_pagamento as tipo_movto,
               'Despesa' AS identifica_origem
          FROM despesas AS d
               INNER JOIN tipo_despesa AS td ON (d.id_tipo_despesa = td.id_tipo_despesa)
               INNER JOIN tipo_pagamento AS tp ON (d.id_tipo_pagamento = tp.id_tipo_pagamento)
               INNER JOIN categorias AS c ON (td.id_categoria = c.id_categoria)
         WHERE d.id_usuario = 1
        ) AS origem
       ) AS origem
 WHERE (tipo_origem = 'Água')
 ORDER BY data_movimento

See that the SELECTs I left them in parentheses, as being the FROM of SELECT main, which of the name of Origem, from there I can filter, order, order, in order, treat the results of the UNION as well as understand, as a select normal. The code may seem long, but it is worth wasting a little time and leave it beautiful, so, if necessary, it will be easier to understand and maintenance after.

  • Thanks for the reply... good.. is almost there.. only that does the following.. forehead add the following: AND tipo_despesa = 'Água' at the expense WHERE... I need you to return only the water expenses, ignoring the types of revenue, unless you were informed on the filter as well.

  • 1

    puts this Where tipo_despesa = 'Agua' after the as origem and before the ORDER BY data_movimento, and next to it, slide down the column identifica_origem also, taking only from the table despesa

  • Opa... I think now go on... I’ll do some more tests and then I’ll come back here. Thank you!

  • Eduardo, I changed the example according to Rovvann’s tip, see how it turned out. My tip, instead of leaving the search fixed in 'Water', use parameter, so you will use the same script to search for any expense and or recipe; otherwise, try to use TRIM and LIKE, they can help more because" Water" (with space left, "Water" (no space) and "Water" (with right space), are different.

Browser other questions tagged php mysql

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