在d3可视化中访问MySQL数据库 [英] Accessing MySQL database in d3 visualization

问题描述

我需要一些帮助d3和MySQL。下面是我的问题：

I need some help with d3 and MySQL. Below is my question:

我有数据存储在MySQL（例如：关键字及其频率）。我现在想使用d3可视化。至于我的d3的知识，它需要json文件作为输入。我的问题是：如何从d3脚本访问此MySQL数据库？我可以想到的一种方法是：

I have data stored in MySQL (eg: keywords with their frequencies). I now want to visualize it using d3. As far as my knowledge of d3 goes, it requires json file as input. My question is: How do I access this MySQL database from d3 script? One way which i could think of is:

1. 使用Python，连接数据库并转换json格式的数据。将其保存在某些.json文件中。

1. Using Python, connect with database and convert the data in json format. Save this in some .json file.

在d3中，读取此json文件作为输入并在可视化中使用。

In d3, read this json file as input and use it in visualization.

有没有其他方法直接使用d3将MySQL中的数据转换为.json格式？我们可以从d3连接到MySQL并读取数据吗？

Is there any other way to convert the data in MySQL into .json format directly using d3? Can we connect to MySQL from d3 and read the data?

非常感谢！

推荐答案

以下是一个php脚本，您应该能够将其作为文件保存到某个位置（假设您将其称为getdata .php'）从您的HTML文件可以访问您的D3代码。调用时，它将以json格式从MySQL数据库返回数据（只要数据库服务器不在您的域外）;

The following is a php script that you should be able to save somewhere as a file (let's say you call it 'getdata.php') accessible from your HTML file with your D3 code in it. When called it will return data from your MySQL database in a json format (so long as the database server isn't outside your domain);

<?php
    $username = "******"; 
    $password = "******";   
    $host = "******";
    $database="***dbase_name***";

    $server = mysql_connect($host, $user, $password);
    $connection = mysql_select_db($database, $server);

    $myquery = "
    query here
    ";

    $query = mysql_query($myquery);

    if ( ! $myquery ) {
        echo mysql_error();
        die;
    }

    $data = array();

    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    }

    echo json_encode($data);     

    mysql_close($server);
?>

显然，您需要输入用户名，密码，主机和数据库的相应详细信息。
您还需要为数据包含一个合适的查询，以便返回dateTimeTaken和reading的数据。
沿着这行的东西（这只是一个猜测）;

Obviously you would need to enter appropriate details for username, password, host and database. You would also need to include an appropriate query for your data so that it returned data for 'dateTimeTaken' and 'reading'. Something along the lines of (and this is only a guess);

SELECT `dateTimeTaken`, `reading` FROM `tablename`

然后，当你在你的json文件中读取时，你将使用以下语法您将在您的json中阅读的代码;

Then when you go to read in your json file you would use the following syntax for the code where you would be reading in your json;

d3.json("getdata.php", function(error, data) {

希望这很接近你要找的东西
我测试过了它本地和所有似乎工作..

Hopefully that's close to what you're looking for. I've tested it locally and it all seems to work..

我已经放在一起的一个帖子，去本地安装一个简单的WAMP服务器，并设置一个查询MySQL来自d3.js的数据库 http：/ /www.d3noob.org/2013/02/using-mysql-database-as-source-of-data.html

I've put together a post to go over local installation of a simple WAMP server and setting up a query on the MySQL database from d3.js here http://www.d3noob.org/2013/02/using-mysql-database-as-source-of-data.html

这篇关于在d3可视化中访问MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！