MySQL won't INSERT last item from for loop into database

Question

I'm looping through some data to display on my page, and then insert each loop to a row in a database (personal learning exercise for PHP and MySQL).

The for loop runs 5 times (for example, sometimes it may loop more/less), and I am able to successfully insert the data for the first 4 loops, but am having difficulty figuring out why the last loop won't insert into the database.

All 5 loop iterations display on my page, I'm not quite sure why the last loop won't insert into the database.

Here is my for loop that includes the MySQL code:

$artworksIterations = 1

count($artworksTitle[$x]) = 5

for ($x = 0; $x < $artworksIterations; $x++) {
    for ($y = 0; $y < count($artworksTitle[$x]); $y++) {
        $savedartworksTitle = $artworksTitle[$x][$y];
        echo "TITLE: " . $savedartworksTitle . "<br>";
        $savedartworksArtist = $artworksArtist[$x][$y];
        echo "ARTIST: " . $savedartworksArtist . "<br>";
        $savedartworksYear = $artworksYear[$x][$y];
        echo "YEAR: " . $savedartworksYear . "<br>";
        $savedartworksMedium = $artworksMedium[$x][$y];
        echo "MEDIUM: " . $savedartworksMedium . "<br>";
        $implodeGene = implode(", ", $artworksGene[$x][$y]);
        echo "GENRES: " . $implodeGene;
        $savedartworksDisplay = $artworksDisplay[$x][$y];
        echo "<br><img src='" . $savedartworksDisplay . "'><br>";
        echo "<br>----<br>";
        $sql = "INSERT INTO Artworks (title, artist, year, medium, display, genres) VALUES ('$savedartworksTitle', '$savedartworksArtist', '$savedartworksYear', '$savedartworksMedium', '$savedartworksDisplay', '$implodeGene');";
        mysqli_query($conn, $sql);
    } // end of y
} // end of x

Any help would be deeply appreciated. Thank you :)

Show the output of `echo $sql`. Also, try to commit changes after the loop. — Michael, Sep 16 '17 at 01:57
`INSERT INTO` will fail if your duplicate a `PRIMARY KEY`. Can you verify that your table `Artwoks` has such a `PRIMARY KEY` and your data is unique? — Jacques Amar, Sep 16 '17 at 02:06
@JacquesAmar I have the `PRIMARY KEY` linked to the first column, id. Here is the Table Structure of the db (https://imgur.com/peJoIQF). — Sam Sverko, Sep 16 '17 at 02:12
@MichaelO. Just gave it a try: echo'ing the `$sql` does display the final loop iteration, which matches the previous loops exactly. Not sure why this isn't inserted into the db... — Sam Sverko, Sep 16 '17 at 02:16
You need to check for errors then: `if (mysqli_query($conn, $sql) === FALSE) { printf("Errormessage: %s\n", mysqli_error($conn)); }` — Jacques Amar, Sep 16 '17 at 02:22
If all the other entries are inserted OK then you might have an apostrophe in the last entry thus causing a sql syntax error and prevention of the record into the database. — , Sep 16 '17 at 02:26

score 1 · Accepted Answer · answered Sep 16 '17 at 03:20

Thank you to everyone who helped dissect my issue!

It turns out, by mere coincidence, the data from the last loop of each iteration contained an apostrophe ('), which was why they weren't inserted into the database (syntax error).

Thank you to Jacques for pointing out that I should be checking for errors, that is where it informed me of a syntax error (also, thank you for jeff who clued in on my apostrophe issue).

To fix this, I used the mysqli_real_escape_string() function save the data as a safe format for the database (probably something I should have done in the first place, learning experience!).

Updated working code:

for ($x = 0; $x < $artworksIterations; $x++) {
    for ($y = 0; $y < count($artworksTitle[$x]); $y++) {
        // display the information on the web page
        echo "TITLE: " . $artworksTitle[$x][$y] . "<br>";
        echo "ARTIST: " . $artworksArtist[$x][$y] . "<br>";
        echo "YEAR: " . $artworksYear[$x][$y] . "<br>";
        echo "MEDIUM: " . $artworksMedium[$x][$y] . "<br>";
        echo "GENRES: " . implode(", ", $artworksGene[$x][$y]);
        echo "<br><img src='" . $artworksDisplay[$x][$y] . "'><br>";

        // save data to MySQL safe format
        $savedartworksTitle = mysqli_real_escape_string($conn, $artworksTitle[$x][$y]);
        $savedartworksArtist = mysqli_real_escape_string($conn, $artworksArtist[$x][$y]);
        $savedartworksYear = mysqli_real_escape_string($conn, $artworksYear[$x][$y]);
        $savedartworksMedium = mysqli_real_escape_string($conn, $artworksMedium[$x][$y]);
        $savedartworksDisplay = mysqli_real_escape_string($conn, $artworksDisplay[$x][$y]);
        $implodeGene = mysqli_real_escape_string($conn, implode(", ", $artworksGene[$x][$y]));

        // insert data into database
        $sql = "INSERT INTO Artworks (title, artist, year, medium, display, genres) VALUES ('$savedartworksTitle', '$savedartworksArtist', '$savedartworksYear', '$savedartworksMedium', '$savedartworksDisplay', '$implodeGene');";
        if (mysqli_query($conn, $sql) === FALSE) {
            printf("ERROR: %s\n", mysqli_error($conn));
        }
        echo "<br>----<br>";
    } // end of y
} // end of x

Use parametrised queries instead – Strawberry Sep 16 '17 at 06:12 — Strawberry, Sep 16 '17 at 06:12

MySQL won't INSERT last item from for loop into database

1 Answers1