Ryan Castle Ryan Castle - 6 months ago 7
PHP Question

MySQL - Case/If statements

I'm writing a query to fetch data from a table named

contracts
, with a join to the table
user_data
or
teams
. The table
contracts
has a column in it named
contracteeType
, in which I want to use as the conditional variable for the argument. In PHP code terms, this would be done as the following:

if ($contracteeType === 'user') {
// SELECT data FROM user_data table
} else {
// SELECT data FROM teams table
}


I have tried researching this, and I came across a CASE statement solution to the problem, but it doesn't appear to be working on my server. The following is what I had come up with:

SELECT *
FROM contracts
CASE
WHEN contracts.contracteeType = 'user'
THEN 'user_data'
WHEN contracts.contracteeType = 'team'
THEN 'teams'
END as tableName
CASE
WHEN contracts.contracteeType = 'user'
THEN 'userID'
WHEN contracts.contracteeType = 'team'
THEN 'teamID'
END as colName
INNER JOIN tableName
ON tableName.colName = '.$contractID


How could I perform this task with my query, and fetch data from a table depending on the value from another?

Answer

The only way I can see an "if" working for you is to use a store procedure. I find your question a little confusing, but I am sure that you can solve it by making and calling a store procedure.

This approach has some drawbacks (lots of code, different from a simple query) however it has many good points (secure, fast as pre compiled and infinitely flexible for "PHP" type logic).

This is an example stored procedure...

DELIMITER //

DROP PROCEDURE IF EXISTS MyProcedure; // 
CREATE PROCEDURE MyProcedure()
BEGIN

SET @MyVarA = (SELECT cola FROM tablea);
SET @MyVarB = (SELECT colb FROM tableb);

IF @MyVarA = 'a' AND @MyVarB = 'b' THEN 
    SELECT A FROM B;
END IF

IF @MyVarA = 'd' AND @MyVarB = 'e' THEN 
    SELECT D FROM E;
END IF

END; //
Comments