create array with results from MySQL query php

hi there,
i am going to be using a php graphing library called phpgraphlib (http://www.ebrueggeman.com/phpgraphlib/) and need to create an array with with data in it that i drawing from my database.

an example query will draw the number of times an event occurs on a range of days, for example:

SELECT date, count FROM calllog WHERE (date BETWEEN '$startdate' AND '$enddate') AND (company_id = '$companyid') ORDER BY date, company_id ASC

the array i need created from this query needs to look like this:

$resuts = array("12/2/10"=>99, "13/2/10"=>98, "14//2/10"=>70, "15/2/10"=>90);


thanks for the help, i hope that is clear!
LVL 1
alexanderfotiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Hi,

Give this a try.


$sql = "SELECT date, count FROM calllog WHERE (date BETWEEN '$startdate' AND '$enddate') AND (company_id = '$companyid') ORDER BY date, company_id ASC";
$query_result = mysql_query($sql); 

$results = array();

while ($row = mysql_fetch_assoc($query_result))
{
	$results[$row['date']] = $row['count'];
}

Open in new window

0
alexanderfotiAuthor Commented:
no joy, when i do a print_r i get this:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/janda/temp2.php  on line 15
Array ( )

the whole page is attached.  i have commented out the bits about the graph.


<?php
//include_once ("graph/phpgraphlib.php");

//$query = base64_decode($q);



include_once ("dbconnect.php");

$sql = "SELECT date, count FROM calllog WHERE (date BETWEEN '$startdate' AND '$enddate') AND (company_id = '$companyid') ORDER BY date, company_id ASC";
$query_result = mysql_query($sql); 

$results = array();

while ($row = mysql_fetch_assoc($query_result))
{
        $results[$row['date']] = $row['count'];
}

print_r($results);


//$graph = new PHPGraphLib(600,400);
//
//$data = array("12.2.10"=>99, "13.2.10"=>98, "14.2.10"=>70, "15.2.10"=>90);
//
//$graph->addData($results);
//$graph->setTitle('Number of Calls per Day');
//$graph->setGradient('red', 'maroon');
//$graph->createGraph();

?>

Open in new window

0
Chris StanyonWebDevCommented:
OK, That's an issue with your SQL statement.

To get what you want, you'll need to include a GROUP BY clause.

Something like:



$sql = "SELECT Date, COUNT(company_id) AS 'Count' FROM calllog WHERE (DATE BETWEEN '$startdate' AND '$enddate') AND (company_id = '$companyid') GROUP BY Date ORDER BY Date ASC;"

//no need to include company_id in the sort order because all the records will have the same company ID.

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Avinash ZalaWeb ExpertCommented:
Your query should like below:

$sql = "SELECT date, count(*) as total FROM calllog WHERE (date BETWEEN '$startdate' AND '$enddate') AND (company_id = '$companyid') ORDER BY date, company_id ASC";

Because count is reserved word for the mysql statement.

Hope this make sense.
Thanks
Addy
0
flytox06Commented:
to produce exactly the result you need, use :

SELECT date_format(date, "%e/%c/%Y") as formateddate, count(*) as counter
FROM calllog
WHERE date BETWEEN '$startdate' AND '$enddate'
AND company_id = '$companyid'
GROUP BY 1
ORDER BY 1;

This will display date as you described (day without leading 0, month without leading 0). If you need leading 0, respectively use %d and %m.
0
flytox06Commented:
btw, ORDER BY is not necessary in your cas since GROUP BY is already sorting.
0
alexanderfotiAuthor Commented:
hi there,
i have got the query working as required by using:

SELECT company_id, COUNT(id) AS numcalls FROM calls WHERE (company_id = '3') AND (created BETWEEN '2010-01-01 00:00:00' AND '2010-04-17 23:59:59')

the output or a print_r is:

Array ( [3] => 3 )

this is fine for one result, but i need to repeat this query for each client in the calls table.  how can i do this?  
0
alexanderfotiAuthor Commented:
to clarify, i am looking for a query that will output something like:

company_id     |        numcalls
        3              |                5
        4              |                 3
        5              |                11

and so on for each company that exists
0
Chris StanyonWebDevCommented:
The reason you're getting a result for only 1 company is the WHERE (company_id ='3') in your SQL statement. If you want all the companies returned, then just change the WHERE statement.



$sql = "SELECT company_id, COUNT(id) AS numcalls FROM calls WHERE (created BETWEEN '2010-01-01 00:00:00' AND '2010-04-17 23:59:59')";

$query_result = mysql_query($sql); 

$results = array();

while ($row = mysql_fetch_assoc($query_result))
{
        $results[$row['company_id']] = $row['numcalls'];
}

Open in new window

0
alexanderfotiAuthor Commented:
the print_r is now showing

Array ( [2] => 16 )
0
alexanderfotiAuthor Commented:
FYI, i have attached the SQL of the table calls
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


CREATE TABLE IF NOT EXISTS `calls` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `sqldate` varchar(40) NOT NULL,
  `openby` datetime NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `company_id` int(6) NOT NULL,
  `contact_id` int(6) NOT NULL,
  `isadmin` int(2) NOT NULL,
  `summary` varchar(200) NOT NULL,
  `type` int(2) NOT NULL,
  `catagory` int(3) NOT NULL,
  `owner` int(6) DEFAULT NULL,
  `support_group` int(2) DEFAULT NULL,
  `status` int(2) NOT NULL,
  `priority` int(2) NOT NULL,
  `opener` int(5) NOT NULL,
  `resolver` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `summary` (`summary`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1017 ;


INSERT INTO `calls` (`id`, `sqldate`, `openby`, `created`, `company_id`, `contact_id`, `isadmin`, `summary`, `type`, `catagory`, `owner`, `support_group`, `status`, `priority`, `opener`, `resolver`) VALUES
(1000, '2010-03-19T14:14:57+00:00', '2010-03-22 09:14:57', '2010-03-19 14:14:57', 2, 1, 1, 'sdf', 2, 999, 1, NULL, 4, 2, 1, 1),
(1001, '2010-03-19T14:15:08+00:00', '2010-03-22 09:15:08', '2010-03-19 14:15:08', 3, 1, 1, 'asdf', 1, 999, 1, NULL, 4, 2, 1, 1),
(1012, '2010-04-17T15:25:21+00:00', '2010-04-19 13:00:00', '2010-04-17 16:25:21', 1, 1, 1, 'dfg', 1, 999, 1, NULL, 4, 2, 1, 1),
(1011, '2010-04-17T10:58:38+00:00', '2010-04-19 13:00:00', '2010-04-17 11:58:38', 3, 1, 1, 'sdf', 1, 999, 1, NULL, 4, 3, 1, 1),
(1002, '2010-03-19T14:17:40+00:00', '2010-03-22 09:17:40', '2010-03-19 14:17:40', 3, 1, 1, 'asdf', 2, 999, 1, NULL, 4, 2, 1, 1),
(1003, '2010-03-19T14:26:54+00:00', '2010-03-22 09:26:54', '2010-03-19 14:26:54', 1, 1, 1, 'asdf', 3, 999, 1, NULL, 4, 3, 1, 1),
(1004, '2010-03-21T20:04:47+00:00', '2010-03-22 13:00:00', '2010-03-21 20:04:47', 1, 1, 1, 'sadf', 1, 999, 1, NULL, 4, 2, 1, 1),
(1005, '2010-03-22T10:25:40+00:00', '2010-03-22 14:25:40', '2010-03-22 10:25:40', 1, 1, 1, 'sadf', 1, 999, 1, NULL, 4, 3, 1, 1),
(1006, '2010-03-22T10:26:13+00:00', '2010-03-22 14:26:13', '2010-03-22 10:26:13', 2, 1, 1, 'sadf', 1, 999, 1, NULL, 4, 2, 1, 1),
(1007, '2010-03-22T10:26:22+00:00', '2010-03-22 14:26:22', '2010-03-22 10:26:23', 1, 1, 1, 'sadf', 3, 999, 1, NULL, 4, 2, 1, 1),
(1008, '2010-03-22T10:28:51+00:00', '2010-03-22 14:28:51', '2010-03-22 10:28:51', 1, 1, 1, 'asdf', 1, 999, 1, NULL, 4, 2, 1, 1),
(1009, '2010-03-27T12:14:56+00:00', '2010-03-29 13:00:00', '2010-03-27 12:14:56', 2, 1, 1, 'sadf', 1, 999, 1, NULL, 4, 2, 1, 1),
(1010, '2010-04-15T10:38:37+00:00', '2010-04-15 14:38:37', '2010-04-15 11:38:37', 1, 1109, 0, 'dfg', 1, 999, 1, NULL, 4, 3, 1, 1),
(1014, '2010-04-17T15:28:51+00:00', '2010-04-19 13:00:00', '2010-04-17 16:28:51', 2, 1, 1, 'cat 2', 1, 2, 1, NULL, 4, 2, 1, 1),
(1015, '2010-04-17T15:59:12+00:00', '2010-04-19 13:00:00', '2010-04-17 16:59:12', 1, 1, 1, 'BSOD', 3, 0, 1, NULL, 4, 2, 1, 1),
(1016, '2010-04-17T16:00:36+00:00', '2010-04-19 13:00:00', '2010-04-17 17:00:36', 2, 1, 1, 'asdf', 2, 5, 1, NULL, 4, 2, 1, 1);

Open in new window

0
Chris StanyonWebDevCommented:
Alexander,

You need to include a GROUP BY in your SQL statement,

SELECT company_id, COUNT(id) AS numcalls FROM calls WHERE (created BETWEEN '2010-01-01 00:00:00' AND '2010-04-17 23:59:59') GROUP BY company_id;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.