php - MySQL 到 JSON 使用 PHP-6ren

php - MySQL 到 JSON 使用 PHP

转载作者：行者123 更新时间：2023-11-29 01:54:44

我有一个音乐共享脚本，我正在尝试从我的 mysql 数据库创建 JSON，我正在尝试创建一个特定的结构，但似乎无法弄清楚

这是我的代码

<?php
    //Create Database connection
    $db = mysql_connect("localhost","-Username-","-Password-");
    if (!$db) {
        die('Could not connect to db: ' . mysql_error());
    }

    //Select the Database
    mysql_select_db("-Database-",$db);


{

    $result = mysql_query("SELECT * FROM users ORDER BY username ASC"); 
    $json_response = array(); //Create an array
    while ($row = mysql_fetch_array($result))
    {
        $row_array = array();
        $row_array['idu'] = $row['idu'];
        $row_array['username'] = $row['username'];
        $row_array['first_name'] = $row['first_name'];
        $row_array['last_name'] = $row['last_name'];
        $row_array['country'] = $row['country'];
        $row_array['image'] = $row['image'];
        $row_array['cover'] = $row['cover'];
        $row_array['description'] = $row['description'];
        $row_array['playlists'] = array();  
        $row_array['tracks'] = array();  
        $plid = $row['idu'];

        $option_qry = mysql_query("SELECT * FROM playlists where playlists.by=$plid");
        while ($opt_fet = mysql_fetch_array($option_qry))
        {
            $row_array['playlists'][] = array(
                'id' => $opt_fet['id'],
                'name' => $opt_fet['name'],
                'by' => $opt_fet['by'],
            );


        }
        array_push($json_response, $row_array); //push the values in the array


        //tracks


        $option_qry = mysql_query("SELECT * FROM tracks where tracks.uid=$plid");
        while ($opt_fet = mysql_fetch_array($option_qry))
        {
            $row_array['tracks'][] = array(
                'id' => $opt_fet['id'],
                'title' => $opt_fet['title'],
                'name' => $opt_fet['name'],
                'art' => $opt_fet['art'],
                'likes' => $opt_fet['likes'],
                'downloads' => $opt_fet['downloads'],
                'plays' => $opt_fet['views'],
                'uid' => $opt_fet['uid'],



            );


        }
        array_push($json_response, $row_array); //push the values in the array

    }
    echo json_encode($json_response,JSON_PRETTY_PRINT);
}
?>

用户表:

id  username   last_name  first_name  country etc...
1   username1  Mynce      George      USA     etc...
2   username2  Jenkins    Fred        USA     etc...
3   username3  Walberg    Mark        USA     etc...
4   username4  Smith      Will        USA     etc...

播放列表表:

id   by  name
1    4   My playlists
2    3   Popular Songs

播放列表表:

id  playlists   track
12  1           13
13  1           23
14  1           3
15  1           17
16  1           9
12  2           14
13  2           24
14  2           4
15  2           18
16  2           10

轨道表:

id  title      name      art      likes  downloads  views
1   song_title song1.mp3 art1.png 13     8          55
2   song_title song2.mp3 art2.png 12     10         31
3   song_title song3.mp3 art3.png 7      32         50
4   song_title song4.mp3 art4.png 22     11         1
5   song_title song5.mp3 art5.png 1      28         2

使用我当前的代码，我得到了这个 JSON 结果:

