4 tables..Confused and Lost

SpringTimes

Hello,
I need help please..

I have 4 tables:

region:
regionID --primary
regionName

plantType:
plantTypeID --primary
plantTypeDesc

plant:
plantID --primary
plantName
plantScientificName
plantDesc
plantImage

relation: -- no primary keys
plantID
regionID
plantTypeID

-The idea is simple, I want the user to be able to see a list of plant names and their descriptions after choosing the plant type and the region from two drop down menus and clicking the submit button. So I want the query to display the plantName , plantDesc.
-The form right now is connected to the region and plantType tables and shows the list of regions and plantTypes available but I don't know how to write the query so it selects the plantName and plantDesc from plant table that has the same plantID in relation table that is in the same row as the regionID and plantTypeID that the user chose.

Confusing? im confused too 😕 specially for someone who is very new to php and database in general !! I thought it wasn't that hard!!

Please any hint? references? ideas? ANYTHING!! :o
Is there a better way to do the tables? is it better to join the relation table with the plant one? it will be too big!

I appreciate any help..

igorlopez

The easy way but most costly in terms of cpu utilization is to make a two staged query
where you first get the plants from table relation and then extract data for each plant in a loop. I know there is a more efficient way but since i am also new to this i have not lernt it yet.

$query = "SELECT plantID FROM relation WHERE regionID=$regID AND plantTypeID=$typeID";
// $plants will contain all plantID matching region and planttype
$plants = mysql_query($query);
// Loop through all plantID and get the requested data
while($row=mysql_fetch_assoc($plants)) {
   $plantID = $row['plantID'];
   $query = "SELECT plantName, plantDesc FROM plant WHERE plantID=$plantID";
   $result = mysql_query($query);
   $plantData = mysql_fetch_assoc($result);
   $plantName = $plantData['plantName'];
   $plantDesc = $plantData['plantDesc'];
}

Note!!! The above code snippet is not tested and will have bugs in it. You should also consider taking care of empty sets and all other scenarious. This is just to give you an idea (even though it is not a bright one)

The variables

$regID 
$typeID

is extracted from your _POST array with proper handling.

SpringTimes

Thank you so much.. it's working fine 🙂

I have another question.. what If i want the result to display a list of plant names only and by clicking on the name, the rest of the info will be displayed (plant description and image) which are in the same table.. do I need another form and a query?

I would appreciate any hints 🙂

ahundiak

The best way to do the original query is to join the plant table to your relation table. Something like:

SELECT plant.plantId, plant.plantName, plant.plantDesc
FROM relation
JOIN plant ON relation.plantId = plant.plantId
WHERE
  relation.regionId = $regionId AND
  relation.plantTypeId = $plantTypeId;

And for your second question, make the plant name a link and include the plantId as a parameter. You can then write a simple query to get all the plant info for a given plant.

SpringTimes

Thank you 🙂

got it to work...