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?
 
Chris StanyonConnect With a Mentor Commented:
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
 
Chris StanyonCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Chris StanyonCommented:
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
 
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 StanyonConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.