nested while loop issue

Jewelie

I am attempting to display recipes stored in a mysql database on my site using php. I have 2 tables with the following structure:

recipes:
recipe_id
recipe_name
recipe_instructions
recipe_image

ingredients:
ingredient_id
recipe_id
ingredient_name

What I am attempting to do is to show the recipe name with the ingredients listed underneath.

This code show the recipe_name and the ingredients, but not correctly. For example it will give a list like this:

Basic Salad

2-3 large cucumbers

1/2 c lemon juice

1-2 T tahini

1/3 c chopped onion

2 cloves garlic

1 c mint

1 t sea salt

2 avocados, diced

1 c chopped tomatoes

1/2 c red bell pepper

alfalfa sprouts to top

salad mix

tomatoes

Lemon Flax Dressing

Cool as a Cucumber Soup

when in the database, the ingredients table looks like this:

ingredient_id
recipe_id
ingredient_name

1
1
2-3 large cucumbers

2
1
1/2 c lemon juice

3
1
1-2 T tahini

4
1
1/3 c chopped onion

5
1
2 cloves garlic

6
1
1 c mint

7
1
1 t sea salt

8
1
2 avocados, diced

9
1
1 c chopped tomatoes

10
1
1/2 c red bell pepper

11
1
alfalfa sprouts to top

12
2
salad mix

13
2
tomatoes

14
2
Lemon Flax Dressing

There has to either be an error with how I am querying the data or how I am using the while loops.

Here is the relevant code:

$result = @mysql_query ( 'SELECT * FROM recipes ORDER BY recipe_name' );

$result2 = @mysql_query ( 'SELECT * FROM recipes LEFT JOIN ingredients ON recipes.recipe_id=ingredients.recipe_id' );


if (!$result) {
	exit ( '<p>Error performing query: ' . mysql_error() . '</p>' );
}

while ($row = mysql_fetch_array ($result) ) {
	echo '<p>' . $row[ 'recipe_name' ] . '</p>' ;

while ($row2 = mysql_fetch_array ($result2) ) {
	echo '<p>' . $row2 ['ingredient_name'] . '</p>';
}

}

Why aren't the ingredients showing up under its corresponding recipe_name? Are you not allowed to have nested while loops?

Thanks in advance--

tsinka

Hi,

you need only the LEFT JOIN (if you want to show recipes without ingredients, too ... otherwise execute an inner join query).

Additionally, you need to add some code that checks if the recipe id has changed. Something like:

<?PHP
ini_set("error_reporting",E_ALL);
/*$cn = mysql_connect("localhost","user","Pass");
mysql_select_db("db",$cn);*/
$sql = '
SELECT r.recipe_id AS rid,r.recipe_name AS rname,r.recipe_image as rim,
       r.recipe_instructions AS rins,i.ingredient_id AS iid,i.ingredient_name  AS iname
FROM recipes r LEFT JOIN ingredients i 
ON r.recipe_id=i.recipe_id';
$result = mysql_query ($sql);

if (!$result) {
    exit ( '<p>Error performing query: ' . mysql_error() . '</p>' );
}

$recipe_id = 0;
$instructions = "";
$image = "";

while ($row = mysql_fetch_array ($result) ) {
  if ($row['rid'] != $recipe_id) {
    if ($recipe_id != 0)
      echo "<br>".$instructions."<br>";
    $instructions = ($row['rins'] != null) ? $row['rins'] : 'no instructions found!';
    $image = ($row['rim'] != null) ? $row['rim'] : 'noimg.jpg';
    echo '-----------------------------------<br>';
    echo $row[ 'rname' ].'<br><br>' ;
    $recipe_id = $row['rid'];
    echo '<img src="'.$image.'"><br><br>';
  }

  if ($row['iid'] == null) {
    echo 'no ingredients found!<br>';
  } else {
    echo $row['iname'].'<br>';
  }
}
echo '<br>'.$instructions.'<br>';
?>

Not the most beautiful code but should work.

Thomas

qadeer_ahmad