{
    "idu": "2",
    "username": "chicagochurch",
    "first_name": "Chicago Christian Center",
    "last_name": "",
    "country": "United States",
    "image": "839674815_146358938_1746691162.png",
    "cover": "173157219_1187606488_302826016.jpg",
    "description": "This is the official Chicago Church Music page.",
    "playlists": [
        {
            "id": "4",
            "name": "We Give You Praise",
            "by": "2"
        }
    ],
    "tracks": [
        {
            "id": "29",
            "title": "01 We Give You Praise",
            "name": "1667450919_35711384_1898671201.mp3",
            "art": "97020012_1272964020_1490042512.png",
            "likes": "1",
            "downloads": "1",
            "plays": "4",
            "uid": "2"
        },
        {
            "id": "30",
            "title": "02 Luvudees",
            "name": "1361859314_884859216_209326964.mp3",
            "art": "1591964284_1156840179_1721104535.png",
            "likes": "0",
            "downloads": "1",
            "plays": "3",
            "uid": "2"
        },
        {
            "id": "31",
            "title": "03 Mugo Del",
            "name": "1366849477_130736941_1367659635.mp3",
            "art": "1181156184_556035815_1698596436.png",
            "likes": "0",
            "downloads": "1",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "32",
            "title": "04 San Amensa",
            "name": "40925819_971317614_1732715256.mp3",
            "art": "837149755_251638008_1945445596.png",
            "likes": "0",
            "downloads": "0",
            "plays": "1",
            "uid": "2"
        },
        {
            "id": "33",
            "title": "05 One True God",
            "name": "1201163785_1107025307_1077346045.mp3",
            "art": "713651840_1636034922_1247445482.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "34",
            "title": "06 Mugo Anav",
            "name": "173569477_686122962_117960391.mp3",
            "art": "1432749408_578109445_1094716795.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "35",
            "title": "07 Hallelujah",
            "name": "133051077_70993845_552471086.mp3",
            "art": "2040610215_1811023913_383444282.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "36",
            "title": "08 Sheppard",
            "name": "984429058_1532916377_1375134853.mp3",
            "art": "1153269141_143559426_997684622.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "37",
            "title": "09 I Love you Lord",
            "name": "794115968_1411878888_673035094.mp3",
            "art": "1692460167_1433248811_682631716.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "38",
            "title": "10 Jesus In The Center",
            "name": "202390322_461558278_1271927584.mp3",
            "art": "1048499380_362527600_4599069.png",
            "likes": "0",
            "downloads": "1",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "39",
            "title": "11 Mugo Luvudeemos",
            "name": "274506373_302381815_356651583.mp3",
            "art": "1229728795_739090349_686501748.png",
            "likes": "0",
            "downloads": "1",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "40",
            "title": "12 Onday Chedo Kud",
            "name": "378498031_1470949688_1199351944.mp3",
            "art": "308978665_1074723934_419931699.png",
            "likes": "0",
            "downloads": "0",
            "plays": "0",
            "uid": "2"
        },
        {
            "id": "41",
            "title": "13 The Word",
            "name": "956461611_287666310_43661088.mp3",
            "art": "1584112229_240532531_977129308.png",
            "likes": "1",
            "downloads": "4",
            "plays": "0",
            "uid": "2"
        }
    ]
}

我实际上想要得到这种结构:

