Nesting query issue

ali_p

Hi
I am trying to get results from 2 tables to display in a listing.
Table 1 is sports_new - This contains event_ref event_title event_summary amongst others
Table 2 is booking_options - This conatains lookup_ref (same as event_ref) booking_option amongst others.

Basically booking_options can contain none or several records from one event, So

Event 1 title
summary
booking_option1
booking-option2
booking_option3

Event 2 title
summary

Event 3 title
summary
booking_option1

And so on.

My problem with the code that i have is that all my booking_options are just being displayed at the end of the page as opposed to with the event that it belongs to.

Here is my code



//for the fixture information
$sql2="
SELECT *
FROM sports_new
WHERE event_group = 'Celtic'
AND status = 'Live'
ORDER BY event_date ASC
";

$result2 = mysql_query($sql2,$conn)or die(mysql_error());

while ($row2 = mysql_fetch_array($result2)) {
	//give a name to the fields
	$event_id=$row2['event_id'];
	$event_ref=$row2['event_ref'];
	$event_category=$row2['event_category'];
	$event_group=$row2['event_group'];
	$event_title=$row2['event_title'];	
	$event_date_elements = explode("-",$row2['event_date']);
	$sql_event_date = $event_date_elements[2]."-".$event_date_elements[1]."-".$event_date_elements[0];
	$event_summary=$row2['event_summary'];
	$event_image=$row2['event_image'];
	$event_details=$row2['event_details'];		
	$status=$row2['status'];	
	$display_blockd .="
	<div>	
	<p class=\"bodytext_bold_blue\">$event_title</p>
	<p class=\"bodytext_blue\">Date: $sql_event_date</p>
	<p>$event_summary </p> 
	<p><a href=\"sports_soccer_full.php?event_ref=$event_ref\">Read more</a></p>
	</div>";
	$query2 = "Select * from booking_options Where lookup_ref = '$event_ref'";
	$result3 = mysql_query($query2,$conn)or die(mysql_error());
	while ($row3 = mysql_fetch_array($result3)) {
	$lookup_ref=$row3['lookup_ref'];
	$lookup_name=$row3['lookup_name'];
	$booking_option=$row3['booking_option'];
	$booking_ref_code=$row3['booking_ref_code'];
	$booking_ref_name=$row3['booking_ref_name'];
	$depart_code=$row3['depart_code'];
	$out_day=$row3['out_day'];
	$out_month=$row3['out_month'];
	$dest_code=$row3['dest_code'];
	$duration=$row3['duration'];	
	//Now we need to distinguish between the 3 booking options and have the relevant links
	if($booking_option == 'Holiday Package'){
	$display .="<p><a href=\"http://bookings.com/live/holiday.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">$booking_option - $booking_ref_name</a></p>";
	}if($booking_option == 'Flights Only'){
	$display .="<p><a href=\"http://bookings.com/live/flightOnly.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">$booking_option - $booking_ref_name</a></p>";
	}if ($booking_option == 'Accommodation Only'){
	$display .="<p><a href=\"http://bookings.com/live/accomOnly.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">$booking_option - $booking_ref_name</a></p> ";
}
}
}


<body>
<? echo "$display_blockd"; ?>
<? echo "$display"; ?>


</body>

Any suggestions will be greatly appreciated.

ali_p

Ok,

I have decided against the nested loop idea and have tried using a table join instead.


//for the fixture information
$sql2="SELECT event_ref, event_title, lookup_ref, lookup_name, booking_option, booking_ref_name, event_summary, event_date
FROM sports_new LEFT JOIN booking_options
ON sports_new.event_ref = booking_options.lookup_ref
WHERE sports_new.event_group = 'Celtic'
AND sports_new.status = 'Live'
ORDER BY sports_new.event_date ASC";

$result2 = mysql_query($sql2,$conn)or die(mysql_error());

$num_can = mysql_num_rows($result2);

print $num_can ;

while ($row2 = mysql_fetch_array($result2)) {
	//give a name to the fields
	$event_id=$row2['event_id'];
	$event_ref=$row2['event_ref'];
	$event_category=$row2['event_category'];
	$event_group=$row2['event_group'];
	$event_title=$row2['event_title'];	
	$event_date_elements = explode("-",$row2['event_date']);
	$sql_event_date = $event_date_elements[2]."-".$event_date_elements[1]."-".$event_date_elements[0];
	$event_summary=$row2['event_summary'];
	$event_image=$row2['event_image'];
	$event_details=$row2['event_details'];		
	$status=$row2['status'];

$lookup_ref=$row2['lookup_ref'];
$lookup_name=$row2['lookup_name'];
$booking_option=$row2['booking_option'];
$booking_ref_code=$row2['booking_ref_code'];
$booking_ref_name=$row2['booking_ref_name'];
$depart_code=$row2['depart_code'];
$out_day=$row2['out_day'];
$out_month=$row2['out_month'];
$dest_code=$row2['dest_code'];
$duration=$row2['duration'];	

//Now we need to distinguish between the 3 booking options and have the relevant links
if($booking_option == 'Holiday Package'){
$display .="<p><a href=\"http://bookings.com/live/holiday.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">(Holiday) $booking_option - $booking_ref_name</a></p>";
}if($booking_option == 'Flights Only'){
$display .="<p><a href=\"http://bookings.com/live/flightOnly.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">(Flight) $booking_option - $booking_ref_name</a></p>";
}if ($booking_option == 'Accommodation Only'){
$display .="<p><a href=\"http://bookings.com/live/accomOnly.jsp?overrideTitle=$event_title+($sql_event_date)&specificHotel=$booking_code&specificHotelName=$booking_name\" class=\"bold_link\">(Accomm) $booking_option - $booking_ref_name</a></p> ";

}
$display_blockd .="
	<div>	
	<p class=\"bodytext_bold_blue\">$event_title</p>
	<p class=\"bodytext_blue\">Date: $sql_event_date</p>
	<p>$event_summary </p> 
	<p><a href=\"sports_soccer_full.php?event_ref=$event_ref\">Read more</a></p>
	$display</div>";

}

<body>
<? echo "$display_blockd"; ?>


</body>

However my results from the booking_options table have gone crazy. It doesn't relate to what really in in the table. Booking-options have been added to events which shouldn't be.

I really could do with an expert eye to take a glance over this please.

Many thanks

Roger_Ramjet

Well the first thing you have to remember is that events with multiple booking options are going to produce multiple rows in the results. That means you have to modify the processing loop to trap these extra rows and modify the output accordingly.

Search these forums for 'loop control' cos I've posted how to do this many times and so have others.

ali_p

Thanks for that. I have been testing this out for most of the morning. However I still don't know what I'm doing wrong. Is this suitable for the nested query or the joined table query.

Thanks in advance.

Roger_Ramjet

For the joined table query. That query will produce results like this

event1 flight_only
event1 holiday_package
event2 flight_only
event3 flight_only
event3 holiday_package
event3 accom_only
event4 NULL

So you need to track the event from row to row so that you know when it changes.

As to the booking options for events being jumbled: show us some of the results