asked on

MySQL query: if and between

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.

theGhost_k8

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 Kouwenhoven

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

Lennart Ericson

ASKER

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 Kouwenhoven

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.

Lennart Ericson

ASKER

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 Kouwenhoven

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.

Lennart Ericson

ASKER

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.

ASKER CERTIFIED SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial