This should be so simple yet im waiting hours and hours to figure it out... please have a look at my code and tell me what is wrong..
The whole story is that im making a site for people to search for suitable local plants in their region by choosing the region, the plant type, position (shade), water needed. So I created 5 tables for plants, regions, types, shade, water (with IDs as primary, auto) and another table called relation to put all the IDs together..
I created a simple form for the Admin to insert new plants "insert.php" and then my modify.php does all the insertion into tables.. should be simple right?
My first INSERT works correctly : inserts plants names into the plants table.
then I SELECT the plantID for the new inserted plant in plants table ...working fine.
my third query should insert the regionId, typeId, shadeId, waterId along with plantId (all for the inserted plant) in the relation table... *BUT NOT WORKING*
I know my form is working coz I can echo everything i insert and I can get the Ids too.. but the second INSERT isn't working...
Here is my code: insert.php -- using functions to read some lists from database..
<form action="modify.php" method="post" name="form1">
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3"><strong>Insert the details of the new plant </strong></td>
</tr>
<tr>
<td width="256">Plant Common Name</td>
<td width="4">:</td>
<td width="523"><input name="comname" type="text" id="comname"></td>
</tr>
<tr>
<td>Plant Scientific Name</td>
<td>:</td>
<td><input name="sciname" type="text" id="sciname"></td>
</tr>
<tr>
<td>Region </td>
<td>:</td>
<td><?php echo regionList(); ?></td>
</tr>
<tr>
<td>Plant Type </td>
<td>:</td>
<td><?php echo plantTypeList(); ?></td>
</tr>
<tr>
<td>Shade requirement </td>
<td>:</td>
<td><?php echo shadeList(); ?></td>
</tr>
<tr>
<td>Water Requirement </td>
<td>:</td>
<td><?php echo waterList(); ?>
</td>
</tr>
<tr>
<td><p> </p>
<p> </p></td>
<td> </td>
<td><input type="submit" name="add" value="Add plant" id="add"></td>
</tr>
</table>
</form>
Here is the code for my modify.php
<?php
include_once("config.php");
// plant common name and scientific name sent from insert form
$comname=$POST['comname'];
$sciname=$POST['sciname'];
$planttypeId=$POST['plantType'];
$shadeId=$POST['shade'];
$waterId=$POST['water'];
$regionId=$POST['region'];
//this insert is working fine
$query="INSERT INTO g_plant (plantName, plantSName)
VALUES ('$comname', '$sciname')";
mysql_query($query) or die('Error, insert query failed');
echo"test";
$query2="SELECT plantID FROM g_plant WHERE plantName = '$comname' AND plantSName = '$sciname'";
$result=mysql_query($query2);
$row = mysql_fetch_array($result);
$plantId = $row['plantID'];
//testing the chosing
echo"region: $regionId";
echo"type: $planttypeId";
echo"shade: $shadeId";
echo"water: $waterId";
echo"plantid: $plantId";
//this is the code that is not working---------------this insert query 🙁 ehy ehy ehy
$query3="INSERT INTO g_relation (plantID, regionID, plantTypeID, shadeID, waterID) VALUES ('$plantId', '$regionId', '$planttypeId','$shadeId','$waterId')";
mysql_query($query3); or die('Error, insert query failed');
echo"test";
mysql_close();
?>
sorry.. my code is a mess... Im new to PHP so please any advise??? why isn't my $query3 working??? if i comment it, everything else works fine!!! :S