{
    "idu": "2",
    "username": "chicagochurch",
    "first_name": "Chicago Christian Center",
    "last_name": "",
    "country": "United States",
    "image": "839674815_146358938_1746691162.png",
    "cover": "173157219_1187606488_302826016.jpg",
    "description": "This is the official Chicago Church Music page.",
    "playlists": [
        {
            "id": "4",
            "name": "We Give You Praise",
            "by": "2",
            "tracks": [
                {
                    "id": "29",
                    "title": "01 We Give You Praise",
                    "name": "1667450919_35711384_1898671201.mp3",
                    "art": "97020012_1272964020_1490042512.png",
                    "likes": "1",
                    "downloads": "1",
                    "plays": "4",
                    "uid": "2"
                },
                {
                    "id": "30",
                    "title": "02 Luvudees",
                    "name": "1361859314_884859216_209326964.mp3",
                    "art": "1591964284_1156840179_1721104535.png",
                    "likes": "0",
                    "downloads": "1",
                    "plays": "3",
                    "uid": "2"
                },
                {
                    "id": "31",
                    "title": "03 Mugo Del",
                    "name": "1366849477_130736941_1367659635.mp3",
                    "art": "1181156184_556035815_1698596436.png",
                    "likes": "0",
                    "downloads": "1",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "32",
                    "title": "04 San Amensa",
                    "name": "40925819_971317614_1732715256.mp3",
                    "art": "837149755_251638008_1945445596.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "1",
                    "uid": "2"
                },
                {
                    "id": "33",
                    "title": "05 One True God",
                    "name": "1201163785_1107025307_1077346045.mp3",
                    "art": "713651840_1636034922_1247445482.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "34",
                    "title": "06 Mugo Anav",
                    "name": "173569477_686122962_117960391.mp3",
                    "art": "1432749408_578109445_1094716795.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "35",
                    "title": "07 Hallelujah",
                    "name": "133051077_70993845_552471086.mp3",
                    "art": "2040610215_1811023913_383444282.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "36",
                    "title": "08 Sheppard",
                    "name": "984429058_1532916377_1375134853.mp3",
                    "art": "1153269141_143559426_997684622.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "37",
                    "title": "09 I Love you Lord",
                    "name": "794115968_1411878888_673035094.mp3",
                    "art": "1692460167_1433248811_682631716.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "38",
                    "title": "10 Jesus In The Center",
                    "name": "202390322_461558278_1271927584.mp3",
                    "art": "1048499380_362527600_4599069.png",
                    "likes": "0",
                    "downloads": "1",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "39",
                    "title": "11 Mugo Luvudeemos",
                    "name": "274506373_302381815_356651583.mp3",
                    "art": "1229728795_739090349_686501748.png",
                    "likes": "0",
                    "downloads": "1",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "40",
                    "title": "12 Onday Chedo Kud",
                    "name": "378498031_1470949688_1199351944.mp3",
                    "art": "308978665_1074723934_419931699.png",
                    "likes": "0",
                    "downloads": "0",
                    "plays": "0",
                    "uid": "2"
                },
                {
                    "id": "41",
                    "title": "13 The Word",
                    "name": "956461611_287666310_43661088.mp3",
                    "art": "1584112229_240532531_977129308.png",
                    "likes": "1",
                    "downloads": "4",
                    "plays": "0",
                    "uid": "2"
                }
            ]
        }
    ]
}

正如您在上面的结构中看到的那样，播放列表在其自己的嵌套数组中列出了与其关联的轨道，我确定播放列表条目表将需要用于此但我不知道如何实现它，抱歉所有代码...但感谢您的帮助!

最佳答案

要获取播放列表的轨道，您应该在名为 in playlist 的轨道表中添加一个新列，这样您就可以运行 mysql 查询并获取轨道 WHERE inplaylist = x

这是我的解决方案(在本地主机上测试过):

<?php
//Create Database connection
$db = mysql_connect("localhost","","");
if (!$db) {
    die('Could not connect to db: ' . mysql_error());
}

//Select the Database
mysql_select_db("test",$db);




$result = mysql_query("SELECT * FROM users ORDER BY username ASC"); 
$json_response = array(); //Create an array
while ($row = mysql_fetch_array($result))
{
    $row_array = array();
    $row_array['idu'] = $row['idu'];
    $row_array['username'] = $row['username'];
    $row_array['first_name'] = $row['first_name'];
    $row_array['last_name'] = $row['last_name'];
    $row_array['country'] = $row['country'];
    $row_array['image'] = $row['image'];
    $row_array['cover'] = $row['cover'];
    $row_array['description'] = $row['description'];
    $row_array['playlists'] = array();  
    $plid = $row['idu'];

    $option_qry = mysql_query("SELECT * FROM playlists where playlists.by=$plid");
    $playlist = 1;
    while ($opt_fet = mysql_fetch_array($option_qry))
    {

        $row_array['playlists'][$playlist]['id'] = $opt_fet['id'];
        $row_array['playlists'][$playlist]['name'] = $opt_fet['name'];
        $row_array['playlists'][$playlist]['by'] = $opt_fet['by'];

        $tracks_qry = mysql_query("SELECT * FROM tracks where tracks.uid=$plid AND tracks.inplaylist = {$opt_fet['id']}");
        while ($tcks_fet = mysql_fetch_array($tracks_qry))
        {
            $row_array['playlists'][$playlist]['tracks'][] = array(
                'id' => $tcks_fet['id'],
                'title' => $tcks_fet['title'],
                'name' => $tcks_fet['name'],
                'art' => $tcks_fet['art'],
                'likes' => $tcks_fet['likes'],
                'downloads' => $tcks_fet['downloads'],
                'plays' => $tcks_fet['views'],
                'uid' => $tcks_fet['uid'],
            );
        }
        $playlist++;
    }

    //tracks




    array_push($json_response, $row_array);

}
print "<pre>";
echo json_encode($json_response,JSON_PRETTY_PRINT);

