Solved

Trouble returning the right data for highcharts using php, mysql

Posted on 2013-12-08
10
1,760 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Hey, this is great!  I will try to set up a test.  Thanks! ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:tjyoung
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now