php - 如何使用动态调用获取另一个表中的另一个值

Question

我目前有一个带有数组的查询，该数组使用我的表单 (term) 中的动态输入输出变量，这创建了一个自动完成的动态搜索以填写所有详细信息一个产品。

    $return_arr = array();
$param = $_GET["term"];
    $fetch = mysql_query("SELECT * FROM crd_jshopping_products WHERE `name_en-GB` REGEXP '^$param'");
    while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    //$row_array['category_id']  = $row ['category_id'];

$row_array['product_id'] = $row['product_id'];
    $row_array['product_names']  = $row['name_en-GB'];
    $row_array['jshop_code_prod'] = $row['product_ean'];
    $row_array['_ext_price_html']  = number_format($row['product_price'],2);
if (!empty($row['product_thumb_image']) AND isset($row['product_thumb_image'])){
$row_array['image'] = $row['product_thumb_image'];      
}else {
    $row_array['image'] = 'noimage.gif';    
}
    array_push( $return_arr, $row_array);
}
mysql_close($conn);
echo json_encode($return_arr);

不幸的是，我还需要获取不在同一个表中的category_id，我已经尝试修改我的query，但无济于事:

$fetch = mysql_query("SELECT * FROM crd_jshopping_products WHERE `name_en-GB` REGEXP '^$param' AND `crd_jshopping_products_to_categories`  = `product_id` ");

我在这里缺少哪一步？ product_id 的 在两个表 中匹配吗？

Answer 1

改为尝试这个查询并尝试理解我在其中写的内容:

$fetch = mysql_query("
    SELECT
        p.*,
        c.category_id
    FROM
        crd_jshopping_products as p
        INNER JOIN crd_jshopping_products_to_categories as c
            ON p.product_id = c.product_id
    WHERE
        `p.name_en-GB` REGEXP '^$param'
");

这意味着:

SELECT: Give me everything from p and the category_id from c.

FROM: Do this from rows in the tables crd_jshopping_products (referred to as p) and crd_jshopping_products_to_categories (referred to as c), where the rows match on the count of p.product_id is the same as c.product_id.

WHERE: Only return the rows where p.name_en-GB REGEXP '^$param'.