IF conditional within SQL query

Asked

Viewed 1,089 times

0

I have the following appointment:

$string = "
SELECT DISTINCT(idClientes), nome FROM clientes WHERE idClientes IN ( 
 SELECT idClientes FROM planosclientes WHERE idPlanosClientes IN (
   SELECT distinct(idPlanoClientes) FROM pagamentos WHERE mesReferencia NOT IN ('".$mesReferencia."') 
    ) and DATEDIFF (CURDATE(), CONCAT(DATE_FORMAT(CURDATE(),'%Y,%m' ),'-', diaVencimento)) > ".$dias."
    UNION
    SELECT idPlanosClientes FROM planosclientes WHERE idPlanosClientes NOT IN                                                (SELECT distinct(idPlanoClientes) FROM pagamentos)
           ) ORDER BY nome";

It turns out that if the field diaVencimento is larger than the current day, in php date('d'), the expression $mesReferencia which receives a value of the type date('Y-m') needs to be from the previous month. That is date('Y-m') less 1 month.

How to make that parole?

Tables in question:

CREATE TABLE clientes (
  idClientes int(10) unsigned NOT NULL AUTO_INCREMENT,
  tipoClientes char(1) NOT NULL DEFAULT '',
  nome varchar(100) NOT NULL,
  cpf char(11) DEFAULT '',
  cnpj char(14) DEFAULT '',
  email varchar(100) DEFAULT '',
  telefone varchar(11) NOT NULL DEFAULT '',
  celular varchar(11) DEFAULT '',
  bloqueado char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (idClientes)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

CREATE TABLE pagamentos (
  idPagamentos int(10) unsigned NOT NULL AUTO_INCREMENT,
  idPlanoClientes int(10) NOT NULL,
  idAdmins int(1) NOT NULL,
  mesReferencia char(7) NOT NULL,
  dataPgto date NOT NULL,
  valorPgto double NOT NULL,
  multa double NOT NULL,
  juros double NOT NULL,
  desconto double NOT NULL,
  totalPago double NOT NULL,
  formaPgto char(2) NOT NULL,
  observacao text,
  PRIMARY KEY (idPagamentos)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE planosclientes (
  idPlanosClientes int(10) unsigned NOT NULL AUTO_INCREMENT,
  idClientes int(10) NOT NULL,
  idPlanos int(10) NOT NULL,
  valorCombinado double NOT NULL,
  diaVencimento varchar(2) NOT NULL DEFAULT '0',
  dataInstalacao date NOT NULL DEFAULT '0000-00-00',
  observacao text,
  login varchar(25) NOT NULL DEFAULT '',
  senha varchar(25) DEFAULT '',
  bloqueado char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (idPlanosClientes)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
php mysql sql

  • Your query would be much better (and faster) if you used JOINS, but to help you would need to see the structure of the tables in use. Another tip is to use the database’s own functions to calculate the date instead of concatenating the PHP function in your sql query

  • I put the structure of the tables!

  • They don’t have Foreign Keys? or you didn’t put ?

  • has but not put. I informed this in SQL $mesReference comes from the form and $days also because are days and late

  • If CURDATE(), '%d' ) < diaVencimento, the variable ***$mesReference ***that receives date('Y-m') php is decreased by 1 month and otherwise

  • Some questions: 1 - The reference month you pass from PHP is always the current month (date(Y-m)) or can it be an informed value? 2 - The purpose of this line DATEDIFF (CURDATE(), CONCAT(DATE_FORMAT(CURDATE(),'%Y,%m' ),'-', diaVencimento)) is to decrease the current date of the date formed by the Concat with the diaVencimento to compare with the $dias , if that is what is to happen if the Kurdish returns a day shorter than the date of concatenation ? 3 - You have created an idClients Union with idPlanosClients that is correct? makes no sense!

  • 4 - Union is to take out all customers who have plans but have no payments ?

    1. same Ference is the current month. If the day that is typical of each contract plan is after the day the report is printed, then the same Reference for that launcher needs to be the previous one! 2) Know how many days late the customer is from the due day to the date of issue 3) The 2 clauses united by Union ask idPlanosClientes and are within an IN() 4) that, customers who have plan but have not paid any month yet!

  • Let’s go continue this discussion in chat.

Show 4 more comments

1 answer

1


After the doubts raised in the comments the final query for what you need would be this:

$string = "
  SELECT c.idClientes, c.nome
    FROM clientes c 
           INNER JOIN planosclientes PC ON (c.idClientes = pc.idClientes)
           INNER JOIN pagamentos p ON (pc.idPlanosClientes = p.idPlanoClientes)
   WHERE p.mesReferencia != (CASE WHEN pc.diaVencimento>EXTRACT(DAY from CURDATE()) 
                                  THEN DATE_FORMAT((CURDATE() - INTERVAL '1' MONTH), '%Y-%m')
                                  ELSE DATE_FORMAT(CURDATE(), '%Y-%m') 
                              END)
     AND DATEDIFF(CURDATE(), CONCAT(DATE_FORMAT( CURDATE(), '%Y-%m' ), '-', pc.diaVencimento)) > ".$dias."
   ORDER BY c.nome ";

The condition you seek is made by the command CASE, in your case:

(CASE WHEN pc.diaVencimento>EXTRACT(DAY from CURDATE()) 
      THEN DATE_FORMAT((CURDATE() - INTERVAL '1' MONTH), '%Y-%m')
      ELSE DATE_FORMAT(CURDATE(), '%Y-%m') 
  END)

Note that I have replaced the use of date('Y-m') by the function of the bank DATE_FORMAT( CURDATE(), '%Y-%m' )

The UNION eliminates customers who have plan and have not made payments, so the use of JOIN meets this filter.

Any problem is just write there in the comments.

  • I’m just thinking here. If the date of publication of the report is longer than the date of publication of the report, so, to see if the previous month of the guy is paid would be good since the report is only of that month? What do you think?

  • Given the structure of its tables, payment (dataPgto date NOT NULL) means that a record in this table means the payment record itself, which, again, INNER JOIN already resolves. When the reference month is the previous one he is already bringing the record that was paid!

  • 1

    Thank you very much!

Browser other questions tagged php mysql sql

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