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

Question

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...

I think you're going to need a FROM in there somewhere, so how about showing us your entire code, not just remnants. — DevlshOne
– DevlshOne, Commented Aug 25, 2013 at 14:57
SELECT CurrencyAbbreviation, TRIM(TRAILING '0' FROM CurrencyRate/Units) AS FinalCurrencyRate FROM 2013Currencies WHERE (DateOfCurrencyRate = IFNULL(?,0) AND CurrencyAbbreviation = IFNULL(?,0)) OR (DateOfCurrencyRate = IFNULL(?,0) AND CurrencyAbbreviation = IFNULL(?,0)) — user2465936
– user2465936, Commented Aug 25, 2013 at 14:58
Do you mean what mysql table looks like? Yes, I am using php — user2465936
– user2465936, Commented Aug 25, 2013 at 15:01

Bohemian · Accepted Answer · 2013-08-25 15:12:51Z

1

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.

edited Aug 25, 2013 at 15:12

answered Aug 25, 2013 at 15:05

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2465936 Over a year ago

Experimenting with query. If use WHERE and OR OR etc. then UNION ALL must use for each OR? And LIMIT 1 must use only once (at the end)?

Bohemian Over a year ago

Yes. UNION ALL may be used to merge the results of many queries, so you could code your query using a variety of comparisons and still have a final "default". And yes, the ZLIT 1 is applied to the result of the unioned rows (which I aliased as "x")

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related