PHP 和 MYSQL JSON API 查询

Question

我一直在寻找答案并尝试了多种方法，但无论我怎么说，似乎仍然无法解决这个问题。我错过了一些东西..哈哈。

我有一个从 mysql 获取数据的 php 文件。

$query = "SELECT * FROM `UnitMaintbl` WHERE `Unit` LIKE '%{$value}%' ORDER BY Recnum DESC";

这很好用，但是我有一个字段是另一个表的索引，我需要另一个表的描述。所以我需要 INNER JOIN 这两个表。好的没问题。

$query = "SELECT * FROM `UnitMaintbl` 
INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
ORDER BY Recnum DESC";

首先，我想让 JOIN 基于任何测试单元工作。然后我想从表单中取回我的 $Value 。所以……

$query = "SELECT * FROM `UnitMaintbl` 
INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
WHERE Unitmaintbl.Unit = '%{$value}%'"
ORDER BY Recnum DESC";

我尝试了几种不同的帮助方案，但似乎无法正常工作。

非常感谢任何帮助....提前致谢..

编辑

<?php


$host = "localhost"; //Your database host server
$db = "PicorpBE"; //Your database name
$user = "username"; //Your database user
$pass = "password"; //Your password
$value=$_GET['Unit']; // Unit Number

$connection = mysql_connect($host, $user, $pass);

//Check to see if we can connect to the server
if(!$connection)
{
    die("Database server connection failed.");  
}
else
{
    //Attempt to select the database
    $dbconnect = mysql_select_db($db, $connection);

    //Check to see if we could select the database
    if(!$dbconnect)
    {
        die("Unable to connect to the specified database!");
    }
    else
    {

    $query = "SELECT * FROM `UnitMaintbl` 
    INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
WHERE Unitmaintbl.Unit = '%{$value}%'
ORDER BY Recnum DESC";

$resultset = mysql_query($query, $connection);

        $records = array();

        //Loop through all our records and add them to our array
        while($r = mysql_fetch_assoc($resultset))
        {
            $records[] = $r;        
        }

        //Output the data as JSON
        echo json_encode($records);
    }


}

?>

我重做了 php 但我没有得到:

[2015 年 12 月 23 日 14:26:52] PHP 警告:mysqli_query() [function.mysqli-query]:第 25 行/home/picorp05/public_html/json.php 中的空查询[2015 年 12 月 23 日 14:26:52] PHP 警告:mysqli_close() 期望参数 1 为 mysqli，第 45 行/home/picorp05/public_html/json.php 中给出的 bool 值

<?php
// Variables From Search

$sunit=$_GET['Unit']; // Unit Number

// Create connection
$con=mysqli_connect("localhost","username","password","dbname");

// Check connection
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// This SQL statement should select Unit ($sunit) from the table     'UnitMainTbl and
// Join related table. '

$query = "SELECT * FROM `UnitMaintbl` 
         INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
         WHERE Unitmaintbl.Unit = '%{$sunit}%'
         ORDER BY Recnum DESC";

// Check if there are results
if ($result = mysqli_query($con, $sql))
{
// If so, then create a results array and a temporary one
// to hold the data
$resultArray = array();
$tempArray = array();

// Loop through each row in the result set
while($row = $result->fetch_object())
{
    // Add each row into our results array
    $tempArray = $row;
    array_push($resultArray, $tempArray);
}

// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);
}

// Close connections
mysqli_close($result);
mysqli_close($con);
?>

好的最终代码，无论如何它都适用于我的前几次测试哈哈..哇哦..谢谢大家的帮助。

<?php

// Variables From Search
$sunit=$_GET['Unit']; // Unit Number

//open connection to mysql db
$connection = mysqli_connect("localhost","user","pass","PicorpBE") or     die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "SELECT * FROM `UnitMaintbl` 
    INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
    WHERE UnitMaintbl.Unit LIKE '%{$sunit}%'
    ORDER BY Recnum DESC";

$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray = array();
while($row =mysqli_fetch_assoc($result))
{
    $emparray[] = $row;
}
echo json_encode($emparray);

//close the db connection
mysqli_close($connection);
?>

Answer 1

你的查询应该是这样的:

$query = "SELECT * FROM `UnitMaintbl` 
        INNER JOIN `Statustbl` ON UnitMaintbl.StatusID=Statustbl.StatusDesc
        WHERE Unitmaintbl.Unit LIKE '%{$value}%'
        ORDER BY Recnum DESC";

代替 = 使用 LIKE 来搜索列中的指定模式。

旁注:请不要使用 mysql_ 数据库扩展，它们在 PHP 5.5.0 中已弃用，并在 PHP 7.0.0 中被删除。请改用 mysqli 或 PDO 扩展。这是 why you shouldn't use mysql_ functions .