从 2 个表中选择信息将返回错误

Question

我做这样的事情

<?php
require_once('inc/config.php');
$con = mysqli_connect($host, $user, $pass, $db) or die ('Cannot Connect : '.mysqli_error());
$sql = "select username from education_info,profile_info where username ='j_spaxx22'";
$result = mysqli_query($con,$sql)  or die("Error: ".mysqli_error($con));
while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) )
{
echo $row['fullname'] ."". $row['email'] ."". $row['city'] ."". $row['state'] ."". $row['lga'] ."". $row['inst_name'] ."". $row['study_course'];
}
?>

我收到此错误

错误：字段列表中的"用户名"列不明确

我做错了什么？

编辑：

当我执行这样的查询时

$sql = "select * from education_info,profile_info";

我正确获取了所有表格

但是当我做这样的事情时

$sql = "select username from education_info,profile_info where username ='j_spaxx22'";

我收到错误，我似乎出错了什么？

Answer 1

使用此查询并获取输出而没有错误，只需使用表名;

select education_info.username,profile_info.username from education_info,profile_info where education_info.username ='j_spaxx22' and profile_info.username = 'j_spaxx22';

Answer 2

您的表中username有 2 个同名的列education_info,profile_info

如果您不需要JOIN表，只需从您想要的表格中选择

select username 
from education_info
where username ='j_spaxx22'

或

select username 
from profile_info
where username ='j_spaxx22'

如果需要JOIN表

SELECT profile.username 
FROM profile_info      profile
JOIN education_info    education ON profile.columntojoin=education.columntojoin
WHERE profile.username ='j_spaxx22'

请注意，在每个表之后，每个表都会给出一个ALIAS，您可以说出要从哪个表中显示数据。例如profile.username.当 2 个表联接具有相同的列时，还需要使用表名别名来标识它们，否则会出现Column in field list is ambiguous错误。

当你使用*而没有WHERE时，MySQL将返回完整的数据集，因为它可以按原样显示数据，当使用WHERE这样的过滤器时，MySQL必须确切地知道哪一列存在于多个表中。

Answer 3

您可以使用此查询

选择用户名 从education_info 其中用户名 = 'j_spaxx22' 联盟 选择用户名 从 profile_info 其中用户名 = 'j_spaxx22' ;

Answer 4

试试这个。

$sql="select 
education_info.username as un, education_info.col2 as c2, 
education_info.col3 as c3, 
profile_info.username as un2, profile_info.col2 as c4 
from education_info LEFT JOIN profile_info ON 
education_info.username=profile_info.username 
AND profile_info.username ='whatever';";

Answer 5

您应该首选具有完整表名称的字段，例如：

如果username字段来自education_info表，则 SQL 语句应为：

select education_info.username from education_info,profile_info where education_info.username ='j_spaxx22';