我正在寻找对2个表的一个MYSQL查询,并将结果作为一个对象/数组。我的问题是查询需要匹配两个表中命名不同的一组ID。假设我有一个urls
表和一个products
表,它们都有一个与我要搜索的ID相对应的列,但名称不同...在urls表中称为resource_id,在products表中称为productid。
URLS TABLE: id | resource_id | url
PRODUCTS TABLE: productid | title | description | thumbnail
因此,在php中,我有一个ID列表,该ID仅在urls.resource_id
和products.productid
等于这些ID时才用于检索两个组合的表。
这是我到目前为止所拥有的:
$ids = ["18552", "18554", "18555", "18556"];
$newIds = implode(",",$ids);
$q3 = " SELECT products.title as title, urls.url as url
FROM urls, products
WHERE urls.resource_id IN ($newIds)
AND products.productid IN ($newIds)";
目前,这会根据$ids
变量4次返回4个结果-总共有16个结果。我认为这与WHERE urls.resource_id IN ($newIds) AND products.productid IN ($newIds
查询的一部分有关,因为4 * 4 = 16,但是我可能错了。
像这样:
{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },
{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },
{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },
{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },
不十分确定如何做到这一点而无需重复...有什么建议吗?
另外-该数据库来自购物车系统,因此我尝试尽可能简化示例,而不会在我的问题/示例中引起任何疑问。
先感谢您!
请尝试此查询。它显示了两个表和$ tdis中都存在的所有条目
$ids = ["18552", "18554", "18555", "18556"];
$newIds = implode(",",$ids);
$q3 = " SELECT p.title as title, u.url as url
FROM urls u INNER JOIN products p ON u. resource_id = p.productid
WHERE u.resource_id IN ($newIds);";
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句