CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 6233

How to get in MySQL defined names into php?

If I have a mysql query like

(SELECT COUNT(*) FROM data AS amount)
UNION
(SELECT COUNT(*) FROM data WHERE some < 50 AS something)

and then create an array with php like this $row = mysqli_fetch_array($sql, MYSQLI_ASSOC);.

How can I now address each of the AS names. This does not work: echo $row["amount"];. The second question I have is why can't I use AS something when having a WHERE clause?

Upvotes: 0

Views: 93

Answers (4)

Stephan
Stephan

Reputation: 8090

Try this:

(
  SELECT 
    'amount1' as za_name, 
    COUNT(*) as za_count 
  FROM 
    data
) 
UNION 
(
  SELECT 
    'amount2' as za_name, 
    COUNT(*) as za_count 
  FROM 
    data 
  WHERE some < 50 
)

Then you can differentiate by $row[za_name] and get the amount $row[za_count]

For the second question : you can use it if you make a temp table :

SELECT
  tmp.za_name,
  tmp.za_count
FROM (
  SELECT 
    'amount2' as za_name, 
    COUNT(*) as za_count 
  FROM 
    data 
  WHERE some < 50 
) as tmp

Upvotes: 2

gen_Eric
gen_Eric

Reputation: 227270

In a UNION the row names/aliases for the entire query are whatever they are in the first query.

So if you have 

SELECT field1 AS A
UNION
SELECT field2 AS B

Your final result will only have an A field, which will have both field1 and field2.

In your query, you want to alias the COUNT(*), not the table.

(SELECT COUNT(*) AS amount FROM data)
UNION
(SELECT COUNT(*) FROM data WHERE some < 50)

Nor $row['amount'] will be all of the COUNT(*) rows from the entire query.

Upvotes: 1

shadyyx
shadyyx

Reputation: 16055

You have to edit the query this way (aliases on the columns, too)

(SELECT COUNT(*) as amount FROM data)
UNION
(SELECT COUNT(*) as amount FROM data WHERE some < 50 AS something)

This way You are able to address $result['amount'] as a result from the fetch assoc method.

Upvotes: 0

fullybaked
fullybaked

Reputation: 4127

in your query 

(SELECT COUNT(*) FROM data AS amount) UNION (SELECT COUNT(*) FROM data WHERE some < 50 AS something)

You are aliasing the table data to the name amount rather than the sub-query

Upvotes: 0

Related Questions