我想在我的查询中连接两个表来显示结果。
我在第一个表中有lastname,firstname等。
在第二个表中,我有training,date...
在第二个表中,我还有一个ID引用,它将姓氏和名字链接到培训和日期。
因此,每当我在数据库中创建一条训练记录时,它对同一个人使用相同的ID。
我希望能够在查询中连接这两个表,并在搜索lastname时显示结果。
它还会列出他们已经完成的所有培训,如果我在出现的搜索中单击那里的名称。
我是MySQL数据库的新手。
出于测试目的,我正在使用MySQL的phpmyadmin并运行本地主机数据库...
目前,我可以搜索单个表并显示结果,但我无法找到链接它们的联盟,并且无法将姓名列表作为链接来显示那里的培训。把它的名字放在打印输出的最上面...这是我从教程中得到的代码。
<form>
<div align="right">
<input type="button" value="Print Results" onClick="myprint()">
</div>
</form>
<?php
// TAKE THE INFORMATION FROM FORM.
$search = $_GET['search'];
// IF THERE IS NOT A KEYWORD GIVE AN ERROR
if (!$search)
echo "You didn't enter a keyword";
else
{
echo "<td>You searched for: <strong>$search </strong></td>";
mysql_connect('localhost','loginid','password');
mysql_select_db('trainingrecords');
$id=@$_GET['id'];
//QUERY IS THE CODE WHICH WILL MAKE THE SEARCH IN YOUR DATABASE.
//I WROTE AN EXPLANATION ABOUT IT AFTER THE CODE.
$query="SELECT * FROM username
WHERE MATCH( lastname, firstname, location, created)
AGAINST('%$search%' IN BOOLEAN MODE)";
$result1 = MySQL_query($query);
if(!$result1) {
echo MySQL_error()."<br>$query<br>";
}
if (MySQL_num_rows($result1) > 0) {
echo "<table width='750' align='center' border='1'
cellspacing='2' cellpadding='2'>";
while($result2 = MySQL_fetch_array($result1)) {
//A short description from category.
$description = $result2['location'];
$searchPosition = strpos($description, $search);
$shortDescription = substr($description, $searchPosition, 150);
echo "<td><strong>{$result2['lastname']}
{$result2['firstname']}</td></strong><td>
$shortDescription</td><td>{$result2['created']}</td><tr/>";
}
echo "</table>";
} else {
echo "No Results were found in this category.<br>";
}
echo "<br>";
}
?>发布于 2011-08-25 21:06:20
SQL 1-您有一个注入漏洞
确保使用mysql_real_escape_string对所有变量进行转义
并在查询_中使用正确的引号字符串(否则mysql_real_escape_string将无法在)_中工作
$search = mysql_real_escape_string($GET['search']);
....
$query="SELECT * FROM username
WHERE MATCH( lastname, firstname, location, created)
AGAINST('%".$search."%' IN BOOLEAN MODE)";
// ^ ^ quotes were already there, good!join 2-如果您想从多个表中获取结果,则必须执行
$search = "SELECT u.*, t.* FROM username u
INNER JOIN training t ON (u.id = t.user_id)
WHERE MATCH( lastname, firstname, location, created)
AGAINST('%".$search."%' IN BOOLEAN MODE)";通常情况下,您不希望选择所有字段,如果您显式声明要向用户显示的字段,则查询会运行得更快。
这里有一个关于php和MySQL的不错的教程:http://www.tizag.com/mysqlTutorial/
下面是如果有SQL注入漏洞会发生的情况:How does the SQL injection from the "Bobby Tables" XKCD comic work?
发布于 2011-08-25 20:52:01
我认为你想要这样的东西:
SELECT * FROM username, training
where username.lastname = $lastname and username.firstname = $firstname
and username.id = training.id;这将需要您将$lastname和$firstname值作为单独的变量进行传递。
发布于 2011-08-25 20:52:32
它不是联合,而是你想要的连接
Select a.id,a.firstname,a.lastname,a.location,a.date,b.trainingdate
from username a
inner join
training b on a.id=b.id在结尾处添加where子句
https://stackoverflow.com/questions/7190619
复制相似问题