MySQL "case as column" question in WHERE clause

I'm setting up a new column with this:

SELECT * , 
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1 
WHEN  'two' THEN 2 
WHEN  'three' THEN 3 
WHEN  'four' THEN 4 
WHEN  'five' THEN 5 
WHEN  'six' THEN 6 
WHEN  'seven' THEN 7 
WHEN  'eight' THEN 8 
WHEN  'nine' THEN 9 
WHEN  'ten' THEN 10 
ELSE 0 
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
AND numfield >= 3

Open in new window


So - numfield is created by the CASE clause...but in the WHERE clause it's telling me that numfield is an unknown column.

Do I have to declare the column or something for it to be recognized - and what would the syntax for that be?  

Thanks in advance!
LVL 2
erzoolanderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
This worked well for me.  Most of the script is just setup.  The test data is near line 32, the query is near line 175.

Please see: http://iconoun.com/demo/temp_erzoolander.php

<?php // demo/temp_erzoolander.php

/**
 * Demonstrate some of the basics of MySQLi
 *
 * References that must be understood to use PHP and MySQL(i)
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */

// RAISE THE ERROR REPORTING LEVEL TO THE HIGHEST POSSIBLE SETTING
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// CREATE AN ARRAY OF TEST DATA
$test_array = [ 'one', 'two', 'three', 'four', 'gooseball' ];


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, thing VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_array as $thing)
{
    $safe  = $mysqli->real_escape_string($thing);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( thing ) VALUES ( '$safe' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// VERIFY THE INSERTIONS
$sql = "SELECT * FROM my_table ORDER BY thing";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND NO DATA ";
}
else
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND $num_fmt ROWS OF DATA ";
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// TRY A TRANSLATION QUERY
$sql = "
SELECT * ,
CASE thing
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM my_table
ORDER BY numfield DESC
"
;

$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND NO DATA ";
}
else
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND $num_fmt ROWS OF DATA ";
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
erzoolanderAuthor Commented:
In your query of

QUERY:
SELECT * ,
CASE thing
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM my_table
ORDER BY numfield DESC

Does it also work if you say

FROM my_table
WHERE numfield > 2

because that's where it's hanging up on me - weirdly
0
Ray PaseurCommented:
Hmm, no - the WHERE clause is sick.  Seems like a good argument for using ALTER TABLE and putting the numeric value into a column.
0
Ryan ChongCommented:
try;

SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
AND
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END >= 3

or you can create a subquery, like:

select a.*
from
(
SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
) a
Where a.numfield >= 3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.