Solved

Trouble returning the right data for highcharts using php, mysql

Posted on 2013-12-08
10
1,792 Views
Last Modified: 2013-12-08
HI,
Hopefully this will make some sense as I find explaining it almost as hard as trying to do it.

Below is a query to get the total amount of 'deals' per day and the date for each day that has at least one deal in a given time. I'm also trying to format the results to suit the data requirements for highcharts. Prior to returning the array I'm json_encoding ($deals).

//deals by date
$result = mysql_query("SELECT Count(id) AS dealno, DATE_FORMAT(purchaseCreated,'%Y, %m, %d') AS purchaseDate FROM deals WHERE DATE_FORMAT(purchaseCreated,'%Y-%m-%d') >= '$from' AND DATE_FORMAT(purchaseCreated,'%Y-%m-%d') <= '$to'");
$deals = array(); // create an array to dump in each iteration

while ($row = mysql_fetch_array($result)) {
 $deals[] = 'Date.UTC('.$row['purchaseDate'].'),'.$row['dealno'].'';
}

Open in new window

Format required for highcharts for each day with deals should look like:
[Date.UTC(2013, 12, 09),2],[Date.UTC(2013, 12, 10),2]
Which is what I'm trying to achieve above.

What I'm noticing is 2 different problems:
1) My loop above isn't returning more than one date (if there are 2 deals on 2 different days, I seem to only get the one date with the overall total amount of deals). I suspect my logic above is wrong.

2) Provided I get over that hurdle, I seem to have a problem with double-quotes being returned in the json. Currently looks like this:
"deals":["Date.UTC(2013, 12, 09),2"],

Need to lose the double-quotes with the end result looking like:
"deals":[Date.UTC(2013, 12, 09),2],

Any help would be much appreciated. Been at it for 2 hours and haven't got much further.
0
Comment
Question by:tjyoung
  • 7
  • 3
10 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704263
You probably already know that you're using an obsolete data base extension.  If you need some help with the required transition to something that will keep working in the future, this article can provide a roadmap.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Please post the CREATE TABLE statement for the deals table.  I'll try to show you some ways to get the diagnostic information you need.
0
 
LVL 1

Author Comment

by:tjyoung
ID: 39704266
Great, thanks. I think this is what you'd be after.
-- phpMyAdmin SQL Dump
-- version 3.5.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Dec 08, 2013 at 08:45 AM
-- Server version: 5.0.95
-- PHP Version: 5.3.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `steele_dealboxx`
--

-- --------------------------------------------------------

--
-- Table structure for table `deals`
--

