Inserting into table based on alternate table value

Question

I can't get the syntax right for this. I'm trying to get the $nproj_hours value that is sumitted via a form to look-up the hours_id key value associated to it in the hours table, and put that numerical value in the summary table in a new row (I'll be doing this for departments and projects as well, so if there's a way to wrap them all up into one, I have a projects and departments table too).

Final Code:

if (isset($_POST['btnnew']))  

    {
    echo "<pre>Value of \$_POST:</br>";print_r($_POST);echo"</pre>";

        $nclarity_id    = $_POST['nclarity_id'];
        $nproj_hours    = $_POST['nproj_hours'];
        $ndept_name     = $_POST['ndept_name'];
        $proj_id        = $_POST['nclarity_id'];
        $hours_id       = $_POST['nproj_hours'];
        $dept_id        = $_POST['ndept_name'];

        $sql        = "INSERT INTO `summary` VALUES (null,'$proj_id','$hours_id','$dept_id',null)" 
                            or die ("couldn't update".mysql_error());
        $query = mysql_query($sql);

//echo "<pre>Value of \$sql:</br>";print_r($sql);echo"</pre>";
        if ($query)
{
echo "success!";
}
else
            {
        die('error inserting new record'.mysql_error());
    } // end of the nested if statement
}

?>

<table width="500" border="0" cellspacing="1" cellpadding="0">
   <tr>
   <td align="center">&nbsp;</td>
   <td align="center"><strong>Clarity ID</strong></td>
   <td align="center"><strong>Hours</strong></td>
   <td align="center"><strong>Department</strong></td>
   </tr>
<form name="form1" method="post" action="stupid.php">
<tr>
<td>&nbsp;</td>
<select name="nclarity_id">

<?php
$sql = "SELECT proj_id, clarity_id FROM projects " . "ORDER by clarity_id";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['proj_id']."\">".$row['clarity_id']."</option>\n  ";
}
?>

</select>
<select name="nproj_hours">
<?php
$sql = "SELECT hours_id, proj_hours FROM hours";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['hours_id']."\">".$row['proj_hours']."</option>\n  ";
}
?>
</select>

<select name="ndept_name">

<?php
$sql = "SELECT dept_id, dept_name FROM departments";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['dept_id']."\">".$row['dept_name']."</option>\n  ";
}
?>
</select>
   <input type="submit" name="btnnew" value="Enter New Record">
</tr>
</table>

<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="500" border="1" cellspacing="0" cellpadding="3">
</tr>


<?php
while($rows=mysql_fetch_array($res))
{

?>

<?php
}

?>

</table>
</td>
</tr>
</table>


<?php
mysql_close();
?>

Answer 1

You have to change your query - specifically remove the keyword VALUES and add the column names. Refer the spec for the exact syntax and details. I have just put in colname1, colname2, colname3, colname4 - you need to replace that with the actual column names from your summary table

INSERT INTO summary (colname1, colname2, colname3, colname4) 
   SELECT NULL, NULL, proj_hours, NULL
   FROM hours 
   WHERE proj_id ='$nproj_hours

As per usual, the disclaimer to avoid using such queries due to possibility of SQL Injection is applicable. Please google sql injection / prepared statements / parameterized queries etc if you are not aware of the same.