CODEWITHSUNDEEP
phpmysql
Adictonator
Adictonator

Reputation: 158

mysql - Retrieve value from two tables in single query line

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.

Upvotes: 1

Views: 68

Answers (2)

Tony Chiboucas
Tony Chiboucas

Reputation: 5683

Yes, the right way to do that is with a join, but the trick is what kind of join you want.

From your description, it is VERY unclear exactly what you're trying to do, so I'll answer your questions as it was asked. Also, bindParam always better with named variables (":pcode").

$statement = $mysqli->prepare("
    SELECT products.product_name as name, cat_1.product_name as cat_name
    FROM products
    LEFT JOIN cat_1 ON products.product_code = cat_1.product_code
    WHERE products.product_code = :pcode
    LIMIT 1
");
$statement->bindValue(':pcode', $new_product['product_code']. PDO::PARAM_STR);
$statement->$execute();

No need for bind_result, or a loop, since you are limiting to 1 result...

$product = $statement->fetchAll(PDO::FETCH_ASSOC);
echo $product ['name'] .':'. $product ['cat_name'] . '\n<br>';

...but when you need a loop...

foreach ( $statement->fetchAll(PDO::FETCH_ASSOC) as $product ) {
    echo $product ['name'] .':'. $product ['cat_name'] . '\n<br>';
}

Upvotes: 0

Felippe Duarte
Felippe Duarte

Reputation: 15131

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;
...

Upvotes: 1

Related Questions