Cookbook/Recipes Database - Reading/Displaying Data (PHP/PDO)

[snakeeyes]

I am currently trying to put a cookbook together that holds a collection of recipes. This is purely to try to help me improve my knowledge of mysql/php.

I have found an outline for the database at Link to outline

The database structure is

I am using PHP/PDO

Currently my issue is, I have the output but within my while loop I have a title, sub title and paragraph and then the ingredients for said recipe however I because I have the title etc inside the while loop it then outputs for every ingredient so I have title, sub title, paragraph, ingredient then title and so on.

$recipe4 = "SELECT * FROM recipes
                    JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipe_id
                    JOIN measurement_qty ON recipe_ingredients.measurement_quantity_id = measurement_qty.measurement_qty_id
                    JOIN measurement_units ON recipe_ingredients.measurement_id = measurement_units.measurement_id
                    JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
                    ";
                  
                    $stmt = $pdo->query($recipe4);
                  
                    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                            
                            echo "<h1>".$row['recipe_name']."</h1>";
                            echo "<em>".$row['recipe_description']."</em>";
                            echo "<p>".$row['qty_amount']." ".$row['measurement_description']." ".$row['ingredient_name']."</p><br>";    
                            
                            
                    }

Output

There should only be 1 title then all the ingredients listed underneath.

I realise the title etc should be outside the while loop however if I move the code outside the while loop then the title etc does not display.

I realise that this code might not be the most efficient which maybe part of the problem.

I would appreciate pointers/advice on how to improve on my current code.

Thanks

[phdr]

You need to pre-fetch all the data into a php variable (see the PDO fetchAll() method.) You can then access the zeroth element to get the unique/one-time information for display.

Next, you should list out the columns you are SELECTing. This helps avoid mistakes, especially with JOINed queries, and it makes your code self-documenting (anyone can see what you are trying to do without needing the database table definitions.)

Also, (PDO::FETCH_ASSOC), if you set the default fetch mode to assoc when you make the database connection, you don’t need to specify it in each fetch statement.

Lastly, that query will match all recipes. If that’s your intent, you would want to index/pivot the data using the recipe id as the main array index, when you fetch the data. This will give you a sub-array for each recipe, that you can loop over to output the data for each individual recipe. There’s actually a fetch mode that will index the data for you.

[snakeeyes]

I am unsure what you mean regarding pre-fetch?

@phdr

Also, (PDO::FETCH_ASSOC), if you set the default fetch mode to assoc when you make the database connection, you don’t need to specify it in each fetch statement.

I have declared this in the connection file now.

echo "<pre>";
                        print_r($row);
                        echo "</pre>";

Gives the following output

Currently I am trying to get the full menu list with the specific ingredients listed unnder appropriate title.

I am now getting

Notice
: Undefined index: recipe_name in
C:\xampp\htdocs\mysqlpdo\index.php
on line
332

[phdr]

@snakeeyes

I am unsure what you mean regarding pre-fetch?

Did you look at the documentation for the fetchAll() method?

@snakeeyes

Gives the following output

@snakeeyes

on line
332

Since we don’t know what you specifically changed to produce that value in $row or what the code leading up to line 332 is, I’m not sure what help we can give you.

[snakeeyes]

@snakeeyes

I am unsure what you mean regarding pre-fetch?

Yes, I looked at the php manual and was no better off.

@snakeeyes

Gives the following output

Using print_r I get the array outputted

The code now is

$recipe4 = "SELECT * FROM recipes
                    JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipe_id
                    JOIN measurement_qty ON recipe_ingredients.measurement_quantity_id = measurement_qty.measurement_qty_id
                    JOIN measurement_units ON recipe_ingredients.measurement_id = measurement_units.measurement_id
                    JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
                    ";
                  
                    $stmt = $pdo->query($recipe4);
                  
                    while($row = $stmt->fetchAll()){
                        
                        echo "<pre>";
                        print_r($row);
                        echo "</pre>";
                        
                        $title = $row['recipe_name'];
                        $sub_title = $row['recipe_description'];
                        
                        
                        echo $title;
                        echo $sub_title;
}

[phdr]

The fetchAll() method’s purpose is to fetch all the rows of data at once. You can then do whatever you want with that data, such as accessing the zeroth (1st row) element to get the recipe name and description, or looping over the data -

// get all the rows of data at once
$rows = $stmt->fetchAll();
// get a copy of the zeroth row
$heading = $rows[0];

echo "<h1>{$heading['recipe_name']}</h1>";
echo "<em>{$heading['recipe_description']}</em>";
foreach($rows as $row)
{
    // access elements in $row to display the qty, measurement, and ingredient
}

[snakeeyes]

Using your code

@phdr

// get all the rows of data at once
$rows = $stmt->fetchAll();
// get a copy of the zeroth row
$heading = $rows[0];

echo "<h1>{$heading['recipe_name']}</h1>";
echo "<em>{$heading['recipe_description']}</em>";
foreach($rows as $row)
{
    // access elements in $row to display the qty, measurement, and ingredient
}

Resolves part of the issue but when echoing out the titles from the recipe table using the indexes [0] it doesn’t correspond.

The recipe table is

