I wish to visualize data alike the sample @ https://gist.github.com/mbostock/1044242.
However, I would like to do so from the values drawn from an SQL DB. The DB currently holds sample data related to the Olympics, derived from http://msftdbprodsamples.codeplex.com/releases/view/97636. There are 4 tables in it. To illustrate an example, rows @ the table 'medalist' is related to tables 'sport' and 'discipline'.
I know I have to write a PHP script to format the data in JSON, to be read in by d3.js via Eclipse, which produces nothing close to 'readme-flare-imports.json' at the above link. Please see my attempt at the following (I know it is far from acceptable)
Attempt:
<?php
$db_host = "localhost";
$db_user = "root";
$password = "password";
$database = "olympics";
$conn = @mysqli_connect ($db_host, $db_user, $password, $database);
if (! $conn) {
trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
}
$data = array();
$myquery = "SELECT * from medalist";
$result = mysqli_query ($conn, $myquery);
while ($row = mysqli_fetch_array ($result)) { //fetch row instead of entire objs
$medalistID = $row ['medalistID'];
$edition = $row ['edition'];
$athlete = $row ['athlete'];
$noc = $row ['noc'];
$gender = $row ['gender'];
$event = $row ['event'];
$medal = $row ['medal'];
$season = $row ['season'];
$medalValue = $row ['medalValue'];
$sportID = $row ['sportID'];
$disciplineID = $row ['disciplineID'];
echo '{"medalistID":'.json_encode($medalistID).'}';
echo '{"edition":'.json_encode($edition).'}';
echo '{"athlete":'.json_encode($athlete).'}';
echo '{"noc":'.json_encode($noc).'}';
echo '{"gender":'.json_encode($gender).'}';
echo '{"event":'.json_encode($event).'}';
echo '{"medal":'.json_encode($medal).'}';
echo '{"season":'.json_encode($season).'}';
echo '{"medalValue":'.json_encode($medalValue).'}';
echo '{"sportID":'.json_encode($sportID).'}';
echo '{"disciplineID":'.json_encode($disciplineID).'},<br>';
$data[] = array ('medalistID' => $medalistID,
'edition' => $edition,
'athlete' => $athlete,
'noc' => $noc,
'gender' => $gender,
'event' => $event,
'medal' => $medal,
'season' => $season,
'medalValue' => $medalValue,
'sportID' => $sportID,
'disciplineID' => $disciplineID);
}
$fp = fopen ('sample_Olympics.json', 'w');
fwrite ($fp, json_encode ($data));
fclose ($fp);
mysqli_close ($conn);
?>
My question is: - with reference to the link and my attempt above, how may I alter the code(s) to display 'sample_Olympics.json' correctly please? I am not sure if there is any hierarchy involved in the DB but hierarchical edge bundling is the closest I can find next to radial convergence diagram.
