current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm trying to create a PHP script that will echo the average value of each of my columns and array them all in a single JSON array.

I know that this is how I can get the average for a column.

select avg(`sales`) as sales  from `mytable`

But I'm not sure how to string this together

select avg(`sales`) as sales  from `mytable`
select avg(`profit`) as profit  from `mytable`
select avg(`costs`) as costs  from `mytable`

To get something like this echo'd from PHP:

[
{
    "sales": 56812
},
{
    "profit": 2312
},
{
    "costs": 324
}
]
share|improve this question
    
json_encode() - never form the JSON by hand. You could use a UNION query or JOIN to get all of the data in one shot. –  Jay Blanchard Feb 4 at 17:12
    
You shouldn't need to run three different queries. –  Devon Feb 4 at 17:14

1 Answer 1

up vote 0 down vote

You're close.

select
    avg(sales) as sales,
    avg(profit) as profit,
    avg(costs) as costs
from mytable;

Note that I removed the backticks around your column and table names because they are not necessary and are just visual noise and are one other thing to make mistakes about.

share|improve this answer
    
Then fetch it into an associative array and use json_encode. I think is what the OP was getting at. –  Devon Feb 4 at 17:15
    
I tried this, but getting 500 error: $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password); $sql= ("select avg(sales) as sales, avg(profit) as profit, avg(costs) as costs from mytable;"); $result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC); header('Content-type: application/json'); echo json_encode($result); –  jonmrich Feb 4 at 19:08
    
"500 error" isn't enough to know what the problem is. There's an actual useful error message in your web server's error log. –  Andy Lester Feb 4 at 19:11
    
Sorry...thought I put that in there. The error message: Call to a member function fetchAll() on a non-object in /averages.php –  jonmrich Feb 4 at 19:16
    
This is more debugging than can be done in comments. The SQL I gave you should work, but there is apparently something else wrong with your code. –  Andy Lester Feb 4 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.