CODEWITHSUNDEEP
phpmysql
user2465936
user2465936

Reputation: 1040

Need mysql return array with zero value (or empty or NULL)

I have a query like 

SELECT FirstColumn
WHERE SecondColumn = ?
AND ThirdColumn = ?

There may be situation that for one ? value is provided, but for another ? there is no value. Like

[0] => 123
[1] =>

So query may be SELECT FirstColumn WHERE SecondColumn = 123 AND ThirdColumn = ''

When experimented, see that for such query mysql returns nothing (no array).

But necessary is to return array with empty value or NULL or zero. Like

[0] => Array
    (
        [FirstColumn] => 0
    )

So trying to find solution.

One solution may be to create row in mysql where FirstColumn, SecondColumn and ThirdColumn is 0. And with php modify data for ? so if empty variable, then data for ? is 0. Then use the same mysql query. Seems this is not very good solution.

Other solution would be query something like this

SELECT FirstColumn
WHERE SecondColumn = IFNULL(?,0) 
AND ThirdColumn = IFNULL(?,0)

Created mysql row with 0, executed query. All works, but does not return array element where SecondColumn or ThirdColumn is 0. Something wrong with my query or may be IFNULL can not be used in such a way. Please, advice what need to correct? Or may be better solution?

Tried with 

SELECT FirstColumn
WHERE SecondColumn = 0
AND ThirdColumn = 0`

and get array with 0 value. Possibly IFNULL used incorrectly...

Upvotes: 0

Views: 1400

Answers (1)

Bohemian
Bohemian

Reputation: 425208

This query will always return exactly one row:

SELECT * FROM (
  SELECT FirstColumn
  FROM mytable
  WHERE SecondColumn = ?
  AND ThirdColumn = ?
  UNION ALL
  SELECT NULL -- code your "not found" value here
) x
LIMIT 1

UNION ALL preserves the order rows are selected, so if the first query (your original one) returns a row, that is the one returned, otherwise the row created by the solitary select is returned.

You can choose any value for the "default" value as long as it is compatible with the data type of FirstColumn.

Upvotes: 1

Related Questions