MYSQL Insert Data Into Table With Foreign Key From Another Table

Question

I have two tables: book and borrowed_books.

Book Table has book_ID | Book Name

Borrowed_Books has student_ID | Book Name

The Book names from both tables are Indexed.

I added the values of Book Table into an HTML Form select/option and it is being read but the problem is when I'm trying to insert the value, it's not reading the option name or value.

HTML:

<select id="heard" class="form-control">
<option value="">Choose Book</option>
 <?php require_once '../mysql/bookssql.php';
  while($row = mysqli_fetch_array($data, MYSQLI_ASSOC)){ 
   echo '<option name="borrow_book" value="'.$row['Title'].'">' .$row['Title']. '</option>';
  }
 ?>
</select>

PHP Insert Code:

<?php $borrow_book = $_POST['borrow_book'];
 $upload = "INSERT INTO `borrowed_books` (`student_ID`, `Book Name`) VALUES ('23, '$borrow_book');";
?>

The foreign key should be the book ID, not the book name. – Barmar Nov 17 '19 at 16:08 — Barmar, Nov 17 '19 at 16:08
`'23,` has an extra quote. – Funk Forty Niner Nov 17 '19 at 16:16 — Funk Forty Niner, Nov 17 '19 at 16:16

Barmar · Accepted Answer · 2019-11-17T16:18:50.800

The name should be in the <select> element, not the <option>s.

<select id="heard" name="borrow_book" class="form-control">
<option value="">Choose Book</option>
 <?php require_once '../mysql/bookssql.php';
  while($row = mysqli_fetch_array($data, MYSQLI_ASSOC)){ 
   echo '<option value="'.$row['Title'].'">' .$row['Title']. '</option>';
  }
 ?>
</select>

You have a typo in $upload, there's an extra ' before 23.

You should stop substituting variables directly into query strings, and use prepared statements with parameters. See How can I prevent SQL injection in PHP?

MYSQL Insert Data Into Table With Foreign Key From Another Table

1 Answers1