CODEWITHSUNDEEP
mysqlsql
Chris Wilson
Chris Wilson

Reputation: 203

MySQL complex query with AND and OR

Seems like I'm trying to learn everything at once: PHP, JS, and now MySQL queries, so its taking a long time to get good at even one... I have a database of tests as shown below. I want a user to see all tests (both pretests and posttests) in a given date range. Here's what I tried (and failed):

$query = $pdo->prepare("SELECT * 
FROM `starprepost` 
WHERE (`pretestdate` >= ? AND `pretestdate` <= ?)
OR (`posttestdate` >= ? AND `posttestdate <=?)
AND `site` = '". $_POST['fromlocation'] ."'
ORDER BY `pretestdate`, `posttestdate` ASC");
$query->execute(array($datebeginning, $dateending, $datebeginning, $dateending));

Here's the schema. Also, any references to how to build good queries is appreciated.

CREATE TABLE `starprepost` (
  `firstname` text,
  `lastname` text,
  `studentnumber` text,
  `site` text,
  `pretestdate` date DEFAULT NULL,
  `posttestdate` date DEFAULT NULL,
  `q1` text,
  `q2` text,
  `q3` text,
  `q4` text,
  `q5` text,
  `q6` text,
  `q7` text,
`id` int(6) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1

Upvotes: 0

Views: 66

Answers (1)

Prashant16
Prashant16

Reputation: 1526

Try like 

$query = $pdo->prepare("SELECT * 
FROM `starprepost`  
WHERE ((`pretestdate` >= ? AND `pretestdate` <= ?) OR `pretestdate`= NULL )
AND ((`posttestdate` >= ? AND `posttestdate` <=?) OR `posttestdate` = NULL  )
AND `site` = '". $_POST['fromlocation'] ."'
ORDER BY `pretestdate`, `posttestdate` ASC");
$query->execute(array($datebeginning, $dateending, $datebeginning, $dateending));

Upvotes: 1

Related Questions