?>

我的输出:

[
    {
        "idu": "2",
        "username": "chicagochurch",
        "first_name": "Chicago Christian Center",
        "last_name": "",
        "country": "United States",
        "image": "8396748146691162.png",
        "cover": "1731572302826016.jpg",
        "description": "Music page.",
        "playlists": {
            "1": {
                "id": "4",
                "name": "We Give You Praise",
                "by": "2",
                "tracks": [
                    {
                        "id": "29",
                        "title": "01 We Give You Praise",
                        "name": "1667450919_35711384_1898671201.mp3",
                        "art": "97020012_1272964020_1490042512.png",
                        "likes": "1",
                        "downloads": "1",
                        "plays": "4",
                        "uid": "2"
                    },
                    {
                        "id": "30",
                        "title": "02 Luvudees",
                        "name": "1361859314_884859216_209326964.mp3",
                        "art": "1591964284_1156840179_1721104535.png",
                        "likes": "0",
                        "downloads": "1",
                        "plays": "3",
                        "uid": "2"
                    }
                ]
            },
            "2": {
                "id": "5",
                "name": "We Give You Praise 2",
                "by": "2"
            }
        }
    }
]

关于php - MySQL 到 JSON 使用 PHP，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32290138/

文章推荐： mysql - 导入以前导出的 mysql 触发器时出错

文章推荐： android - 将 xml (android 源代码) 转换为 pdf

文章推荐：安卓:白日梦中的 fragment

文章推荐： php - 在数据库中搜索整个字符串/短语