Hi,

as i see "recipe_id" is present in both of ur table use it and first run a query

<?
$result = @ ( 'SELECT * FROM recipes ORDER BY recipe_name' );
if (!$result) {
exit ( 'Error performing query: ' . mysql_error() . '' );
}

while ($row = mysql_fetch_array ($result) ) {
echo '' . $row[ 'recipe_name' ] . '' ;
$recipe_id=$row[ 'recipe_id' ] ;
$result2 = @ ( "SELECT * FROM ingredients where recipe_id='".$recipe_id."'")
$row2 = mysql_fetch_array ($result2) ;
echo '' . $row2 ['ingredient_name'] . '';
}

Jewelie

First of all, thank you both for replying.

I am still trying to get tsinka's suggestion to work. I am just getting a blank screen so there may be something wrong with my syntax, but I understand it conceptually, so it still helped 🙂

qadeer_ahmad, I got your suggestion to work- almost. . . the problem is that it is not displaying all of the ingredients, just the first one. For example, I got something like this:

Basic Salad

salad mix

Cool as a Cucumber Soup

2-3 large cucumbers

Any suggestions to get it to display all of the ingredients for that recipe_id?

tsinka

Hi,

post the code you have so far.

In the code qadeer_ahmad posted replace

$row2 = mysql_fetch_array ($result2) ;
echo '<p>' . $row2 ['ingredient_name'] . '</p>';

while ($row2 = mysql_fetch_array ($result2)) {
echo '<p>' . $row2 ['ingredient_name'] . '</p>';
}

EDIT: Add error_reporting(E_ALL); to the script so see all errors.

Thomas

Jewelie

Here is my code: it is giving me a blank screen

 <?php

//Connect to the database.

$dbcnx = @mysql_connect ('localhost' , 'radish', 'durian');
if (!$dbcnx) {
	exit ( '<p>Unable to connect to the database server at this time.</p>' );
}

mysql_select_db ( 'radish_db', $dbcnx );

if (!@mysql_select_db ( 'radish_db') ) {
	exit ( '<p>Unable to locate the recipe database at this time.</p>' );
}


$result = @mysql_query ( 'SELECT * FROM recipes ORDER BY recipe_name' ); 
if (!$result) { 
exit ( '<p>Error performing query: ' . mysql_error() . '</p>' ); 
} 

while ($row = mysql_fetch_array ($result) ) { 
echo '<p>' . $row[ 'recipe_name' ] . '</p>' ; 

$recipe_id=$row[ 'recipe_id' ] ; 

$result2 = @mysql_query ( "SELECT * FROM ingredients where recipe_id= " ' $recipe_id ' " ") ;

while_($row2_=_mysql_fetch_array_($result2))_{ 
echo_'<p>'_._$row2_['ingredient_name']_._'</p>'; 
}
}

?>

ahundiak

Is this a direct cut and paste? What's up with all those underscores?
echo''.$row2['ingredient_name'].'';

If you edited the script after pasting it then it becomes diffiecult to determine just where the errors might be.

Your second query can be simply:
"SELECT * FROM ingredients where recipe_id='$recipe_id'"

Add error_reporting(E_ALL); to the top of the file.

Get rid of all those @ suppressions.

Test for errors after every query:
$result = mysql_error("...");
if (mysql_errno()) die(mysql_error());

qadeer_ahmad

Hi,
Try to print the query, i am afraid there is nothing in ur query.

before this line

$result2 = @ ( "SELECT * FROM ingredients where recipe_id= " ' $recipe_id ' " ") ;

use this

print"SELECT * FROM ingredients where recipe_id= " ' $recipe_id ' " ")

also in this line

echo '' . $row[ 'recipe_name' ] . '' ;

make these changing

echo '' . $row[ 'recipe_name' ] . ' Testing for Name' ;

also in other all pionts where ur getting value in variables check the existence of these values..

tsinka

Please create a little phpinfo() script and post the output as attachment (save the page with your browser and rename the file to .txt).

Thomas