我一直在寻找答案,并尝试了多种方法,但无论我如何说,似乎仍然无法弄清楚。我想念一些东西..哈哈。
我有一个从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,而不是我得到:
[23-Dec-2015 14:26:52] PHP警告:mysqli_query()[function.mysqli-query]:/home/picorp05/public_html/json.php中第25行的空查询[2015年12月23日14: [26:52] PHP警告:mysqli_close()期望参数1为mysqli,在第45行的/home/picorp05/public_html/json.php中给出的布尔值
<?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);
?>
好的最终代码,无论如何我的前几个测试都可以使用哈哈.. woohoo ..谢谢大家的帮助。
<?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);
?>
您的查询应如下所示:
$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
扩展名代替。这就是为什么您不应该使用mysql_函数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句