在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)
?>

答

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

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

在json中加入多个表

相关推荐