douzai2562 2015-04-07 03:00
浏览 63
已采纳

在json中加入多个表

I have problem when I want to display data by json file. If I display data in one table it is ok, but when I want to join more than tables no data displayed

<?php
mysql_connect($hostname,$username, $password) or die ("<html><script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script></html>");
mysql_select_db($dbname);

 $query = "SELECT Product.Product_Name, Product.Price, Product.Image, Gender.Description, Age.Description, Status.Availability  from Product join Age on Age.Age_ID join Gender on Gender.Gender_ID join Status on Status.ID";

$result = mysql_query($query);

//Create an array
    $json_response = array();

    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $row_array['Product_Name'] = $row['Product_Name'];
        $row_array['Price'] = $row['Price'];
        $row_array['Image'] = base64_encode($row["Image"]);
        $row_array['Description'] = $row['Description'];
        $row_array['Description'] = $row['Description'];
        $row_array['Availability'] = $row['Availability'];



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

    //Close the database connection
    fclose($db)
?>
  • 写回答

1条回答 默认 最新

  • douquejituan938904 2015-04-07 03:23
    关注

    Your last join on Status.ID is the issue. Status table doesn't have ID column. Based on you diagram, you have Status.Status_ID (you don't have Status.ID) Also, your data have to have related data where each table has values in common, otherwise, you will get empty results

    Your Diagram:

    enter image description here

    Change your Query

    SELECT 
        Product.Product_Name,
        Product.Price,
        Product.Image,
        Gender.Description,
        Age.Description,
        Status.Availability
    FROM
        Product
    JOIN
        Age ON Age.Age_ID
    JOIN
        Gender ON Gender.Gender_ID
    JOIN
        Status ON Status.ID
    

    to

    SELECT 
        Product.Product_Name,
        Product.Price,
        Product.Image,
        Gender.Description,
        Age.Description,
        `Status`.Availability
    FROM
        Product
    JOIN
        Age ON Product.Age_ID = Age.Age_ID
    JOIN
        Gender ON Product.Gender_ID = Gender.Gender_ID
    JOIN
        `Status` ON Product.Status_ID = `Status`.Status_ID
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题