[MartinPL]

Hello,
please take a look at this query ...
This query should return datas only from November.

SELECT * FROM historia_wplat WHERE (data_1 BETWEEN '2009-11-01' AND '2009-11-31') OR (data_2 BETWEEN '2009-11-01' AND '2009-11-31')

...works with this table but returns records witch are from December but the condition is BETWEEN '2009-11-01' AND '2009-11-31'. Why?

here is my table

CREATE TABLE IF NOT EXISTS `historia_wplat` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_wniosku` int(11) NOT NULL COMMENT 'identyfikuje historie z wnioskiem',
  `id_wnioskodawcy` int(5) NOT NULL COMMENT 'kto wprowadzil wniosek',
  `wplata_1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_1` date NOT NULL COMMENT 'data 1 wplaty',
  `wplata_2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_2` date NOT NULL COMMENT 'data 2 wplaty',
  `wplata_3` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_3` date NOT NULL COMMENT 'data 3 wplaty',
  `wplata_4` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_4` date NOT NULL COMMENT 'data 4 wplaty',
  `wplata_5` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_5` date NOT NULL COMMENT 'data 5 wplaty',
  `wplata_6` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_6` date NOT NULL COMMENT 'data 6 wplaty',
  `wplata_7` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_7` date NOT NULL COMMENT 'data 7 wplaty',
  `wplata_8` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_8` date NOT NULL COMMENT 'data 8 wplaty',
  `wplata_9` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_9` date NOT NULL COMMENT 'data 9 wplaty',
  `wplata_10` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_10` date NOT NULL COMMENT 'data 10 wplaty',
  `wplata_11` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_11` date NOT NULL COMMENT 'data 11 wplaty',
  `wplata_12` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'prowizja za wplate  nr',
  `data_12` date NOT NULL COMMENT 'data 12 wplaty',
  PRIMARY KEY (`id`),
  KEY `id_wniosku` (`id_wniosku`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=434;

INSERT INTO `historia_wplat` (`id`, `id_wniosku`, `id_wnioskodawcy`, `wplata_1`, `data_1`, `wplata_2`, `data_2`, `wplata_3`, `data_3`, `wplata_4`, `data_4`, `wplata_5`, `data_5`, `wplata_6`, `data_6`, `wplata_7`, `data_7`, `wplata_8`, `data_8`, `wplata_9`, `data_9`, `wplata_10`, `data_10`, `wplata_11`, `data_11`, `wplata_12`, `data_12`) VALUES
(433, 272, 3, '175', '2009-11-02', '200', '2009-12-01', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00', '', '0000-00-00');

[motcom]

Hi,

I think the error is in on the "OR" it will get everything that is between '2009-11-01' AND '2009-11-31' from data_1 and everything from data_2.

So try changing the "OR" to "AND"

SELECT * FROM historia_wplat WHERE (data_1 BETWEEN '2009-11-01' AND '2009-11-31') AND (data_2 BETWEEN '2009-11-01' AND '2009-11-31')

[MartinPL]

Quote

I think the error is in on the "OR" it will get everything that is between '2009-11-01' AND '2009-11-31' from data_1 and everything from data_2.

You are right.
But it should get values which are between '2009-11-01' AND '2009-11-31' from data_1, data_2 etc.

I cant change OR to AND becasue it will not be what i want. In your example mysql will return values ONLY IF TWO conditions are true.

i.e. mysql return empty result even if data_1 = '2009-11-01' beacuse data_2 ='2009-02-11'.

true AND false - > false

true OR false - > true

I want get values from data_1, data_2, ..., data_12 if data is between i.e. '2009-11-01' AND '2009-11-31'.

I figured out a different way to get what I want. I works but it is a little crazy

for($i=1;$i<=12; $i++)
	{
	$pobierz_dane_do_raportu = mysql_query("SELECT * FROM historia_wplat WHERE id_wnioskodawcy = '$id' AND data_$i BETWEEN '$data_poczatkowa' AND '$data_koncowa';");
	while($dane_do_raportu = mysql_fetch_array($pobierz_dane_do_raportu))
		{
.
.
.
}
}

however it is not an sql solution it is solved by php.
My English is not good so let me show it.

This post has been edited by MartinPL: 04 November 2009 - 06:54 AM