CREATE TABLE IF NOT EXISTS `deals` (
  `id` int(20) NOT NULL auto_increment,
  `user_id` int(20) NOT NULL,
  `lead_id` int(20) NOT NULL,
  `parent_id` int(20) NOT NULL,
  `isTradeIn` varchar(40) NOT NULL,
  `purchaseType` varchar(40) NOT NULL,
  `purchaseYear` int(40) NOT NULL,
  `purchaseMake` varchar(255) NOT NULL,
  `purchaseModel` varchar(255) NOT NULL,
  `purchaseTrim` varchar(255) default NULL,
  `purchasePrice` int(40) NOT NULL,
  `purchaseStock` varchar(255) default NULL,
  `purchaseCreated` datetime NOT NULL,
  `purchaseModified` datetime NOT NULL,
  `purchaseStatus` varchar(40) NOT NULL default 'Current',
  `archive` int(11) NOT NULL default '0',
  `locked` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=126 ;

--
-- Dumping data for table `deals`
--

INSERT INTO `deals` (`id`, `user_id`, `lead_id`, `parent_id`, `isTradeIn`, `purchaseType`, `purchaseYear`, `purchaseMake`, `purchaseModel`, `purchaseTrim`, `purchasePrice`, `purchaseStock`, `purchaseCreated`, `purchaseModified`, `purchaseStatus`, `archive`, `locked`) VALUES
(124, 79, 502, 78, 'noTrade', 'NEW', 2013, 'CADILLAC', 'ATS STANDARD 4DR SEDAN AWD', NULL, 23000, '', '2013-12-09 04:57:09', '2013-12-09 06:11:43', 'Deal', 1, 1),
(125, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK', 'ENCLAVE 4DR SUV', NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704283
Well, this is not really a question with an answer, so much as a process.  Work with me and I'll try to help you through it.  There are a lot of moving parts that need individual attention, and as we work through them we can get to the answer.

Please see Antipractice #9a.  When you create the query string in the function call, you cannot print out the query string.  A better strategy is to create the query string in its own variable, and then pass the variable to the function call.

Please see AntiPractice #23.  A good strategy is to test for the success or failure of a query, and trigger an error if the query fails (or at least log the event for future study).  In any case, a failed query will result in a null data set.

Please see AntiPractice #26. MySQL_Fetch_Array() should not even exist - it's a drag on performance to return twice as much data as you need.  

I think this is the first thing I would do - tidy up the code and add error visualization, as well as data visualization.  The output from var_dump() will show you what the query is returning.  Once you can see that, you can use phpMyAdmin to compare the return values to the original data in your deals table.  Obviously I do not have you data model so I cannot test the code; please feel free to correct any typos.  When you have the output from var_dump() you will find it is easier to read if you use "view source" in the browser.  Please copy that and paste it into the code snippet here.

// CREATE THE QUERY STRING FOR DEALS BY DATE
$sql 
= 
"
SELECT 
  Count(id) AS dealno
, DATE_FORMAT(purchaseCreated,'%Y, %m, %d') AS purchaseDate 
FROM deals 
WHERE DATE_FORMAT(purchaseCreated,'%Y-%m-%d') >= '$from' 
AND   DATE_FORMAT(purchaseCreated,'%Y-%m-%d') <= '$to'
"
)
;

// SHOW THE QUERY WE ARE ABOUT TO RUN
print_r($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS

// ITERATE OVER THE RESULTS SET TO VISUALIZE THE RETURNED DATA
while ($row = mysql_fetch_object($res))
{
    // SHOULD SEE STRING VARIABLES "dealno" AND "purchaseDate"
    var_dump($row);
}

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704284
Hey, this is great!  I will try to set up a test.  Thanks! ~Ray
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704342
I've gotten this far.  Next we reformat the results set into a JSON-like string.  I say "JSON-like" because JSON will want quotes around string variables, and everything that comes back in the SQL results set will be in string format.  See JSON.

<?php // RAY_EE_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// TEST DATA SET
$cre
=
"
CREATE TEMPORARY TABLE `deals`
( `id`               INT(20)      NOT NULL AUTO_INCREMENT PRIMARY KEY
, `user_id`          INT(20)      NOT NULL
, `lead_id`          INT(20)      NOT NULL
, `parent_id`        INT(20)      NOT NULL
, `isTradeIn`        VARCHAR(40)  NOT NULL
, `purchaseType`     VARCHAR(40)  NOT NULL
, `purchaseYear`     INT(40)      NOT NULL
, `purchaseMake`     VARCHAR(255) NOT NULL
, `purchaseModel`    VARCHAR(255) NOT NULL
, `purchaseTrim`     VARCHAR(255) DEFAULT NULL
, `purchasePrice`    INT(40)      NOT NULL
, `purchaseStock`    VARCHAR(255) DEFAULT NULL
, `purchaseCreated`  DATETIME     NOT NULL
, `purchaseModified` DATETIME     NOT NULL
, `purchaseStatus`   VARCHAR(40)  NOT NULL DEFAULT 'Current'
, `archive`          INT(11)      NOT NULL DEFAULT '0'
, `locked`           INT(11)      NOT NULL DEFAULT '0'
)
"
;

$ins
=
"
INSERT INTO `deals`
  (`id`, `user_id`, `lead_id`, `parent_id`, `isTradeIn`, `purchaseType`, `purchaseYear`, `purchaseMake`, `purchaseModel`, `purchaseTrim`, `purchasePrice`, `purchaseStock`, `purchaseCreated`, `purchaseModified`, `purchaseStatus`, `archive`, `locked`)
VALUES
  (124, 79, 502, 78, 'noTrade', 'NEW', 2013, 'CADILLAC', 'ATS STANDARD 4DR SEDAN AWD', NULL, 23000, '', '2013-12-09 04:57:09', '2013-12-09 06:11:43', 'Deal', 1, 1)
, (125, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK',    'ENCLAVE 4DR SUV',            NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0)
"
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$res = $mysqli->query($cre);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $cre
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$res = $mysqli->query($ins);
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $ins
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// VERIFYING THE LOAD (IT WORKED)
$sql = "SELECT * FROM deals";
if (!$res = $mysqli->query($sql)) trigger_error($mysqli->error, E_USER_ERROR);
while ($row = $res->fetch_object()) { print_r($row); }


// CREATE THE QUERY STRING FOR DEALS BY DATE USING ISO-8601 DATE FORMATS
$from = date('Y-m-d 00:00:00', strtotime("December 8, 2013"));
$to   = date('Y-m-d 23:59:59', strtotime("December 9, 2013"));
$sql
=
"
SELECT
  DATE(purchaseCreated) AS purchaseDate
FROM deals
WHERE purchaseCreated
BETWEEN '$from' AND '$to'
"
;

// SHOW THE QUERY WE ARE ABOUT TO RUN
print_r($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
var_dump($res);

// GET THE ROW COUNT
$num = $res->num_rows;
var_dump($num);

// LOOK AT THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
}

Open in new window

0
 
LVL 1

Author Comment

by:tjyoung
ID: 39704350
The above is working as expected :)

Getting (db table create etc. I removed)
stdClass Object
(
    [id] => 124
    [user_id] => 79
    [lead_id] => 502
    [parent_id] => 78
    [isTradeIn] => noTrade
    [purchaseType] => NEW
    [purchaseYear] => 2013
    [purchaseMake] => CADILLAC
    [purchaseModel] => ATS STANDARD 4DR SEDAN AWD
    [purchaseTrim] => 
    [purchasePrice] => 23000
    [purchaseStock] => 
    [purchaseCreated] => 2013-12-09 04:57:09
    [purchaseModified] => 2013-12-09 06:11:43
    [purchaseStatus] => Deal
    [archive] => 1
    [locked] => 1
)
stdClass Object
(
    [id] => 125
    [user_id] => 79
    [lead_id] => 496
    [parent_id] => 78
    [isTradeIn] => noTrade
    [purchaseType] => NEW
    [purchaseYear] => 2012
    [purchaseMake] => BUICK
    [purchaseModel] => ENCLAVE 4DR SUV
    [purchaseTrim] => 
    [purchasePrice] => 23333
    [purchaseStock] => 
    [purchaseCreated] => 2013-12-08 08:06:09
    [purchaseModified] => 2013-12-08 10:55:49
    [purchaseStatus] => Current
    [archive] => 0
    [locked] => 0
)

SELECT
  DATE(purchaseCreated) AS purchaseDate
FROM deals
WHERE purchaseCreated
BETWEEN '2013-12-08 00:00:00' AND '2013-12-09 23:59:59'
object(mysqli_result)#4 (0) {
}
int(2)
stdClass Object
(
    [purchaseDate] => 2013-12-09
)
stdClass Object
(
    [purchaseDate] => 2013-12-08
)

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704358
Have a look at this.  I think we are getting closer!  Note the use of GROUP BY clause
http://www.laprbass.com/RAY_temp_tjyoung.php

<?php // RAY_EE_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// TEST DATA SET
$cre
=
"
CREATE TEMPORARY TABLE `deals`
( `id`               INT(20)      NOT NULL AUTO_INCREMENT PRIMARY KEY
, `user_id`          INT(20)      NOT NULL
, `lead_id`          INT(20)      NOT NULL
, `parent_id`        INT(20)      NOT NULL
, `isTradeIn`        VARCHAR(40)  NOT NULL
, `purchaseType`     VARCHAR(40)  NOT NULL
, `purchaseYear`     INT(40)      NOT NULL
, `purchaseMake`     VARCHAR(255) NOT NULL
, `purchaseModel`    VARCHAR(255) NOT NULL
, `purchaseTrim`     VARCHAR(255) DEFAULT NULL
, `purchasePrice`    INT(40)      NOT NULL
, `purchaseStock`    VARCHAR(255) DEFAULT NULL
, `purchaseCreated`  DATETIME     NOT NULL
, `purchaseModified` DATETIME     NOT NULL
, `purchaseStatus`   VARCHAR(40)  NOT NULL DEFAULT 'Current'
, `archive`          INT(11)      NOT NULL DEFAULT '0'
, `locked`           INT(11)      NOT NULL DEFAULT '0'
)
"
;

$ins
=
"
INSERT INTO `deals`
  (`id`, `user_id`, `lead_id`, `parent_id`, `isTradeIn`, `purchaseType`, `purchaseYear`, `purchaseMake`, `purchaseModel`, `purchaseTrim`, `purchasePrice`, `purchaseStock`, `purchaseCreated`, `purchaseModified`, `purchaseStatus`, `archive`, `locked`)
VALUES
  (124, 79, 502, 78, 'noTrade', 'NEW', 2013, 'CADILLAC', 'ATS STANDARD 4DR SEDAN AWD', NULL, 23000, '', '2013-12-09 04:57:09', '2013-12-09 06:11:43', 'Deal', 1, 1)
, (125, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK',    'ENCLAVE 4DR SUV',            NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0)
"
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$res = $mysqli->query($cre);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $cre
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$res = $mysqli->query($ins);
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $ins
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// VERIFYING THE LOAD (IT WORKED)
$sql = "SELECT * FROM deals";
if (!$res = $mysqli->query($sql)) trigger_error($mysqli->error, E_USER_ERROR);
// while ($row = $res->fetch_object()) { print_r($row); }


// CREATE THE QUERY STRING FOR DEALS BY DATE USING ISO-8601 DATE FORMATS
$from = date('Y-m-d 00:00:00', strtotime("December 8, 2013"));
$to   = date('Y-m-d 23:59:59', strtotime("December 9, 2013"));
$sql
=
"
SELECT COUNT(id) AS dealno
, DATE(purchaseCreated) AS purchaseDate
FROM deals
WHERE purchaseCreated
BETWEEN '$from' AND '$to'
GROUP BY purchaseDate
"
;

// SHOW THE QUERY WE ARE ABOUT TO RUN
print_r($sql);
echo PHP_EOL;

// RUN THE QUERY
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// var_dump($res);

// LOOK AT THE RESULTS SET
while ($row = $res->fetch_object())
{
    // CREATE THE FORMATTED DATA STRINGS LIKE [Date.UTC(2013, 12, 09),2],[Date.UTC(2013, 12, 10),2]
    $y = date('Y', strtotime($row->purchaseDate));
    $m = date('m', strtotime($row->purchaseDate));
    $d = date('d', strtotime($row->purchaseDate));

    $deals[] = "[Date.UTC($y, $m, $d), $row->dealno]";
}

// CREATE THE JSON-LIKE STRING
$jso = '"deals":' . implode(',', $deals);
print_r($jso);

Open in new window

0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39704369
You can also omit the WHERE clause if you don't care about a date range.  I added some more test data.  This outputs:

"deals":[Date.UTC(2013, 12, 08), 3],[Date.UTC(2013, 12, 09), 1]

Check the query near line 130.  This could probably be tightened up some, perhaps more of the date-related work could be done in the SQL engine.  But it seems to get the results set back in a way that will be useful for building the charts.

<?php // RAY_EE_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// TEST DATA SET
$cre
=
"
CREATE TEMPORARY TABLE `deals`
( `id`               INT(20)      NOT NULL AUTO_INCREMENT PRIMARY KEY
, `user_id`          INT(20)      NOT NULL
, `lead_id`          INT(20)      NOT NULL
, `parent_id`        INT(20)      NOT NULL
, `isTradeIn`        VARCHAR(40)  NOT NULL
, `purchaseType`     VARCHAR(40)  NOT NULL
, `purchaseYear`     INT(40)      NOT NULL
, `purchaseMake`     VARCHAR(255) NOT NULL
, `purchaseModel`    VARCHAR(255) NOT NULL
, `purchaseTrim`     VARCHAR(255) DEFAULT NULL
, `purchasePrice`    INT(40)      NOT NULL
, `purchaseStock`    VARCHAR(255) DEFAULT NULL
, `purchaseCreated`  DATETIME     NOT NULL
, `purchaseModified` DATETIME     NOT NULL
, `purchaseStatus`   VARCHAR(40)  NOT NULL DEFAULT 'Current'
, `archive`          INT(11)      NOT NULL DEFAULT '0'
, `locked`           INT(11)      NOT NULL DEFAULT '0'
)
"
;

$ins
=
"
INSERT INTO `deals`
  (`id`, `user_id`, `lead_id`, `parent_id`, `isTradeIn`, `purchaseType`, `purchaseYear`, `purchaseMake`, `purchaseModel`, `purchaseTrim`, `purchasePrice`, `purchaseStock`, `purchaseCreated`, `purchaseModified`, `purchaseStatus`, `archive`, `locked`)
VALUES
  (124, 79, 502, 78, 'noTrade', 'NEW', 2013, 'CADILLAC', 'ATS STANDARD 4DR SEDAN AWD', NULL, 23000, '', '2013-12-09 04:57:09', '2013-12-09 06:11:43', 'Deal', 1, 1)
, (125, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK',    'ENCLAVE 4DR SUV',            NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0)
, (126, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK',    'ENCLAVE 4DR SUV',            NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0)
, (127, 79, 496, 78, 'noTrade', 'NEW', 2012, 'BUICK',    'ENCLAVE 4DR SUV',            NULL, 23333, '', '2013-12-08 08:06:09', '2013-12-08 08:54:40', 'Deal', 1, 0)
"
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$res = $mysqli->query($cre);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $cre
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$res = $mysqli->query($ins);
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $ins
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// VERIFYING THE LOAD (IT WORKED)
$sql = "SELECT * FROM deals";
if (!$res = $mysqli->query($sql)) trigger_error($mysqli->error, E_USER_ERROR);
// while ($row = $res->fetch_object()) { print_r($row); }


// CREATE THE QUERY STRING FOR DEALS BY DATE USING ISO-8601 DATE FORMATS
// $from = date('Y-m-d 00:00:00', strtotime("December 8, 2013"));
// $to   = date('Y-m-d 23:59:59', strtotime("December 9, 2013"));
// OMITTING THE WHERE CLAUSE
// WHERE purchaseCreated BETWEEN '$from' AND '$to'


$sql
=
"
SELECT COUNT(id) AS dealno
, DATE(purchaseCreated) AS purchaseDate
FROM deals
GROUP BY purchaseDate
"
;

// SHOW THE QUERY WE ARE ABOUT TO RUN
print_r($sql);
echo PHP_EOL;

// RUN THE QUERY
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// var_dump($res);

// LOOK AT THE RESULTS SET
while ($row = $res->fetch_object())
{
    // CREATE THE FORMATTED DATA STRINGS LIKE [Date.UTC(2013, 12, 09),2],[Date.UTC(2013, 12, 10),2]
    $y = date('Y', strtotime($row->purchaseDate));
    $m = date('m', strtotime($row->purchaseDate));
    $d = date('d', strtotime($row->purchaseDate));

    $deals[] = "[Date.UTC($y, $m, $d), $row->dealno]";
}

// CREATE THE JSON-LIKE STRING
$jso = '"deals":' . implode(',', $deals);
print_r($jso);

Open in new window

HTH, ~Ray
0
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 39704378
Worth far more than the max 500 points to me. Really appreciate your help/direction as always. Have about 3 other queries to go and clean up based on the above.

Thank you!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39704522
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question