Error in Generating graph (using jpgraph) when graph code is placed after PDO SQL code

Question

In my PHP file I get rows from a mysql database table and then loop through them, store the values to an array and output it to the chart library (jpgraph). I get an error when checking this in the browser.

Here is the code:

<?php
require __DIR__.'/../../config/config.php';
// require_once ROOT_DIR.'/inc/functions.php';
require_once ROOT_DIR.'/inc/chart/jpgraph.php';
require_once ROOT_DIR.'/inc/chart/jpgraph_line.php';

$ticker = 'AAPL';

// Database
include_once(ROOT_DIR.'/inc/db_connect.php');
$dbname = "trade_stocks";

$table_name = 'agg_'.strtolower($ticker).'_min_1';



try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

    echo $conn->getAttribute(PDO::ATTR_CONNECTION_STATUS).PHP_EOL;

    if(!$conn) {
        echo 'Not connected';
    } else {
        echo 'DB Connected!'.PHP_EOL; 
    }

    $query = $conn->prepare("
        SELECT *
        FROM $table_name
        WHERE 
            start_date = '2022-07-19'
            AND start_time > '10:30:00'
            AND end_time < '15:50:00'
    ");
    $query->execute();
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $stock_data = $query->fetchAll();

    // var_dump($stock_data);

    // Setup Chart
    $x_data = array();
    $y_data = array();
    foreach ($stock_data as $s_data) {
        $x_data[] = $s_data['start_time'];
        $y_data[] = floatval($s_data['close']);
    }

    $y_data = array_values($y_data);     

// complete and close connection
} // end try
    catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage().PHP_EOL;
}
$conn = null;

// Charting Code
// ------------------------------------------------------------------------

// Some data
$y_data = array(11,3,8,12,5,1,9,13,5,7);

// Create the graph. These two calls are always required
$graph = new Graph(3440,1440);
$graph->SetScale('textlin');

// Create the linear plot
$lineplot=new LinePlot($y_data);
$lineplot->SetColor('blue');

// Add the plot to the graph
$graph->Add($lineplot);

// Display the graph
$graph->Stroke();

When I move the charting code above the PDO mysql query, it works with a dummy data array.

// Chart a specific stock range
require __DIR__.'/../../config/config.php';
// require_once ROOT_DIR.'/inc/functions.php';
require_once ROOT_DIR.'/inc/chart/jpgraph.php';
require_once ROOT_DIR.'/inc/chart/jpgraph_line.php';

$ticker = 'AAPL';

// Database
include_once(ROOT_DIR.'/inc/db_connect.php');
$dbname = "trade_stocks";

$table_name = 'agg_'.strtolower($ticker).'_min_1';

// Charting Code
// ------------------------------------------------------------------------

// Some data
$y_data = array(11,3,8,12,5,1,9,13,5,7);

// Create the graph. These two calls are always required
$graph = new Graph(3440,1440);
$graph->SetScale('textlin');

// Create the linear plot
$lineplot=new LinePlot($y_data);
$lineplot->SetColor('blue');

// Add the plot to the graph
$graph->Add($lineplot);

// Display the graph
$graph->Stroke();


try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

    echo $conn->getAttribute(PDO::ATTR_CONNECTION_STATUS).PHP_EOL;

    if(!$conn) {
        echo 'Not connected';
    } else {
        echo 'DB Connected!'.PHP_EOL; 
    }

    $query = $conn->prepare("
        SELECT *
        FROM $table_name
        WHERE 
            start_date = '2022-07-19'
            AND start_time > '10:30:00'
            AND end_time < '15:50:00'
    ");
    $query->execute();
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $stock_data = $query->fetchAll();

    // var_dump($stock_data);

    // Setup Chart
    $x_data = array();
    $y_data = array();
    foreach ($stock_data as $s_data) {
        $x_data[] = $s_data['start_time'];
        $y_data[] = floatval($s_data['close']);
    }

    $y_data = array_values($y_data);     

// complete and close connection
} // end try
    catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage().PHP_EOL;
}
$conn = null;

Testing without the charting, the code works. The PDO mysql query is somehow interfering with outputting the chart.

It doesn't change anything. I had tried with `intval` originally, but I lose the decimal places in the data. However the chart does not work in either scenario. — Alexnl, Sep 28 '22 at 21:30
if your code doesn't work you have to check for errors and do a step-by-step debugging. Your charting code expects an array of specific format. Did you try to output the data you get from mysql to see how it differs from your dummy array? Shouldn't it be the very first thing to do when something doesn't work? — Your Common Sense, Sep 29 '22 at 04:42
The data from the mysql query was correctly formatted for the jpgraph and outputs fine without the chart. There is nothing wrong with the PDO statement. I tested all this before writing this question. I then tried taking the jpgaph example, which works independently, with a dummy array and it works above the PDO fine, but does not work below the PDO. The PDO works without the chart code. Each works fine independently, but together there is a conflict, but that conflict is not the resulting from the data array from the query. — Alexnl, Sep 30 '22 at 13:36

Error in Generating graph (using jpgraph) when graph code is placed after PDO SQL code

0 Answers0