From this there are the following entries

I am aiming to list all the titles etc, with the relevant ingredients underneath the appropriate title.

The output I have currently from the amended code is

From

$heading = $rows[0];
                        
 echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";
 echo "<em class='stocklist'>{$heading['recipe_description']}</em>";
                        
 $heading1 = $rows[1];    
 echo "<h1 class='stocklist'>{$heading1['recipe_name']}</h1>";
                  
 $heading2 = $rows[6];    
 echo "<h1 class='stocklist'>{$heading2['recipe_name']}</h1>";

I am having to physically go into the table to see what index the next menu title is.

Would the solution be to use DISTINCT in my query so that I only get 1 instance of each title rather than 1 title then 1 ingredient then same title then next ingredient or is there a better way of doing this?

Something along the lines of

$recipe = "SELECT DISTINCT recipe_name, recipe_description FROM recipes
JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipe_id
JOIN measurement_qty ON recipe_ingredients.measurement_quantity_id = measurement_qty.measurement_qty_id
JOIN measurement_units ON recipe_ingredients.measurement_id = measurement_units.measurement_id
JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
";

Also wouldn’t a foreach/while loop be better than writting the following out for all the titles etc?

$heading = $rows[0];
echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";
echo "<em class='stocklist'>{$heading['recipe_description']}</em>";
                  
$heading2 = $rows[2];    
echo "<h1 class='stocklist'>{$heading2['recipe_name']}</h1>";

Thanks for your help so far.

[phdr]

@phdr

Lastly, that query will match all recipes. If that’s your intent, you would want to index/pivot the data using the recipe id as the main array index, when you fetch the data. This will give you a sub-array for each recipe, that you can loop over to output the data for each individual recipe. There’s actually a fetch mode that will index the data for you.

So, you are actually trying to do the above ^

The fetch mode to add in the fetchAll(…) call is - PDO::FETCH_GROUP This will group the fetched data into sub-arrays, indexed by the first column you are SELECTing. You would typically use an id as this index. In your case, it would be the recipe_id.

After you do that, use print_r($rows) to see what the data looks like.

[snakeeyes]

My aim is to have 1 unique recipe title with the relevant sub heading then list all the ingredients used in that recipe.

e.g.

Pizza base
sub heading

ing 1
ing 2
etc

Basic Cheese Pizza
sub heading

ing 1
ing 2
etc etc

[snakeeyes]

When using the following query

$recipe = "SELECT * FROM recipes
JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipe_id
JOIN measurement_qty ON recipe_ingredients.measurement_quantity_id = measurement_qty.measurement_qty_id
JOIN measurement_units ON recipe_ingredients.measurement_id = measurement_units.measurement_id
JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
 ";

And the following PHP

$stmt = $pdo->query($recipe);
                  
$rows = $stmt->fetchAll(PDO::FETCH_GROUP);
                        
$heading = $rows[0];
                        
echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";
echo "<em class='stocklist'>{$heading['recipe_description']}</em>";
                  
foreach($rows as $row){
                            
echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";    
                            
}
                      
                        
                        
echo "<pre>";
print_r($rows);
echo "</pre>";

I get the following output

These warning messages span from line 328 as shown to line 335. In total 11 warnings

Print_r gives me a total of 7 all with sub-arrays displaying the relevant data

[phdr]

@snakeeyes

Print_r gives me a total of 7 all with sub-arrays displaying the relevant data

So, all you need to do is use two nested foreach() loops -

foreach($rows as $recipe_id=>$recipe)
{
	$heading = $recipe[0];
	// display the heading items here...
	
	foreach($recipe as $row)
	{
		// display the individual ingredient data in $row here...
		
	}
}

[snakeeyes]

I appreciate your time on fixing this. Thanks.

The output now is

Just in case anyone else is interested in the code

$recipe = "SELECT * FROM recipes
                    JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipe_id
                    JOIN measurement_qty ON recipe_ingredients.measurement_quantity_id = measurement_qty.measurement_qty_id
                    JOIN measurement_units ON recipe_ingredients.measurement_id = measurement_units.measurement_id
                    JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
                    ";
                  
                    $stmt = $pdo->query($recipe);
                  
                    $rows = $stmt->fetchAll(PDO::FETCH_GROUP);
                        
                    //$heading = $rows[0];
                        
                        //echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";
                        //echo "<em class='stocklist'>{$heading['recipe_description']}</em>";
                  
                        foreach($rows as $recipe_id=>$recipe)
                        {
	                       $heading = $recipe[0];
	                       // display the heading items here...
                            echo "<br>";
                            echo "<h1 class='stocklist'>{$heading['recipe_name']}</h1>";
                            echo "<em class='stocklist'>{$heading['recipe_description']}</em>";
	                        echo "<br>";
	                       foreach($recipe as $row)
                           {
		                      // display the individual ingredient data in $row here...
                            echo "<p class='stocklist'>".$row['qty_amount']." ".$row['measurement_description']." ".$row['ingredient_name']."</p>";   
		                    echo "<br>";
	                       }
                        }
                  
                      
                        
                        
                        //echo "<pre>";
                        //print_r($rows);
                        //echo "</pre>";