Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am currently inserting data from a csv file (movie information) into a MySQL database using php. My connection to the database using Xammp is all okay and the initial bit of my code runs okay and inserting the directors into the director table works fine and the names are inserted without duplicates, as intended, along with an auto incremented primary key. Pictures of table attached. 

My issue is with my next bit of code in which I am attempting to insert the information into the movies table which contains a foreign key of 'director_id'. When I run my code I get an error message and only 1 line of null data is inserted into my table.

As my connection is fine and I the php code to insert into directors table (code 1) runs okay I think there must be some mistake in the php code to insert into the movies table (code 2)?  

1) Code for directors table insert (runs successfully):
'''
<?php

include('db.php'); 

$filepath = "Movie-DataSet2_final.csv"; 

$f = fopen($filepath, 'r'); 

if($f === false){

    echo "Cannot find the file"; 
    exit(); 
}else{

    while (($row = fgetcsv($f)) !==false){

        $director = $row[2]; 

        $director = mysqli_real_escape_string($conn, $director); 

        if(empty($director)){
            $director = "Unknow director"; 
        }

        $sql = "INSERT INTO directors (director_name) VALUES ('$director')"; 

        $duplicate = "SELECT * FROM directors WHERE director_name='$director' "; 

        $check = $conn->query($duplicate); 

        if($check->num_rows == 0){
            $statement = $conn->query($sql); 

            if(!$statement){
                echo "<div> SQL error -".$conn->error."</div>"; 
            }
        }else 
        echo "<div>duplicate found at $director so no inserting"; 
    }
}
fclose($f); 
'''

2) Code for movies table insert (does not insert successfully): 

'''
<?php

include('db.php'); 

$filepath = "Movie-DataSet2_final.csv"; 

$f = fopen($filepath, 'r'); 

if($f === false){

    echo "Cannot find the file"; 
    exit(); 
}else{
    while(($row = fgetcsv($f)) !==false){

        $title = $row[0]; 
        $director = $row[2]; 
        $time = $row[5]; 
        $rev = $row[6]; 

        $finddir = "SELECT * FROM directors WHERE director_name = '$director' "; 

        $res = $conn->query($finddir); 

        $dir = $res->fetch_assoc(); 

        $dir_id = $dir['director_id']; 

        $insert = "INSERT INTO movies (movie_title, director_id, runtime, revenue)
            VALUES ('$title', '$dir_id', '$time', '$rev')"; 

        $inserttres = $conn->query($insert); 

        if(!$inserttres){
            echo $conn->error; 
        }

        echo "<p> movie inserted </p>"; 
    }
}
fclose($f); 
   
'''




[1] directors table with section of successfully inserted data: https://i.stack.imgur.com/gx7TF.png


[2] error message from attempted insert into movies table: https://i.stack.imgur.com/Ad8g7.png


[3] result when code to insert in movies table is run: https://i.stack.imgur.com/xcrUV.png

[4] section of csv file: https://i.stack.imgur.com/8mOHc.png


What I have tried:

I have checked inserted code so as to check sql queries are correct and have ensured database is connected okay via xammp
Posted
Updated 20-Feb-22 22:06pm
Comments
Richard MacCutchan 21-Feb-22 3:21am    
It would appear that the SELECT to find the director name is failing. You need to use your debugger to find out why.

1 solution

Your code is open to injection, maybe start working with PDO - See Tutorial

That being said, you are making a reference to director_id but nowhere did you gave it a value? Maybe add it in this part of your code depending on the array number -
$title = $row[0];
$director_id = $row[1]; //or whatever row the id is on... 
        $director = $row[2]; 
        $time = $row[5]; 
        $rev = $row[6];


You should also add error checking and check your database settings, it should not update a record if 1 field is missing. This can be fixed when you create your table by using the
NOT NULL
operator.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900