Question 108394 in PHP Question posted in
Official manual can be found here.

PHP script not saving data to MySQL database

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’

Tags:

2

Answers


  1. 0

    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 !

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search