Database table contents to email problem (PHP and MySQL)

blokeofftheinternet · #1 (**permalink**) 04-28-04, 12:48 PM

Hi there!

I've developed a script (for a Restaurant and Guesthouse website) whereby people can send an email (to friends or themselves) which contains the restaurant's menu - the menu is stored on a database.

Now the problem I am having is that the script seems to "shave off" the first meal on every course, but displays all other courses.

My code is:

Code:

<?php

// SELECT STARTERS COURSE
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsCourseStarters = "SELECT meal, Course FROM meal WHERE Course = 'Starters'";
$rsCourseStarters = mysql_query($query_rsCourseStarters, $nantyfelin) or die(mysql_error());
$row_rsCourseStarters = mysql_fetch_assoc($rsCourseStarters);
$totalRows_rsCourseStarters = mysql_num_rows($rsCourseStarters);

//SELECT MAIN COURSE
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsCourseMain = "SELECT meal, Course FROM meal WHERE Course = 'Main Course'";
$rsCourseMain = mysql_query($query_rsCourseMain, $nantyfelin) or die(mysql_error());
$row_rsCourseMain = mysql_fetch_assoc($rsCourseMain);
$totalRows_rsCourseMain = mysql_num_rows($rsCourseMain);

//SELECT DESSERTS COURSE
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsDesserts = "SELECT meal, Course FROM meal WHERE Course = 'Desserts'";
$rsDesserts = mysql_query($query_rsDesserts, $nantyfelin) or die(mysql_error());
$row_rsDesserts = mysql_fetch_assoc($rsDesserts);
$totalRows_rsDesserts = mysql_num_rows($rsDesserts);

//SELECT COFFEE AND TEA COURSE
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsCoffeeTea = "SELECT meal, Course FROM meal WHERE Course = 'Coffee and Tea'";
$rsCoffeeTea = mysql_query($query_rsCoffeeTea, $nantyfelin) or die(mysql_error());
$row_rsCoffeeTea = mysql_fetch_assoc($rsCoffeeTea);
$totalRows_rsCoffeeTea = mysql_num_rows($rsCoffeeTea);

//SELECT VEGETARIAN MEALS
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsVegetarian = "SELECT meal, Course FROM meal WHERE Course = 'Vegetarian Meals'";
$rsVegetarian = mysql_query($query_rsVegetarian, $nantyfelin) or die(mysql_error());
$row_rsVegetarian = mysql_fetch_assoc($rsVegetarian);
$totalRows_rsVegetarian = mysql_num_rows($rsVegetarian);

//SELECT SALADS
mysql_select_db($database_nantyfelin, $nantyfelin);
$query_rsSalads = "SELECT meal, Course FROM meal WHERE Course = 'Salads'";
$rsSalads = mysql_query($query_rsSalads, $nantyfelin) or die(mysql_error());
$row_rsSalads = mysql_fetch_assoc($rsSalads);
$totalRows_rsSalads = mysql_num_rows($rsSalads);

//BUILDING THE EMAIL

$to = "$recipient"; 	//The email address of the recipient

$subject = "Message from $friend_name: The Nant-Y-Felin Menu"; 	//Subject of the Email

$message = "Hello \n\n"; //First line of the body of the email

$message .= $row_rsCourseStarters['Course'] . "\n\n";//Prints coursename 'Starters'
   while ($row_rsCourseStarters = mysql_fetch_assoc($rsCourseStarters)) {
	$message .= $row_rsCourseStarters['meal']. "\n"; 
	} //Displays list of meals in 'Starters'

$message .= "\n\n"; //Space between two courses

$message .= $row_rsCourseMain['Course'] . "\n\n"; //Prints coursename 'Main Course'
   while ($row_rsCourseMain = mysql_fetch_assoc($rsCourseMain)) {
	$message .= $row_rsCourseMain['meal']. "\n"; 
	} //Displays list of meals in 'Main Course'

$message .= "*All main course dishes are served with a selection of fresh vegetables."; //Extra bit

$message .= "\n\n";//Space between two courses

$message .= $row_rsVegetarian['Course'] . "\n\n"; //Prints coursename 'Vegetarian Meals'
   while ($row_rsVegetarian = mysql_fetch_assoc($rsVegetarian)) {
	$message .= $row_rsVegetarian['meal']. "\n"; 
	} //Displays list of meals in 'Vegetarian'

$message .= "\n\n";//Space between two courses

$message .= $row_rsSalads['Course'] . "\n\n"; //Prints coursename 'Salads'
   while ($row_rsSalads = mysql_fetch_assoc($rsSalads)) {
	$message .= $row_rsSalads['meal']. "\n"; 
	} //Displays list of meals in 'Salads'

$message .= "\n\n";//Space between two courses

$message .= $row_rsDesserts['Course'] . "\n\n"; //Prints coursename 'Desserts'
   while ($row_rsDesserts = mysql_fetch_assoc($rsDesserts)) {
	$message .= $row_rsDesserts['meal']. "\n"; 
	} //Displays list of meals in 'Desserts'

$message .= "\n\n";//Space between two courses

$message .= $row_rsCoffeeTea['Course'] . "\n\n";//Prints coursename 'Coffee and Tea'
   while ($row_rsCoffeeTea = mysql_fetch_assoc($rsCoffeeTea)) {
	$message .= $row_rsCoffeeTea['meal']. "\n"; 
	} //Displays list of meals in 'Coffee and Tea'

$from = "From: $friend_name <$friend_email>\n"; //Email address and name of sender

