sql insert statement syntax?

Question

I prefer to do it in php not in mysqli because of time.

How to insert part of column in MySQL using php ?

For example ,my column table in sql are : courseid, code, title, cr ,prerequisite, std_id, status, grade . I used table to fetch the data in php , but status and grade are empty of data because I want user to inter them to see what courses each student complete from the list of courses . I want user to insert them with the other column that are fixed with specific data . The problem i face in this code is that the insert data is coming in new record and i want to insert in just two column which are status and and Grade with the names of course .

How to insert in just in Status and Grade

enter code here
     <?php
    error_reporting(0);
   include('config.php');
    $sql="select * from studyplan";
    $result=mysql_query($sql);
    while ($row = mysql_fetch_array($result)){

    $id=$row['courseid'];

    $code=$row['code'];
    $Title=$row['title'];
          $cr=$row['cr'];
       $pre=$row['prerequisite'];
       $std=$row['std_id'];
       $status=$row['status'];
      $grade=$row['grade'];


  echo "<tr  class='edit_tr' id='$id'>

   <td class='edit_td'>
            <span class='text' id='one_$id' >$id </span>
          </td>

          <td class='edit_td'>
            <span class='text' id='one_$id' >$code </span>
          </td>

          <td class='edit_td'>
            <span class='text' id='two_$id' >$Title</span>
          </td>

          <td class='edit_td'>
            <span class='text' id='three_$id' >$cr</span>
          </td>

          <td class='edit_td'>
            <span class='text' id='three_$id' >$pre</span>
          </td>

          <td class='edit_td'>
            <span class='text' id='three_$id' >$std</span>
          </td> 
          <td class='edit_td'>
            <span class='text' id='three_$id' >$status</span>
        <select name='status' id='status'>

            <option value=''>Completed</option>
                        <option value='B'>Not complate</option>


                    </select>

    </td>

          <td class='edit_td'>
            <span class='text' id='three_$id' >$grade</span>

                                    <select name='grade' id='grade'>
      <option value=''>A</option>
            <option value='B'>B</option>
            <option value='C'>C</option>
            <option value='D'>D</option>
            <option value='F'>F</option>
    </select>

    </td> 

    </tr>";  

 }

 echo '<input type="hidden" name="courseid" value="' . $id . '" />';

   ?>

  <p align="center"><a href="year.html">Go Back</a> </p>
    <p><input type="submit" value="Add Link"></p>
</tr>
   </table>
</form>
  </div>
 </body>
  </html>

<?php 

   if ($_SERVER['REQUEST_METHOD'] == "POST") { 


  $status=$_POST['status'];
  $grade=$_POST['grade'];

Insert statement

    $SQL = " INSERT INTO studyplan ";
    $SQL = $SQL . " (status, grade) VALUES ";
    $SQL = $SQL . " ('$status', '$grade') ";
    $result = mysql_db_query($db,"$SQL",$cid);

    if (!$result) { 
     echo("ERROR: " . mysql_error() . "\n$SQL\n");  }

    echo ("New Link Added\n");

   } 

  mysql_close($cid); 
 ?>

i want it like small pic

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 03 '15 at 13:29
You should really move to PDO or mysqli and prepared statements to avoid sql injection. That would also solve your current problem which is you possibly sql injecting yourself and never finding a match because of the space after `$id` — jeroen, Nov 03 '15 at 13:29
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 03 '15 at 13:29

score 3 · Accepted Answer · edited May 23 '17 at 11:59

Your form has no input element with the name courseid, which is why the index is undefined. You probably want to add it as a hidden element in this case, since it doesn't appear to be a user-entered value:

echo '<input type="hidden" name="courseid" value="' . $id . '" />';

A few things to note:

Your code is wide open to SQL injection attacks. This basically means that any user can execute any code they want on your database. Please read this (and of course this) and look into using prepared statements. Always treat user input as values, not as executable code.
Even though the value is in a "hidden" field, that doesn't mean the user can't modify it. Always validate all user input before performing an action and updating data. Otherwise users can modify any record they want in your database, whether it's theirs or not.
Even with this added, I can't guarantee your form will "work" as expected. You seem to be missing form tags entirely, which may cause unexpected behavior since you seem to intend to have a separate form per record in that table. The table structure itself also appears to be broken (I'm seeing multiple </tr> tags), so there's no telling how the browser is going to interpret any of this. Please fix your markup or you're likely going to encounter some very strange and difficult to diagnose bugs.

sql insert statement syntax?

1 Answers1