MySQL complex query with AND and OR

Question

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

I guess the question is how can I get ALL records in the date range whether they are pre-tests (have a date in the pretestdate, but not the posttestdate), or post-tests (have no date in the pretestdate, but one in the posttestdate). — Chris Wilson
– Chris Wilson, Commented Feb 20, 2014 at 23:14

Prashant16 · Accepted Answer · 2014-02-21 05:29:33Z

1

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));

edited Feb 21, 2014 at 5:29

answered Feb 20, 2014 at 7:51

Prashant16

1,5264 gold badges18 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Wilson Over a year ago

No errors, but also no records returned either, even when I chose a wide date range. Also had to fix a apostrophe on line 4 (missing).

Collectives™ on Stack Overflow

MySQL complex query with AND and OR

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related