MySQL query: if and between

Lennart Ericson
Lennart Ericson used Ask the Experts™
on
Having the following database:
CREATE TABLE `bokningsdagar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boknings_ar` int(11) DEFAULT NULL,
  `boknings_dag1a` date DEFAULT NULL,
  `boknings_dag1b` date DEFAULT NULL,
  `boknings_ant1` int(11) DEFAULT NULL,
  `boknings_dag2a` date DEFAULT NULL,
  `boknings_dag2b` date DEFAULT NULL,
  `boknings_ant2` int(11) DEFAULT NULL,
  `boknings_dag3a` date DEFAULT NULL,
  `boknings_dag3b` date DEFAULT NULL,
  `boknings_ant3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I want to pick
boknings_ant1 if $_POST[bokningsdatum]  is between or on boknings_dag1a and boknings_dag1b
or
boknings_ant2 if $_POST[bokningsdatum]  is between or on boknings_dag2a and boknings_dag2b,
etc.
I have tried this, but it failed.
$SQL1 = " SELECT * FROM bokningsdagar
IF $_POST[bokningsdatum] between boknings_dag1a AND boknings_dag1b then boknings_ant1 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag2a AND boknings_dag2b then boknings_ant2 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag3a AND boknings_dag3b then boknings_ant3 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag4a AND boknings_dag4b then boknings_ant4 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag5a AND boknings_dag5b then boknings_ant5 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag6a AND boknings_dag6b then boknings_ant6 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag7a AND boknings_dag7b then boknings_ant7 as antalet END IF
IF $_POST[bokningsdatum] between boknings_dag8a AND boknings_dag8b then boknings_ant8 as antalet END IF ";

Please lead me right.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
theGhost_k8Database Consultant

Commented:
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag1a AND boknings_dag1b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag2a AND boknings_dag2b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag3a AND boknings_dag3b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag4a AND boknings_dag4b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag5a AND boknings_dag5b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag6a AND boknings_dag6b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag7a AND boknings_dag7b union  
SELECT boknings_ant1 as antalet FROM bokningsdagar where $_POST[bokningsdatum] between boknings_dag8a AND boknings_dag8b ;

If you're not quoting your post parameters then you will have to quote them.
Theo KouwenhovenApplication Consultant

Commented:
I'm not sure what you like to do but I think you need the CASE and not the IF
$SQL1 = " SELECT * FROM bokningsdagar
Where 
CASE WHEN $_POST[bokningsdatum] between boknings_dag1a AND boknings_dag1b then boknings_ant1
     WHEN $_POST[bokningsdatum] between boknings_dag2a AND boknings_dag2b then boknings_ant2 
     WHEN $_POST[bokningsdatum] between boknings_dag3a AND boknings_dag3b then boknings_ant3 
     WHEN $_POST[bokningsdatum] between boknings_dag4a AND boknings_dag4b then boknings_ant4 
     WHEN $_POST[bokningsdatum] between boknings_dag5a AND boknings_dag5b then boknings_ant5 
     WHEN $_POST[bokningsdatum] between boknings_dag6a AND boknings_dag6b then boknings_ant6 
     WHEN $_POST[bokningsdatum] between boknings_dag7a AND boknings_dag7b then boknings_ant7 
     WHEN $_POST[bokningsdatum] between boknings_dag8a AND boknings_dag8b then boknings_ant8 END antalet ";

Open in new window

Author

Commented:
With theGhost_k8's solution I get this message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between boknings_dag1a AND boknings_dag1b union SELECT boknings_ant2 as antalet' at line 1.

Eith murphey2's solution I get this message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between boknings_dag1a AND boknings_dag1b then boknings_ant1 WHEN between' at line 3
Theo KouwenhovenApplication Consultant

Commented:
Yeah, you are building a string, so the variable myt be outside the constant part like :

$SQL1 = " SELECT * FROM bokningsdagar
Where CASE WHEN" + $_POST[bokningsdatum] + " between boknings_dag1a AND boknings_dag1b then boknings_ant1" etc etc

I'm not sure if you have to use the + to connect the parts together, if it's PHP the Plus met be a dot.

Author

Commented:
murphy2, I am not building a string.

I thought for a moment this would work:
$SQL = " SELECT  bokningsdagar.*,
CASE WHEN $_POST[bokningsdatum] between 'boknings_dag1a' AND 'boknings_dag1b' then 'boknings_ant1'
     WHEN $_POST[bokningsdatum] between 'boknings_dag2a' AND 'boknings_dag2b' then 'boknings_ant2'
     WHEN $_POST[bokningsdatum] between 'boknings_dag3a' AND 'boknings_dag3b' then 'boknings_ant3'
     WHEN $_POST[bokningsdatum] between 'boknings_dag4a' AND 'boknings_dag4b' then 'boknings_ant4'
     WHEN $_POST[bokningsdatum] between 'boknings_dag5a' AND 'boknings_dag5b' then 'boknings_ant5'
     WHEN $_POST[bokningsdatum] between 'boknings_dag6a' AND 'boknings_dag6b' then 'boknings_ant6'
     WHEN $_POST[bokningsdatum] between 'boknings_dag7a' AND 'boknings_dag7b' then 'boknings_ant7'
     WHEN $_POST[bokningsdatum] between 'boknings_dag8a' AND 'boknings_dag8b' then 'boknings_ant8'
END
as antalet FROM bokningsdagar ";

When running the query, it doesn't produce any warnings, but it doesn't give the value I am expecting. Why don't I get any values?
Theo KouwenhovenApplication Consultant

Commented:
Hi lericson,

If you have $SQL = "something", then you are building a query string that is executed next step.

Your format is right (my example contained the "where" that was wrong, sorry)

what you can add is after the last Case an else:

WHEN $_POST[bokningsdatum] between 'boknings_dag8a' AND 'boknings_dag8b' then 'boknings_ant8'
ELSE 'test'
END


(Some SQL's use "OTHER" instead of  "ELSE" )

If  you get "test" back in your result, every between failed, then you have to check the format of the $_POST[bokningsdatum] and the 'boknings_dagXx'

The fields boknings_dagXx are date fields, $_POST[bokningsdatum] probably isn't,
so you first have to change the selection field to a date format.

Author

Commented:
Thanks murphey2. Your comments and information are appreciated.
The next question is how do I change $_POST[bokningsdatum] to a date format?
It is sent to this page as YYYY-MM-DD, e. g. 2012-06-23.
Application Consultant
Commented:
Hi lericson,

I'm not sure if the fields 'boknings_dag1a' AND 'boknings_dag1b' should be quoted?

this is date compare, quote indicate string compare.
Maybe it's enough to change that, otherwise the date conversion can be done like this:

For your $_POST[bokningsdatum] it probably should be:
$myDate = date("Y-m-d", strtotime($_POST[bokningsdatum]));

Otherwise checkout Example #2: Intricacies of DateTime::createFromFormat() in this link

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial