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;
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
– Jorge Campos
I put the structure of the tables!
– Carlos Rocha
They don’t have Foreign Keys? or you didn’t put ?
– Jorge Campos
has but not put. I informed this in SQL $mesReference comes from the form and $days also because are days and late
– Carlos Rocha
If CURDATE(), '%d' ) < diaVencimento, the variable ***$mesReference ***that receives date('Y-m') php is decreased by 1 month and otherwise
– Carlos Rocha
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 lineDATEDIFF (CURDATE(), CONCAT(DATE_FORMAT(CURDATE(),'%Y,%m' ),'-', diaVencimento))
is to decrease the current date of the date formed by the Concat with thediaVencimento
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!– Jorge Campos
4 - Union is to take out all customers who have plans but have no payments ?
– Jorge Campos
– Carlos Rocha
Let’s go continue this discussion in chat.
– Jorge Campos