Sorry if this has been asked and answered but I have looked and can’t see it anywhere. Yes I am a nooby. Also, sorry if I inadvertantly put this in wrong thread.
What I have here is the need to report the contents of a mysql table (collectors) based on a user selection from the first table (area).
Currently, the page has a single dropdown form element that correctly shows of the contents of the area table . It has a submit button. The user does NOT need type anything simply select the area and a list of people in that areae will display. The code below works for the first part. I just need to get the second part working. Here’s the details:
I have a DB called Swarm with two tables.
Table 1 is called Area and contains two fields: AreaID (Primary Key), Area
Table 2 is called Collectors and contains five fields: CollectorID (Primary Key), Collector, Landline, Mobile & AreaID (Foreign Key).
There is a 1 to M relationship between the Area Table and the collectors table
For example data:
Area Table:
areaID / Area
1 / London
2 / Edinburgh
etc
Collectors Table
CollectorID / AreaID / Collector / Landline / Mobile
1 / 1 / Jim Smith / 123456 / 0789097
2 / 2 / Jo Soap / 34567 / 987654
3 / 2 / Bilbo Baggins / 987655/ 897987668
4 / 1 / Peter Jones /89723487634 /o89874
etc
So when the user chooses an area from the drop down List of the Areas available (London and Edinburgh in this case). The same Page (reloaded by the the submit button) displays all the fields with that areaID in the Collectors Table. In the above example, therefore. If the user chooses Edinburgh then Collectors Jo Soap and Bilbo Baggins will be displayed.
Hope this is clear. I hope you can help. Thanks in advance
Here is what I have so far:
[PHP]<?php
// Connect to MYSQL
$con = mysql_connect(“localhost”,“ausername”,"");
if (!$con)
{
die('Could not connect: ’ . mysql_error());
}
// Select the DB in MYSQL
mysql_select_db(“Swarm”, $con);
// Creating a Query
$sql = mysql_query(“SELECT areaID, Area FROM Area”) or die(mysql_error());
$areaname = “”;
while ($row = mysql_fetch_array($sql)) {
$areaname .= “<OPTION VALUE=”". $row[“areaID”] . “”>" . $row[“Area”] . “\n”;
}
?>
Swarm Area:
Choose
<?php echo $areaname; ?>
</form>
[/PHP]
This displays a dropdown box populated by the Area names and works great.
As mentioned above though would like users to choose the area and for and for all those people in that area to be displayed (on same page preferably.
Currently, (part 1) the page has a single dropdown form element that correctly shows of the contents of the area table (in this case London & Edinburgh (in the real DB there is a choice of 28 Areas). It has a submit button. The user does NOT need type anything in, simply select the area from the list, press the submit button and … well what I would like to happen is that … (Part 2) a list of people in that area will display.
The PHP code above works for Part 1. I just need to get the part 2 working.
I know that the SQL for searching the db for a specific area is:
SELECT Area.Area, collectors.Collector, collectors.LLine, collectors.Mob
FROM Area INNER JOIN collectors ON Area.AreaID = collectors.AreaID
WHERE (((collectors.AreaID)=areaID));But how to wrap this up in PHP is the problem.
Please help. :’(
Regards Peter