CODEWITHSUNDEEP
phpmysqljoin
eve_mf
eve_mf

Reputation: 825

Recipes book - PHP confusion JOINING 3 tables, not displaying what expected

I am doing a recipe book. My database tables look like this:

The idea is that the user will be able to insert an ingredient for a recipe selecting how it is measured (volume, mass, length...), and then, it will display a list of the units for that measurement; for example, if I insert chicken, and I select it as "mass", next time the ingredient chicken is introduced, it will display a list of the units for measuring mass.

Anyway..... at the moment, I am trying to display a table with the recipe name, and the ingredients list, with the ingredient name, amount and unit name.

If I do the query in mysql as 

SELECT recipes_have_ingredients.recipe_id
     , ingredients.name
     , recipes_have_ingredients.ingredient_amount
     , units.name 
  FROM recipes_have_ingredients 
  JOIN ingredients 
    ON recipes_have_ingredients.ingredient_id = ingredients.id 
  JOIN units 
    ON recipes_have_ingredients.unit_id = units.id 
 WHERE recipe_id = 2

it displays:

+-----------+-------+-------------------+----------+
| recipe_id | name  | ingredient_amount | name     |
+-----------+-------+-------------------+----------+
|         2 | onion |                 1 | kilogram |
|         2 | milk  |                30 | litre    |
+-----------+-------+-------------------+----------+

Which is what I want, but, if I try to create a function of it as:

public function display_ingredients_for_recipe($recipe_id = ':recipe_id') {
    include 'includes/db_connection.php';   
    try {

        $sql = "SELECT recipes_have_ingredients.recipe_id, ingredients.name, recipes_have_ingredients.ingredient_amount, units.name FROM recipes_have_ingredients JOIN ingredients ON recipes_have_ingredients.ingredient_id=ingredients.id JOIN units ON recipes_have_ingredients.unit_id=units.id WHERE recipe_id=:recipe_id";

          $results = $conn->prepare($sql);
          $results->bindParam(':recipe_id', $recipe_id, PDO::PARAM_INT);
          $results->execute();
         } catch(PDOException $e) {
            echo 'Error: ' . $e->getMessage() . '<br />';
            return array();
        }
          return $results->fetchAll(PDO::FETCH_ASSOC);
        }

And I try to use it (recipe['id']) is coming from the display_recipes method:

public function display_recipes() {
    include 'includes/db_connection.php';
    try {

        $sql = "SELECT id, name FROM recipes";

        $results = $conn->prepare($sql);
        $results->execute();
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage() . '<br />';
        return array();
    }
    return $results->fetchAll(PDO::FETCH_ASSOC);
}

 $ingredients = $recipeObj->display_ingredients_for_recipe($recipe['id']);
 echo '<pre>';
 print_r($ingredients);
 echo '</pre>'; die();

It is displaying:

Array
(
    [0] => Array
        (
            [recipe_id] => 1
            [name] => gram
            [ingredient_amount] => 350
        )

)

And I don't know what I am doing wrong.... why if I do the query on mysql it selects what I want but then, if I try to debug the ingredients I can't make the ingredient name and the unit name to appear.

Any help will be appreciated.

Upvotes: 2

Views: 88

Answers (1)

Erik
Erik

Reputation: 3636

Because you have two fields with the same name (which, ironically, also happens to be "name"). PHP can't have two of the same key in an array, so they overwrite. If you rename one (or both) of the fields, it should work.

SELECT recipes_have_ingredients.recipe_id, ingredients.name AS ingredient_name, recipes_have_ingredients.ingredient_amount, units.name AS unit_name ...

Upvotes: 2

Related Questions