UNION query works in mysql but doesn't work the same in php

Question

When I run this query in mysql it returns all the requested results correctly:

SELECT row_id FROM table1 WHERE status = n 
UNION ALL 
SELECT row_id FROM table2 WHERE status = n 
UNION ALL 
SELECT row_id FROM table3 WHERE status = n

However when I run it from PHP it returns only one record, the first row that meets the requested condition.

$query = mysqli_query($link, "SELECT row_id FROM table1 WHERE status = n 
UNION ALL 
SELECT row_id FROM table2 WHERE status = n 
UNION ALL 
SELECT row_id FROM table3 WHERE status = n");

print_r(mysqli_fetch_array($query));

So print_r shows the following: Array ( [0] => 1 [row_id] => 2580 ), where 2580 is a id of the row from table1 that meets the requested condition.

How to make it return the full array of results?

Can you try it with only "UNION" not with "UNION ALL" and also are you sure that another two tables have data? — Alauddin Ansari, Mar 05 '14 at 13:46
http://stackoverflow.com/questions/14456529/mysqli-fetch-array-while-loop-columns - this might help — AMadmanTriumphs, Mar 05 '14 at 13:46
I had similar problem, best way is to echo the query, it identified the error for me. Basically the $query was empty because it was ill formatted because of using multiple ' ' in it. — TheTechGuy, Feb 18 '19 at 05:03

score 0 · Accepted Answer · answered Mar 05 '14 at 13:48

0

You can try to print data in loop like:

$query = mysqli_query($link, "SELECT row_id FROM table1 WHERE status = n 
UNION ALL 
SELECT row_id FROM table2 WHERE status = n 
UNION ALL 
SELECT row_id FROM table3 WHERE status = n");

while($row = mysqli_fetch_array($query)){
    print_r($row);
}

Simply mysqli_fetch_array only return current row in the loop. You need to loop through it to get all the results.

answered Mar 05 '14 at 13:48

Alauddin Ansari

250
3
12

I had this loop on the results all along, but I must have made a mistake and it did not work out and that is when confusion started for me. – qwaz Mar 05 '14 at 14:00

score 0 · Answer 2 · answered Mar 05 '14 at 13:49

0

I never knew that WHERE status = n is valid SQL. Shouldn't it be WHERE status = 'n'
Try looping your mysqli_fetch_array() like this:

while($row = mysqli_fetch_array($query)){ print_r($row); }

answered Mar 05 '14 at 13:49

MonkeyZeus

20,375
4
36
77

1

It is perfectly valid SQL, as `n` *could* be a column name; but somehow I suspect it was used a placeholder. – eggyal Mar 05 '14 at 13:53
@eggyal considering OPs first statement of `When I run this query in mysql` then it is safe to assume that `n` is a column name? I might just be inexperienced but this would only make sense if they were doing a `JOIN` – MonkeyZeus Mar 05 '14 at 13:58
I agree that it is probably a placeholder, most likely for an integer. Quotes are only needed to denote strings, so `WHERE column=2` is perfectly fine. – Tularis Mar 05 '14 at 14:04
Yes, `n` *could* be a column name, even without a `JOIN` (and irrespective of whether it is run in MySQL or using some API): see, for example, [sqlfiddle](http://sqlfiddle.com/#!2/9e5ed/1/0). – eggyal Mar 05 '14 at 15:34

UNION query works in mysql but doesn't work the same in php

2 Answers2