PHP script not saving data to MySQL database

Question

I have a PHP script that is supposed to save movie IDs to a MySQL database table called "seen_movies". However, the data is not being saved to the table even though the script runs without errors. I've checked that the table has the correct columns and data types, and also checked that the SQL statement is correct. I'm not sure what else to try.

Here's my PHP code:

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Origin: http://localhost:3001');
header('Access-Control-Allow-Origin: http://localhost:3000');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');

$servername = "*********";
$username = "******";
$password = "************";
$dbname = "my_movies";

// create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// get selected movie IDs
if (isset($_POST['selectedMovies'])) {
    $selectedMovies = $_POST['selectedMovies'];

    // prepare and bind SQL statement
    $stmt = $conn->prepare("INSERT INTO seen_movies (id) VALUES (?)");
    $stmt->bind_param("i", $movieId);

    // loop through selected movie IDs and execute SQL statement
    foreach ($selectedMovies as $movieId) {
        $stmt->execute();
    }

    // close statement
    $stmt->close();
}

echo json_encode(array('status' => 'success'));

$conn->close();
?>

Response in console in browser:

Saving movies: (2) [315162, 700391]     PopularMovies.js:28
API response: {status: 'success'}       PopularMovies.js:40 

Here is the table layout in mysql

    mysql> DESCRIBE seen_movies
       -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

I have checked the table data on phpmyadmin and in mysql and the table is empty.

Ive been trying to figure it out for hours, thanks!

Edit :

I looked at the network tab in developer tools to see if the code was interacting with the APi, i can see it listed however the response shows 'failed to load data: no content available for preflight request'

Answer 1

The title field is non-null, meaning you cannot insert a row without including a title:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| title | varchar(255) | NO   |     | NULL    |                |

But your SQL is trying to do exactly that:

$stmt = $conn->prepare("INSERT INTO seen_movies (id) VALUES (?)");

There is no error handling in the code so you're not seeing the errors, but the statements are failing.

Answer 2

An answer more or less similar to your question was posted here.

But to be short, the binding is executed only once, with the value that is in your variable $movieID at that time. As the variable is not initialized, $stmt becomes a "insert nothing" query, which is totally valid (hence the success), but nothing is inserted into your DB (and this query is executed multiple times.).

Try and modify your code in this way:

    //loop through selected movie IDs and execute SQL statement
    foreach ($selectedMovies as $movieId) {
        $stmt->bind_param("i", $movieId);
        $stmt->execute();
    }

Hope this helps !