How to get value of select-option tag from database

Question

i made this form of adding data in the database. I have exam table that contains exam_code(PK), exam_title and subject_code(FK). Here is the design

<div style="width:800px;height:auto;margin-left:auto;margin-right:auto;margin-top:50px;">
  <form action="" method="POST" class="form-horizontal" role="form">
    <div class="form-group">
      <div class="col-xs-6 col-sm-3 ">
        <input name="code" type="text" class="form-control" id="excode" placeholder="Enter Exam Code">
      </div>
      <div class="col-xs-6 col-sm-3 ">
        <input name="title" type="text" class="form-control" id="extitle" placeholder="Enter Exam Title">
      </div>
      <div class="col-xs-6 col-sm-3 ">
        <select name="subjcode" class="form-control">
            <option selected="selected">Choose subject</option>
            <option disabled="disabled">---------------------------------</option>
            <?php 
               include('db.php');
               $subj = $connect->query("SELECT subject_code FROM subject");
               while($row1 = mysqli_fetch_array($subj)){
                  echo "<option value = $row1[subject_code]>$row1[subject_code]</option>";
               } 
            ?>
        </select>
        </div>
        <div class="col-xs-6 col-sm-3 ">
        <input type="submit" name="add" class="btn btn-default" value="Add" />
      </div>
    </div>
  </form>
</div>

Is my query here correct? I can't think of anyway to insert the data. Here..

<?php 
    include('db.php');
    if(isset($_POST['add'])){
        $excode = $_POST['code'];
        $extitle = $_POST['title'];
        $subcode = $_POST['subjcode'];

        $examinsert = $connect->query("INSERT INTO exam (exam_code, exam_title, subject_code) VALUES ('$excode', '$extitle', '$subcode')");

        if(!$examinsert){
             die("<script>
                    alert('Error encountered, Reloading page');
                    window.location.href='teacher.php';
                 </script>");
        }else{
            die("<script>
                   alert('Your exam title has been added. You will see your titles in the Examination title section below!');
                   window.location.href='teacher.php';
                 </script>");
         }
     }

 ?>

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Sep 13 '17 at 12:46
Your query looks ok, is it not executing? If not, have you checked your error logs? — Jay Blanchard, Sep 13 '17 at 12:47
Your question is unclear, what is the problem? What errors are you getting and what is it you want our help with exactly? — Epodax, Sep 13 '17 at 12:48
@Epodax oh sorry. My problem is that i can't insert it into the database cause the subject_code is being inserted again. What's another way to call the subject_code, so that it won't be repeated in inserting the data? — Louise M., Sep 13 '17 at 12:55
@JayBlanchard I tried running the query in xampp. This is the error i got - #1452 - Cannot add or update a child row: a foreign key constraint fails (`bsit4d_wap`.`exam`, CONSTRAINT `exam_ibfk_1` FOREIGN KEY (`subject_code`) REFERENCES `subject` (`subject_code`)) — Louise M., Sep 13 '17 at 23:31

score 0 · Answer 1 · answered Sep 13 '17 at 12:59

0

Change Your PHP CODE FROM This

<?php 
     include('db.php');
     $subj = $connect->query("SELECT subject_code FROM subject");
     while($row1 = mysqli_fetch_array($subj)){
       echo "<option value = $row1[subject_code]>$row1[subject_code]</option>";
     } 
?>

To This

<?php 
         include('db.php');
         $subj = $connect->query("SELECT subject_code FROM subject");
         while($row1 = mysqli_fetch_array($subj)){
           echo "<option value = ".$row1[subject_code].">".$row1[subject_code]."</option>";
         } 
    ?>

answered Sep 13 '17 at 12:59

So Store $row1[subject_code] in variable and print variable in single quote. – Sep 13 '17 at 13:19
where do i put that? – Louise M. Sep 13 '17 at 23:28

score 0 · Answer 2 · answered Sep 14 '17 at 05:31

store value in Variable and than put it.

    <?php 
             include('db.php');
             $subj = $connect->query("SELECT subject_code FROM subject");
             while($row1 = mysqli_fetch_array($subj)){
               $subjectCode = $row1[subject_code];
               echo "<option value = $subjectCode>$subjectCode</option>";
             } 
        ?>

Its Works.

score 0 · Answer 3 · answered Sep 14 '17 at 14:05

0

I fixed it by adding a query that disables the foreign keys.

$set = $connect->query('SET foreign_key_checks = 0');
/*insert query*/
$set1 = $connect->query('SET foreign_key_checks = 1');

answered Sep 14 '17 at 14:05

Louise M.

15
8

How to get value of select-option tag from database

3 Answers3