The query
Code:
select PK_grpsrvc, FK_srvc, service, FK_grp, groupname, has_timetable from group_srvc left join services on PK_srvc=FK_srvc left join groups on PK_grp=FK_grp order by service, groupname
The code that generates and runs the above query and stores the result in an array
Code:
291 /******************************************************************************
292 NAME: get_groupservices
293 FUNCTION: get groupservices
294 IN:
295 dbcnx database connection
296 where where clause
297 order sort order
298 limit limit
299 OUT:
300 msg return message
301 allgroupservices array with records for each groupservice; key is PK_grpsrvc
302 RETURN:
303 query result
304 NOTE:
305 html safe
306 'active' hardcoded to 'y'
307 ******************************************************************************/
308 function get_groupservices($dbcnx,&$msg,&$allgroupservices,$where="",$order="",$limit="")
309 {
310
311 $what = "PK_grpsrvc, FK_srvc, service, FK_grp, groupname, has_timetable";
312 $from = "group_srvc ";
313 $from .= "left join services on PK_srvc=FK_srvc ";
314 $from .= "left join groups on PK_grp=FK_grp ";
315
316 disp_msg("<p class=\"qrytxt bold\">get_groupservices()</p>");
317 $rc=query_table($dbcnx,$msg,$from,$what,$where,$order,$limit);
318 if(!$rc)
319 return $rc;
320
321 while($result=mysql_fetch_assoc($rc))
322 {
323 $result['active']="y";
324 $allgroupservices[$result['PK_grpsrvc']]=$result;
325 }
326
327 return $rc;
328 }
Line 317 executes the actual query and returns the resource (query_table basically calls
mysql_query()); you can replace this with your own code. In line 321 to 325 I fetch the records and store them in an associative array allgroupservices (forget about line 323, just for my own needs). The index is PK_grpsrvc; in your case that can / will be the ID that you mention.
So you will now have an array index by a key and each entry containing a full record as pulled from the database (specified in $what)
This is how it's called ($order was set earlier in the code)
Code:
44 // connect to incident database
45 ////////////////////////////////////
46 $dbcnx=connect($msg);
47 if(!$dbcnx)
48 {
49 $alt="<p class=\"db_error\">A technical problem prevented access to the database</p>";
50 disp_msg($msg,$alt);
51 do_htmlfooter();
52 exit;
53 }
54
55 // get all records
56 ////////////////////////////////////
57 $rc=get_groupservices($dbcnx,$msg,$allgroupservices,"",$order,"");
58 if(!$rc)
59 {
60 $alt = "<p class=\"db_error\">Error querying the database</p>";
61 $msg = "<p class=\"db_error\">$msg</p>";
62 disp_msg($msg,$alt);
63 mysql_close($dbcnx);
64 $dbcnx=0;
65 do_htmlfooter();
66 exit;
67 }
68 else
69 {
70 $msg = "<p class=\"db_ok\">$msg</p>";
71 disp_msg($msg);
72 }
After this, you can loop through your result set (mine is called allgroupservices) and populate the combobox. I have code for it but it's overkill for your purpose. Basically
Code:
echo "<select>\n";
foreach($allgroupservices as $id => $dta)
{
echo "<option value=\"$id\">";
echo htmlentities($dta[$display]) . "</option>\n";
}
echo "</select>\n";
$display indicates the field (in the record set) that I want to display; e.g the primary key, the group or the service. The result looks like below (I combined group and service in another part of the code and added it to the record(s)).
Code:
<select>
<option value="10">CSN..Bez Valley</option>
<option value="19">CSN..Bloemfontein</option>
<option value="1">CSN..Brixton</option>
...
...
<option value="264">MND..Worcester</option>
<option value="239">MND..Zeerust</option>
</select>
