Fetching MySQL Data into JavaScript Arrays for Charting
Building a chart requires bridging the gap between a relational database and a frontend visualization library. The core challenge lies in transforming raw SQL rowsâtypically returned as an object or array of objects by your backend APIâinto the specific multidimensional array format expected by libraries like jQuery Flot, Chart.js, or D3.js. Your target structure is [[x1, y1], [x2, y2]], where each sub-array represents a single data point on the graph.
The source material highlights a common stumbling block: attempting to serialize a raw MySQL result directly without mapping it first. When you execute echo json_encode($result); in PHP after running a standard query, the database driver returns a complex mysqli_result object. This object contains metadata like num_rows, field_count, and internal cursors, but it does not automatically convert to a simple JSON array of data points. If you send this directly to JavaScript, you end up parsing an object that looks like {current_field: null, field_count: null...}, which renders your chart empty or throws errors because the library expects [[1, 19], [2, 11]], not a result set object.
To fix this, you must manually iterate through the PHP result set to construct the desired array before encoding it. This ensures that only the actual data values are sent over the network, stripping away internal database driver metadata.
Correcting the PHP Backend
In your serv.php file, you need to fetch the rows and build a new variable that matches your JavaScript requirement. Using a while loop is the standard approach for iterating through mysqli_result. Inside the loop, you extract the specific columns and push them into an array.
Here is the corrected implementation:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "datadb";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the SQL query
$sql = "SELECT column1 as x, column2 as y FROM chartdata ORDER BY column1";
$result = $conn->query($sql);
$dataArray = [];
// Iterate through results and build the target array structure
if ($result && $result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
// Ensure we have valid numbers to avoid "null" entries
$x = isset($row['x']) ? (float)$row['x'] : null;
$y = isset($row['y']) ? (float)$row['y'] : null;
if ($x !== null && $y !== null) {
$dataArray[] = [$x, $y];
}
}
}
// Output ONLY the formatted array as JSON
echo json_encode($dataArray);
?>
Key changes here include aliasing columns (column1 as x) for clarity and using fetch_assoc() to get associative arrays from the database cursor. The critical fix is wrapping this logic in a loop to populate $dataArray. Finally, we use json_encode() on $dataArray, not the raw $result object. This ensures the response sent to JavaScript is strictly [[1, 19], [2, 11], ...].
Handling the JavaScript Side
Your HTML file uses jQuery to fetch this data. The logic remains largely the same, but you must handle the parsed JSON correctly once it arrives.
$.ajax({
url : 'serv.php',
type : 'GET',
success : function(result){
// Parse the incoming string into a JavaScript array
var chartData = jQuery.parseJSON(result);
console.log(chartData);
// Example output: Array(4) [[1,19], [2,11], [3,14], [4,16]]
// Now you can pass chartData directly to your charting library
// var plot = $.plot("#placeholder", [{ data: chartData }]);
},
error : function(err) {
console.error("Failed to fetch data:", err);
}
});
Why fetch_assoc() Matters
Using fetch_array() would return numeric indices (e.g., 0, 1), making it harder to map specific column names. fetch_assoc() allows you to access data by name, but in this case, since we are building a flat array of coordinates, the loop structure is what matters most. The PHP script essentially acts as an adapter, translating the database's row-based storage into the JavaScript library's array-based expectations.
If you were using LaravelCompany's framework or similar modern stacks, you might prefer sending JSON directly from the controller without raw PHP scripts, but the fundamental requirement remains identical: your backend must output a valid JSON array of coordinate pairs, never a database result object. Always validate that your json_encode target is an array and not a complex object containing metadata before sending it to the client.