javascript - 使用 WebScriptEndpoint 使用 javascript 使用 WCF 服务
我在网上搜索但没有找到任何合适的文章解释如何使用 javascript 使用 WCF 服务，尤其是 WebScriptEndpoint。任何人都可以对此给出任何指导吗？谢谢最佳答案这是一篇关于
c - 没有结果!!使用 fork() 使用 dup2 使用 2 个管道运行 execlp()
我正在编写一个将运行 Linux 命令的 C 程序，例如: cat/etc/passwd | grep 列表 |剪切-c 1-5 我没有任何结果 *这里 parent 等待第一个 child (chi
python - 处理文件上传，使用 Pillow 调整大小，使用 SQLAlchemy 存储，使用 Flask 提供文件
所以我正在尝试处理文件上传，然后将该文件作为二进制文件存储到数据库中。在我存储它之后，我尝试在给定的 URL 上提供文件。我似乎找不到适合这里的方法。我需要使用数据库，因为我使用 Google 应用引
excel - 使用 IF 使用 VBA 在单元格中添加公式的问题
我正在尝试制作一个宏，将下面的公式添加到单元格中，然后将其拖到整个列中并在 H 列中复制相同的公式我想在 F 和 H 列中输入公式的数据 Range("F1").formula = "=IF(ISE
使用 OperatorPrecedenceParser 使用 FParsec 解析函数应用程序？
问题类似于this one ，但我想使用 OperatorPrecedenceParser 解析带有函数应用程序的表达式在 FParsec . 这是我的 AST: type Expression =
sql - 使用 sequelize 使用 where 查询编码计数
我想通过使用 sequelize 和 node.js 将这个查询更改为代码取决于在哪里 select COUNT(gender) as genderCount from customers where
bash - 使用 “let”分配Bash失败，使用 “/”
我正在使用GNU bash，版本5.0.3(1)-发行版(x86_64-pc-linux-gnu)，我想知道为什么简单的赋值语句会出现语法错误: #/bin/bash var1=/tmp
javascript - 使用 JavaScript 使用 FOR OF 数组循环时出现错误？
这里，为什么我的代码在 IE 中不起作用。我的代码适用于所有浏览器。没有问题。但是当我在 IE 上运行我的项目时，它发现错误。而且我的 jquery 类和 insertadjacentHTMl 也不
javascript - 使用 javascript 使用 for 属性更改表单标签内容
我正在尝试更改标签的innerHTML。我无权访问该表单，因此无法编辑 HTML。标签具有的唯一标识符是“for”属性。这是输入和标签的结构:
javascript - 使用 jquery 使用 .on() 将事件附加到页面上的动态插入按钮
我有一个页面，我可以在其中返回用户帖子，可以使用一些 jquery 代码对这些帖子进行即时评论，在发布新评论后，我在帖子下插入新评论以及删除按钮。问题是 Delete 按钮在新插入的元素上不起作用，
使用 awk 使用 sha1sum 进行散列
我有一个大约有 20 列的“管道分隔”文件。我只想使用 sha1sum 散列第一列，它是一个数字，如帐号，并按原样返回其余列。使用 awk 或 sed 执行此操作的最佳方法是什么？ Accounti
mysql - 使用 insert into 使用 mysql
我需要将以下内容插入到我的表中...我的用户表有五列 id、用户名、密码、名称、条目。 (我还没有提交任何东西到条目中，我稍后会使用 php 来做)但由于某种原因我不断收到这个错误:#1054 - U
jquery - 将输入字段值修剪为仅字母数字字符/使用 .使用 jQuery
所以我试图有一个输入字段，我可以在其中输入任何字符，但然后将输入的值小写，删除任何非字母数字字符，留下“。”而不是空格。例如，如果我输入: 地球的 70% 是水，-!*#$^^ & 30% 土地输
javascript - 使用 .innerHTML 使用 DOM
我正在尝试做一些我认为非常简单的事情，但出于某种原因我没有得到想要的结果？我是 javascript 的新手，但对 java 有经验，所以我相信我没有使用某种正确的规则。这是一个获取输入值、检查选择
php - 使用 angularjs 使用 where 子句从数据库获取数据
我想使用 angularjs 从 mysql 数据库加载数据。这就是应用程序的工作原理；用户登录，他们的用户名存储在 cookie 中。该用户名显示在主页上我想获取这个值并通过 angularjs
ios - 使用 UITableViewCell 使用 AutoLayout
我正在使用 autoLayout，我想在 UITableViewCell 上放置一个 UIlabel，它应该始终位于单元格的右侧和右侧的中心。这就是我想要实现的目标所以在这里你可以看到我正在谈论的
mysql - 使用 ElasticSearch 使用 or 和运算符搜索多个字段
我需要与 MySql 等效的 elasticsearch 查询。我的 sql 查询: SELECT DISTINCT t.product_id AS id FROM tbl_sup_price t
ios - 使用 Swift 使用 JSON
我正在实现代码以使用 JSON。 func setup() { if let flickrURL = NSURL(string: "https://api.flickr.com/
javascript - 使用 JavaScript 使用 for 循环声明变量
我尝试使用for循环声明变量，然后测试cols和rols是否相同。如果是，它将运行递归函数。但是，我在 javascript 中执行 do 时遇到问题。有人可以帮忙吗？现在，在比较 col.1 和
jquery - 使用 :after 使用 jquery 更改样式
我举了一个我正在处理的问题的简短示例。 HTML代码: 1 2 3 CSS 代码: .BB a:hover{ color: #000; } .BB > li:after {

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

php - MySQL 到 JSON 使用 PHP