CODEWITHSUNDEEP
phpmysqlsql
erol_smsr
erol_smsr

Reputation: 1496

Foreach loop with SQL statement

I'm creating a system that allows you to add a message with multiple images uploaded. The uploading of all the images goes well, however, when I try to insert all the image locations into a table that has a foreign key from the table 'messages' (so that every image knows to which message it belongs), I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`scrapll`.`scrapimage`, CONSTRAINT `scrapimage_ibfk_1` FOREIGN KEY (`scrap_id`) REFERENCES `scraps` (`scrap_id`))' in C:\xampp\htdocs\scrapll_m_nonstatic\process\new\scrap_process.php:132 Stack trace: #0 C:\xampp\htdocs\scrapll_m_nonstatic\process\new\scrap_process.php(132): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\scrapll_m_nonstatic\process\new\scrap_process.php on line 132

This only happens when I try to upload multiple images. When I only upload one image, no issues at all.

Here's the code:

foreach($_FILES['scrapPhotos']['tmp_name'] as $key => $tmp_name ){
                $fileName = $_FILES['scrapPhotos']['name'][$key];
                $fileSize = $_FILES['scrapPhotos']['size'][$key];
                $fileTmp = $_FILES['scrapPhotos']['tmp_name'][$key];
                $fileType= $_FILES['scrapPhotos']['type'][$key];

                if($_FILES['scrapPhotos']['name'][$key]){
                    // Get file extension of uploaded file
                    $imgFileExtension = strrchr($fileName, ".");

                    // Check if file extension of uploaded file is valid
                    if(!in_array($imgFileExtension, $validFileExtensions)) {
                        echo $scrapErrors[0];
                    }
                    // Check if file size is valid (!> 10000000)
                    elseif($fileSize > MAXFILESIZE) {
                        echo $scrapErrors[1];
                    }
                    else {                  
                        // The path to Scrap image
                        $imgLoc = "../../../scrapll_m/static/img/user/scrap/orig/";

                        // Move files to appropiate location
                        $imgFile = sha1(uniqid($_FILES['scrapPhotos']['name'][$key]));
                        $imgFilePath = $imgLoc . $imgFile . $imgFileExtension;

                        // Store image(s) on the server
                        move_uploaded_file($_FILES['scrapPhotos']['tmp_name'][$key], $imgFilePath);

                        $insertImgQuery = 'INSERT INTO scrapimage (scrap_id, image_original_size, image_scrap_size) 
                                                    VALUES (LAST_INSERT_ID(), ?, ?)';
                        $prepInsertImg = $conn->prepare($insertImgQuery);
                        $prepInsertImg->execute(array($imgFilePath, $imgFilePath));
                    }
                }
            }

So, why can't I make the SQL query at the bottom of my code to be executed multiple times and add one row with the next image locations to the database? I've put it in a foreach to do that, but it doesn't seem to work.

Upvotes: 0

Views: 109

Answers (3)

downforme
downforme

Reputation: 413

LAST_INSERT_ID() evaluates to 0 in the first iteration, the you insert the row with 0 as scrap_id. in the next loo-iteration LAST_INSERT_ID() evaluates to ... wait for it ... 0 again.

plus, it only works with auto_increment columns, so if scrap_id is autoincrement you want to insert NULL

Upvotes: 1

Cargo23
Cargo23

Reputation: 3189

Yeah, like lealhugui said, you are inserting LAST_INSERT_ID() as the value for scrap_id in scrapimage. Your constraint is requiring that there be a row in scraps with that same id.

Somewhere, probably above this loop, you should have figured out what the scrap_id is for this row in scraps. Then use that same id for every scrapimage.

Upvotes: 1

lealhugui
lealhugui

Reputation: 177

Basically, the database is telling you that the FK referenced by the "scrap_id" column is not a valid value. Are you sure that you have a record on the "scrap" table so the "ids" in the scrap.scrap_id and scrapimage.scrap_id can match?

Upvotes: 1

Related Questions