Link to home
Start Free TrialLog in
Avatar of tjyoung
tjyoung

asked on

Trouble returning the right data for highcharts using php, mysql

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.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

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.
https://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.
Avatar of tjyoung
tjyoung

ASKER

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

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

Hey, this is great!  I will try to set up a test.  Thanks! ~Ray
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

Avatar of tjyoung

ASKER

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

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

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tjyoung

ASKER

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!
Thanks for the points and thanks for using EE, ~Ray