Converting mySQL data to JSON
There are many reasons you might want to take data from a db and put it into Javascript arrays, from direct rendering of data/records into divs to creating data visualizations through libraries such as p5. Certainly it is a common practice that modern web apps load their data separately from the individual page (code), oftentimes from diffeent servers. For development within our context loading the data into json objects changes the front-end development to be more javascript-driven -- versus flowing the data into html objects through php outputs/echos.

Below are two mechanisms you can use.

The optimal implemenation is Option 2, and setting it up as a stand-alone php output (self-containted file) with your actual page calling the script through an ajax call, to then use the json data, so that individual pages do not actually contain the core query and php-to-json output code. 

Option 1: Export database from mysql, convert into JSON flat file/code (less optimal)

There are converters such as the site https://www.csvjson.com/sql2json that allow you to load up an export of a mysql database (what you would use to recreate or import it), and then it will take the database information and encode it into a json block/file.

So, for example, I fed the file dvd.sql.txt into it, and took the output json it created and save it as dvd.json.txt.

The disadvantage of course is that it is a snapshot in time of the data, and is not updated/synced with the database after export and conversion, and is not inherently searchable and filterable through a SELECT statement

 

Option 2: Convert live data output (such as from a view to JSON)

This allows for dynamic on-the-fly conversion of data to json structures, with (optionally) the ability to support inputs (search strings, filters, custom sorting). It also lets you simply take the output of any view and convert it to json using a php command json_encode(). The advantage of this is that the data is live and the page changes as the data does. The disadvantage is that it requires more live server and database processing (memory, CPU), so is a heavy server load that other methods.

For a simple example of this, we can take the php looped output of a query, which would normally be rendering into html objects, and instead inside each row of the loop populate an array. Then afterward we can convert that array into json.

So first, let's imagine we already have a query and an output loop (you can also load the start code sql_to_json_start.txt):

$host = "webdev.iyaserver.com";
$userid = "[youruserid]";
$userpw = "[yourpw]";
$db = "[database name>";

include '../pdloginvariables.php';

$mysql = new mysqli(
    $host,
    $userid,
    $userpw,
    $db
);

$sql = 		"SELECT * FROM movieView";

$results = $mysql->query($sql);


while($currentrow = $results->fetch_assoc()) {
 
 }

Now before the loop we will create a new array object (variable), and inside the loop we will dump each row of data into that array:

$json_array = array();

while($currentrow = $results->fetch_assoc()) {
    $json_array[] = $currentrow;
    // above adds one new entry/object to existing array
}

echo "<pre>";
print_r($json_array);
echo "</pre>";
The last line dumps the full array that has been built to the screen, so show it is properly working. To see that output, go to sql_to_arrays.php

Now lastly, we need to convert/encode the array data into JSON using thephp command json_encoded(). Because I know there are also some special characters in the data that may cause issues, I am also editing the original $mysqli connection to a charset of utf-8... and commenting out the echo/dump of the array:

$mysql = new mysqli(
    $host,
    $userid,
    $userpw,
    $db
);

$mysql->set_charset("utf8");

$sql = 		"SELECT * FROM movieView";

$results = $mysql->query($sql);

$json_array = array();

while($currentrow = $results->fetch_assoc()) {
    $json_array[] = $currentrow;
}

 $json2 = json_encode($json_array);

echo $json2;
When trying to do this, if you get valid arrays before you json encoding, but then after json_encode() you just back a blank, there may have been a problem. You can check for errors through json_last_error_msg() such as:

$json2 = json_encode($json_array);

echo "<hr>json error: " . json_last_error_msg() . "<hr>";
To see this working page in action, go to sql_to_json_example.php (NOTE: this example both dumps the json object to the screen AND to the console.log, to see the data, bt the actual working script does not output that way) and to see the underlying code you can look at sql_to_json_example.txt.
Finally, if you want to set this up as a stand-alone json data feed, we just need to do two things:

Make sure there are NO output in the script except the json output (no debug, other notes, etc,)
Add to the top of the file, at the VERY top of the file:
	<?php
	header("Access-Control-Allow-Origin: *");
	
(php header commands must be the very first thing that is sent from the server)
 

Load json through jQuery
One more efficient and common development workflow is to separate the database data workflow from the front-end flowing in of that content. So how about if we treat the above json output script as a stand-alone data call.

So let's treat the above final version of the json php script as a stand-alone data call/output: http://demos.webdev.iyaserver.com/json/sql_to_json_example.php

If we simply call that data within the page from a jQuery ajax call, we will end up with the sae result of the movie data loaded into a json object. But we'll pick up that thread from the json-driven page...