Referencing Function Inside MySQL Query?

[cpenk]

Hi there,

 

I'm a beginner with MySQL, and I'm modifying an existing user/event sign up database. My question is, how do I refer to a function within a CASE-WHEN-THEN component of a MySQL query?

 

Specifically, I want to display our "secret"-type events only for people who are logged in. Using if-else statements with the following structure works as expected:

 

if ($session->logged_in){
}

 

I would use that same syntax within the MySQL query, but they require CASE syntax instead. So my solution thus far has been to define a one-off variable ($one_off for the sake of clarity) before the query, and make it equivalent to the result of evaluating the logged-in function. Here is how I defined the variable:

if ($session->logged_in)
{$one_off = 1;}
else {$one_off = 0;};

 

I then refer to that variable within the CASE-WHEN-THEN structure as follows, in order to restrict the events selected by the query:

SELECT * FROM `".TBL_EVENTS."` WHERE `archived` = 0 AND
CASE WHEN ('$one_off' = 1) THEN type >0
ELSE type != 11 AND type != 12
END

 

To be honest, I'm not sure what the "->" notation signifies - the session.php file included on the events page defines both a session class (within which a $logged_in variable is defined) and a $session variable. Ultimately I want to include something similar to ($session->logged_in) within the CASE structure. I'm sure there's a simple way to do this, without passing it through the extra variable. It might just be a syntax issue. Thank you in advance for your help.

[dan2684]

If you just want to use your $session object in the query can't you do it like this?

 

SELECT * FROM `".TBL_EVENTS."` WHERE `archived` = 0 AND
CASE WHEN ('".$session->logged_in."' = 1) THEN type >0
ELSE type != 11 AND type != 12
END

 

Do you get an error? What does it say?

[cpenk]

Dan, thank you SO much for the solution. It was a syntax issue. I will read up on objects - in the meantime, this saves me quite a bit of code.

[dan2684]

No worries - good luck! :-)