WHERE clause in INSERT statement using mysql/php

Question

After searching on Google, i came to know that, I can not use a WHERE clause in my INSERT query.. But i want to insert a value on column "Book_4" where "Student_ID = 1"

How can i do that ?? Is there any alternate to do that ?

Will be Thankful to You !

$Query = "INSERT INTO issued_books (Book_4) VALUES ('$IssuedBookNumber')" ;

EDITED: More Details Using insert query, when i insert a value in column "Student_ID" in my table. All columns in the row of Student_ID (except Student_ID) shows 0 in my DB. I dun know what this 0 means according to DB. It might be Null or numeric 0. If it is a numeric 0, then it should be updated using the UPDATE statement. But whenever i'm trying to update it, it never updates using UPDATE statement. That's why i'm asking !

P.S: All columns have Datatype INT.

Hope you understand what i want to say :)

Here is the complete code. Suppose: Student_ID is already created having the value 2. IssuedBookNumber = 51

Using the above values: Result = A new row is created having all columns 0 except the column "IssuedBookNumber" that is having value = 51.

While i want, the result should be: On row Student_ID = 2, Book_4 should be 51.

The point is, When i inserted a value on Student_ID, all other columns becomes 0 on the same row. But when any of the column on the same row having any number except the 0 (that was automatically came on all columns when i inserted a value in Student_ID). Update Query will work.. !

$IssuedBookNumber = $_POST['IssuedBookNumber'];
     $Student_ID = $_POST['StudentId'];

     $FetchingQuery = "SELECT * FROM issued_books WHERE Student_ID='" . $Student_ID . "'";

     $RunFetchingQuery = mysql_query($FetchingQuery);


     while ( $row = mysql_fetch_array( $RunFetchingQuery ) ) {

          $Book_1 = $row[ 'Book_1' ];
          $Book_2 = $row[ 'Book_2' ];
          $Book_3 = $row[ 'Book_3' ];
          $Book_4 = $row[ 'Book_4' ];
          $Book_5 = $row[ 'Book_5' ];

          }

          if(!empty($Book_4))
          {
               $Update =  "UPDATE issued_books SET Book_4='$IssuedBookNumber' WHERE Student_ID= '$Student_ID'";
             mysql_query ($Update);
          }

          else
          {
               $AddQuery = "INSERT INTO issued_books (Book_4) VALUES ('$IssuedBookNumber')";
    mysql_query ($AddQuery);
          }

Answer 1

That not an INSERT. That's an UPDATE. INSERT statements insert a new row. UPDATE statements update an existing row.

UPDATE issued_books
SET Book_4 = '$IssuedBookNumber'
WHERE Student_ID = '$Student_ID'

(I'm assuming you've properly escaped $IssuedBookNumber and $Student_ID)

Answer 2

Why not something like this:

$Query = "INSERT INTO issued_books (Book_4, Student_ID)
           VALUES ('$IssuedBookNumber', '$Student_ID')"

Answer 3

Use update statement.

update issued_books set Book_4 = $IssuedBookNumber
where Student_ID = '$Student_ID'

Answer 4

If you can add a unique index on the 2 columns combined (book_4, student_id)

This would be a good query andshould replace most of your code above

INSERT INTO issued_books (book_4, student_id) VALUES('$IssuedBookNumber','$Student_ID')
ON DUPLICATE KEY UPDATE
SET Book_4 = '$IssuedBookNumber';

Note: you while loop above is not needed since $Book_4 will always return the value of the last row.

your code will then look like this :)

$IssuedBookNumber = $_POST['IssuedBookNumber'];
$Student_ID = $_POST['StudentId'];

$Update =  "UPDATE issued_books SET Book_4='$IssuedBookNumber' WHERE Student_ID= '$Student_ID'";
mysql_query ($Update);

Things you should consider,

1. Switch to using PDO or mysqli
2. Escape your variables are your code is vulnerable to a SQL injection. Perhaps after you switch to PDO or mysqli you can use prepare statement.