mysql - Retrieve value from two tables in a single query line

advertisements

My question may seem very easy to most but it has its own twists. I'll get to the point right away.

I have a PHP code which consists of a MySQL query whose function is to select a column, product_name from a table called products and then doing some further stuff.

Here's the code:

if(isset($_POST["type"]) && $_POST["type"]=='add')
{
    foreach($_POST as $key => $value){
        $new_product[$key] = filter_var($value, FILTER_SANITIZE_STRING);
    }

    unset($new_product['type']);
    unset($new_product['return_url']); 

    $statement = $mysqli->prepare("SELECT product_name FROM products WHERE product_code=? LIMIT 1");
    $statement->bind_param('s', $new_product['product_code']);
    $statement->execute();
    $statement->bind_result($product_name);

    while($statement->fetch()){

        $new_product["product_name"] = $product_name; 

        if(isset($_SESSION["cart_products"])){
            if(isset($_SESSION["cart_products"][$new_product['product_code']]))
            {
                unset($_SESSION["cart_products"][$new_product['product_code']]);
            }
        }
        $_SESSION["cart_products"][$new_product['product_code']] = $new_product; //update or create product session with new item
    }
}

Now, what I want is to retrieve data from two tables, i.e products AND cat_1, both have exactly same column names. Obviously, the data is different in both the tables. However, I want to retrieve the same column from cat_1 too, i.e product_name.

I have tried using JOIN method, but got this error: Fatal error: Call to a member function bind_param() on boolean in D:\Work\offline\.

So, is there any way to do this? Any solution will be greatly appreciated.

Thanks in advance.


You should use join here, but, as you have the same column name in both table, you must give alias to make it works:

$statement = $mysqli->prepare("
    SELECT
           products.product_name AS p_product_name,
           cat_1.product_name AS c_product_name
      FROM products
      JOIN cat_1
        ON --join clause here, maybe products.product_code = cat_1.product_code--
     WHERE products.product_code=? LIMIT 1");

....
$new_product["p_product_name"] = $p_product_name;
...