Click here to Skip to main content
15,559,491 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have just been writing some code for a project which aims to update/edit existing content (movies) on the site. I'm having problems figuring out what is wrong with my code though. I've attached the code which shows the section where I think the problem lies as the first section of this code works and will update the director table to reflect a new director in the system. However, the update statement does not seem to be working and I can't figure it out as I've tried all SQL statements and they all work fine so I am thinking it must be an error in this code somewhere, but cannot figure out where. Just wondering if anyone can spot anything wrong with the code here as a second pair of eyes can often be so useful. Any help would be much appreciated :)

if($_SERVER["REQUEST_METHOD"]==="POST" && isset($_POST['movietoeditid'])){
    $conn= connect();
    $directorsArray = allDirectors();
    $yearsArray = allYears();
    $allActors = allactors();
    $allGenres = allgenres();
    $movieid = $_POST['movietoeditid'];
    $movie = filmitem($movieid);
    $movie = $movie[0];
    $originalTitle = $movie['movie_title'];
    $originalDir = $movie['director'];
    $originalTime = $movie['runtime'];
    $originalRev = $movie['revenue'];
    $originalYear = $movie['year'];
    $originalImg = $movie['movie_img'];
    $originalCert = $movie['certificate'];
    $originalActors = $movie['actors'];
    $originalGenres = $movie['genres'];
    $originalDesc = $movie['description'];
    $title = $_POST['movietitle'];
    $dir = $_POST['director'];
    $time = $_POST['runtime'];
    $rev = $_POST['revenue'];
    $year = $_POST['year'];
    $image = $_POST['image'];
    $cert = $_POST['certificate'];
    $urate = $_POST['rating'];
    $popu = $_POST['popularity'];
    $desc = $_POST['description'];
    $actorArray = $_POST['actors'];
    $genreArray = $_POST['genres'];
    if($dir !== $originalDir){
        if(!in_array($dir, $directorsArray)){
            $sqld = "INSERT INTO directors (director_name) VALUES ('$dir')";
            $res = $conn->query($sqld);
                echo $conn->error;
        $sqlDirId = "SELECT director_id FROM directors WHERE director_name = '$dir'";
        $dirIdRow = $conn->query($sqlDirId);
            echo $conn->error;
        while($rowDir = $dirIdRow->fetch_assoc()){
            $dirId = $rowDir['director_id'];
        $updateDir = "UPDATE movies SET director_id = '$dirId' WHERE movies.movie_id = '$movieid'";
        $sqlUpdateDir = $conn->query($updateDir);
            echo $conn->query();

What I have tried:

I have tried all SQL statements in my database and they work fine.
Updated 18-Apr-22 11:40am
Richard MacCutchan 18-Apr-22 8:46am    
Try this slightly modified command:
$updateDir = "UPDATE movies SET director_id = '$dirId' WHERE movie_id = '$movieid'"; // remove "movies." in front of movie_id

1 solution

Don;t do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Chances are that once you'd fixed that through your whole app, the problem you have noticed will be fixed as well.
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