我被困在我的表的内部连接方案中。你能帮帮我吗?我在这里做错了什么?
这是我正在尝试做的场景:
我有帐单和发货国家/州列,我正在尝试用内部联接填充这些列,但不知何故它不起作用。非常感谢您的帮助:
$sql = $this->connection->query("SELECT * FROM ".TBL_USERS."
INNER JOIN ".TBL_USER_LEVEL." ON ".TBL_USERS.".userlevel = ".TBL_USER_LEVEL.".user_level_id
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".country OR ".TBL_USERS.".bcountry = ".TBL_COUNTRIES.".country_id
INNER JOIN ".TBL_STATES." ON ".TBL_USERS.".states OR ".TBL_USERS.".bstates = ".TBL_STATES.".states_id
WHERE ".TBL_USERS.".username = '$username'");
我将查询更改为;我得到的错误是
PHP 致命错误:未捕获的异常"PDOException",消息为"SQLSTATE[42000]":语法错误或访问冲突:1066 不唯一的表/别名:"国家/地区"
$sql = $this->connection->query("SELECT * FROM ".TBL_USERS."
INNER JOIN ".TBL_USER_LEVEL." ON ".TBL_USERS.".userlevel = ".TBL_USER_LEVEL.".user_level_id
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".country = ".TBL_COUNTRIES.".country_id
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".bcountry = ".TBL_COUNTRIES.".country_id
INNER JOIN ".TBL_STATES." ON ".TBL_USERS.".states = ".TBL_STATES.".states_id
INNER JOIN ".TBL_STATES." ON ".TBL_USERS.".bstates = ".TBL_STATES.".states_id
WHERE ".TBL_USERS.".username = '$username'");
好的,这是正确的语法,非常感谢@Tiberiu-Ionuț Stan。
$sql = $this->connection->query("SELECT * FROM ".TBL_USERS."
LEFT JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".country = ".TBL_COUNTRIES.".country_id
LEFT JOIN ".TBL_COUNTRIES." AS ".TBL_COUNTRIES."_b ON ".TBL_USERS.".bcountry=".TBL_COUNTRIES."_b.country_id
INNER JOIN ".TBL_USER_LEVEL." ON ".TBL_USERS.".userlevel = ".TBL_USER_LEVEL.".user_level_id
LEFT JOIN ".TBL_STATES." ON ".TBL_USERS.".states = ".TBL_STATES.".states_id
LEFT JOIN ".TBL_STATES." AS ".TBL_STATES."_b ON ".TBL_USERS.".bstates=".TBL_STATES."_b.states_id
WHERE ".TBL_USERS.".username = '$username'");
您可以在连接语句中使用 OR 和 AND。我一直这样做。这是一个有效的:
LEFT JOIN `currencies` ON (
`currency_foreign`=`invoice_entry_amount_currency`
AND
`currency_reference`='".$strTransformToCurrency."'
AND
`currency_date`=FLOOR(`invoice_paid_timestamp`/86400)*86400
)
问题就在这里:
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".country = ".TBL_COUNTRIES.".country_id
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".bcountry = ".TBL_COUNTRIES.".country_id
MySQL 无法决定加入国家/地区表的关系(因为 MySQL 认为 .country 和 .countryb 可以不同 - 所以在连接后它不知道将返回哪些列或用于条件和排序)。
像这样尝试:
INNER JOIN ".TBL_COUNTRIES." ON ".TBL_USERS.".country = ".TBL_COUNTRIES.".country_id
INNER JOIN ".TBL_COUNTRIES." AS ".TBL_COUNTRIES."_b ON ".TBL_USERS.".bcountry=".TBL_COUNTRIES."_b.country_id
如果你有非常大的表,使用完整的最终查询进行解释,包括条件和排序,以确定MySQL是否因为"TBL_USERS AS TBL_USERS_B"而正在执行表复制。
你不能使用像 JOIN table ON field OR another_field = expression
这样的构造,除非引用field
是布尔类型。
您应该使用将返回布尔结果的构造,在您的情况下如下所示:
$sql = $this->connection->query("SELECT * FROM $TBL_USERS
JOIN $TBL_USER_LEVEL ON $TBL_USERS.userlevel = $TBL_USER_LEVEL.user_level_id
JOIN $TBL_COUNTRIES ON $TBL_USERS.country = $TBL_COUNTRIES.country_id
OR $TBL_USERS.bcountry = $TBL_COUNTRIES.country_id
JOIN $TBL_STATES ON $TBL_USERS.states = $TBL_STATES.states_id
OR $TBL_USERS.bstates = $TBL_STATES.states_id
WHERE $TBL_USERS.username = '$username'");
我也直接使用了变量,否则很多字符串连接看起来很混乱。您现在的查询对 SQLi 是开放的。