$header = $from; // set the from field in the header

$header .= "\n";// add a line feed

mail($to, $subject, $message, $header); // send the e-mail

//END OF BUILDING EMAIL
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<CENTER>Thanks</CENTER>
</body>
</html>
<?php
mysql_free_result($rsCourseStarters);

mysql_free_result($rsCourseMain);

mysql_free_result($rsDesserts);

mysql_free_result($rsCoffeeTea);

mysql_free_result($rsVegetarian);

mysql_free_result($rsSalads);
?>

Database:

Code:

meal_id   meal course_id  Course 
 
1 Assorted fish hors doeuvre 1  Starters 
2 Smoked mackerel 1 Starters 
3 Homemade pate 1 Starters 
4 Egg Mayonnaise 1 Starters 
5 Soup with crusty bread & butter 1 Starters 
6 Fruit juice or tomato juice  1 Starters 
7 Grapefruit cocktail 1 Starters 
8 Chilled melon 1 Starters 
9 Mushroom stroganoff with rice 4 Vegetarian Meals 
10 Vegetable lasagne with salad garnish 4 Vegetarian Meals 
11 Cold meats as available 6 Salads 
12 Cheese 6 Salads 
13 Fresh Salmon 6 Salads 
14 Roast fresh farm chicken 2 Main Course 
15 T-bone steak garni 2 Main Course 
16 Fillet steak garni 2 Main Course 
17 Sirloin steak garni 2 Main Course 
18 Lamb cutlets garni 2 Main Course 
19 Farmhouse grill 2 Main Course 
20 Gammon & pineapple or egg 2 Main Course 
21 Fried scampi with tartare sauce 2 Main Course 
22 Fillet of plaice with tartare sauce 2 Main Course 
23 Rainbow trout with prawns & mushrooms 2 Main Course 
24 Fresh salmon steak 2 Main Course 
25 Homemade fruit pie with fresh cream or ice cream 3 Desserts 
26 Sherry trifle 3 Desserts 
27 Peach Melba 3 Desserts 
28 Banana split 3 Desserts 
29 Pineapple Jamaican - pineapple in rum with ice cream and fresh cream 3 Desserts 
30 Chocolate nut sundae 3 Desserts 
31 Coffee walnut sundae 3 Desserts 
32 Sorbet 3 Desserts 
33 Meringue with fresh cream 3 Desserts 
34 Meringue with fresh cream & ice cream 3 Desserts 
35 Meringue with fresh cream, ice cream, & fruit 3 Desserts 
36 Cheese & biscuits 3 Desserts 
37 Dairy ice cream: 3 Desserts 
38 - Rum & raisin 3 Desserts 
39 - Chocolate 3 Desserts 
40 - Coffee 3 Desserts 
41 - Strawberry 3 Desserts 
42 - Vanilla 3 Desserts 
43 Gaelic or liqueur coffee 5 Coffee and Tea 
44 Freshly percolated coffee 5 Coffee and Tea 
45 Pot of tea - per person 5 Coffee and Tea

Better still: http://www.nantyfelin.co.uk/menu.php

Now when I test my script all I get (through the email) is:

Starters

Smoked mackerel
Homemade pate
Egg Mayonnaise
Soup with crusty bread & butter
Fruit juice or tomato juice
Grapefruit cocktail
Chilled melon

Main Course

T-bone steak garni
Fillet steak garni
Sirloin steak garni
Lamb cutlets garni
Farmhouse grill
Gammon & pineapple or egg
Fried scampi with tartare sauce
Fillet of plaice with tartare sauce
Rainbow trout with prawns & mushrooms
Fresh salmon steak
*All main course dishes are served with a selection of fresh vegetables.

Vegetarian Meals

Vegetable lasagne with salad garnish

Salads

Cheese
Fresh Salmon

Desserts

Sherry trifle
Peach Melba
Banana split
Pineapple Jamaican - pineapple in rum with ice cream and fresh cream
Chocolate nut sundae
Coffee walnut sundae
Sorbet
Meringue with fresh cream
Meringue with fresh cream & ice cream
Meringue with fresh cream, ice cream, & fruit
Cheese & biscuits
Dairy ice cream:
- Rum & raisin
- Chocolate
- Coffee
- Strawberry
- Vanilla

Coffee and Tea

Freshly percolated coffee
Pot of tea - per person

If you want to see what I mean go to:

www.nantyfelin.co.uk/newdesign/sendmenuform.php

And send one to yourself.

Cheers

blokeofftheinternet

P.S. The connection string works fine - already been tested as much as possible.

NeverMind · #2 (**permalink**) 04-29-04, 06:12 AM

I'll tell you why !!

first when you were selecting from menus, you called with mysql_fetch_assoc() berfore your loops !
so simply remove this lines:

PHP Code:


			
//remove all these lines ..

//and only use the while loops

$row_rsCourseStarters = mysql_fetch_assoc($rsCourseStarters);

$row_rsCourseMain = mysql_fetch_assoc($rsCourseMain);

$row_rsDesserts = mysql_fetch_assoc($rsDesserts);

$row_rsCoffeeTea = mysql_fetch_assoc($rsCoffeeTea);

$row_rsVegetarian = mysql_fetch_assoc($rsVegetarian);

$row_rsSalads = mysql_fetch_assoc($rsSalads);

with these lines, you were calling the first row, then ignoring it !!
remove them and it should work

blokeofftheinternet · #3 (**permalink**) 04-29-04, 10:34 AM

Done it and it works.

Thanks.