insert into database mysqli prepared statement

Question

I am trying to upload an image to my server and save its name and path into my database. Uploading the file to my server works, saving the data in the database does not.

images table

image_id    int(11)      
image_name  varchar(64)          
image_path  varchar(64)

Upload script

    // Upload image to the server
    $target_dir = "./path/to/image/folder/";
    $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
    $uploadOk = 1;
    $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

    // Check if image file is a actual image or fake image
    if(isset($_POST["submit"])) {
        $check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
        if($check !== false) {
            $uploadOk = 1;
        } else {
            echo "Die Datei ist keine Bilddatei.";
            $uploadOk = 0;
        }
    }

    // Check if file already exists
    if (file_exists($target_file)) {
        echo "Bitte nenne die Datei um, diese Datei existiert bereits.";
        $uploadOk = 0;
    }

    // Check file size
    if ($_FILES["fileToUpload"]["size"] > 500000) {
        echo "Diese Datei ist größer als 5MB und kann daher nicht hochgeladen werden.";
        $uploadOk = 0;
    }

    // Allow certain file formats
    if($imageFileType != "jpg" && $imageFileType != "png" && $imageFileType != "jpeg" && $imageFileType != "gif" ) {
        echo "Ausschließlich JPG, JPEG, PNG & GIF Dateien können hochgeladen werden.";
        $uploadOk = 0;
    }

    // Check if $uploadOk is set to 0 by an error
    if ($uploadOk == 0) {
        echo "Ein Fehler ist aufgetreten. Die Datei konnte nicht hochgeladen werden.";
    // if everything is ok, try to upload file
    } else {
        ========================= DATABASE PART ========================
        // create a database connection
        $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

        // prepare and bind
        $stmt = $conn->prepare("INSERT INTO images (image_id, image_name, image_path) VALUES (?, ?, ?)");
        $stmt->bind_param("iss", $image_id, $image_name, $image_path);

        // set parameters and execute
        $image_id = '';
        $image_name = $target_file;
        $image_path = $target_dir;
        $stmt->execute();

        $stmt->close();
        $conn->close();
        ==================================================================

        if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {            
            header("Location:http://homepage.com");
        } else {
            echo "Während des Hochladens der Bilddatei ist ein Fehler aufgetreten. Bitte versuche es erneut.";
        }
    }

The file is uploaded exactly as it should be.

The database stays empty without an error message.

var_dump on $image_name and $image_path return the correct values.

What am I missing?

I would be very thankful for any kind of help. Thank you!

no error handling on the connection, prepare, bind or execute. there is no way to know what went wrong — amdixon, Dec 31 '15 at 14:15
http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php — Funk Forty Niner, Dec 31 '15 at 14:15
seems you're putting the wagon before the horse here and you should be getting notices about it. — Funk Forty Niner, Dec 31 '15 at 14:16
@amdixon Just in time for New Year's. No better time to be "off the wagon" for a while ;-) — Funk Forty Niner, Dec 31 '15 at 14:18
@Fred-ii- very true, happy new year. may the errors be with you ;) — amdixon, Dec 31 '15 at 14:18
Great! Thank you! Then I will do more error handling. I thought probably an experienced eye could spot my mistake right away. Thank you for your help! — Schwesi, Dec 31 '15 at 14:19
*"I thought probably an experienced eye could spot my mistake right away."* - haven't we? — Funk Forty Niner, Dec 31 '15 at 14:20
possible duplicate of http://stackoverflow.com/questions/4261133/php-notice-undefined-variable-and-notice-undefined-index — Funk Forty Niner, Dec 31 '15 at 14:21
I like making a person "think" as that translates to *"Teach a person how to fish, feed them for life"* ;-) @kringeltorte — Funk Forty Niner, Dec 31 '15 at 14:23
so... this still isn't solved I see. I have my doubts as to why it's failing. but that guy below got an upvote and he's not responding yet in regards to the new error you got. Unless........... he's busy trying to find how "why". — Funk Forty Niner, Dec 31 '15 at 14:38
@Fred-ii- I think he gave up... But the error handling already helped me a lot! At least I have something to work with now! By now I get the file_exists() error, which is very weird but at least I am getting error messages! Thanks again for the help! — Schwesi, Dec 31 '15 at 14:44
the error comes most likely from `$image_id = '';` since nothing is assigned to it, and I am assuming that that column is an AI? (that could have a bearing on all this) what would you like to do with the question here? I didn't post an answer probably due to my instincts on this one. If the guy can't resolve it and you happen to (at some point), then you can post your own answer. But, I'm betting my money on that first parameter. Replace `$stmt->execute();` with `if(!$stmt->execute()){trigger_error("there was an error....".$conn->error, E_USER_WARNING);}` if you're not already doing so. — Funk Forty Niner, Dec 31 '15 at 14:50
addendum to above. change `INSERT INTO images (image_id, image_name, image_path) VALUES (?, ?, ?)` to `INSERT INTO images ('', image_name, image_path) VALUES (?, ?)` and remove the bind `bind_param("ss", $image_name, $image_path)` if that column is an AI. or something similar. I think you get the gist of it. Or assign something to it `$image_id = '123';` — Funk Forty Niner, Dec 31 '15 at 14:52
@Fred-ii- Thank you so much for all your help! I took away the image_id and at least now there is something arriving in my database! :) However, that is something very very weird! The image pth looks like this: `imgs/impressionen/image.jpg./imgs/impressionen` it is supposed to be `./imgs/impressionen/image.jpg`. ' — Schwesi, Dec 31 '15 at 15:15
you're welcome. now that answer got another upvote without even doing anything for it. sorry, but I've put in enough time already that led to more success than what that guy has done. all the best, I gotta run. my espresso machine's ready. *ciao!* e buon anno!! — Funk Forty Niner, Dec 31 '15 at 15:17

SuperDJ · Answer 1 · 2015-12-31T14:21:43.377

2

Instead of:

// prepare and bind
        $stmt = $conn->prepare("INSERT INTO images (image_id, image_name, image_path) VALUES (?, ?, ?)");
        $stmt->bind_param("iss", $image_id, $image_name, $image_path);

        // set parameters and execute
        $image_id = '';
        $image_name = $target_file;
        $image_path = $target_dir;
        $stmt->execute();

Try:

// set parameters and execute
$image_id = '';
$image_name = $target_file;
$image_path = $target_dir;

// prepare and bind
$stmt = $conn->prepare("INSERT INTO images (image_id, image_name, image_path) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $image_id, $image_name, $image_path);

The order of code execution is from top to bottom. Right now you call variables while you haven't set them.

edited Dec 31 '15 at 14:21

answered Dec 31 '15 at 14:20

SuperDJ

7,488
11
40
74

2

which [what I said here...](http://stackoverflow.com/questions/34547611/insert-into-database-mysqli-prepared-statement#comment56836926_34547611) translates to that. [You're just saying "Try".](http://stackoverflow.com/revisions/34547688/1) Edit: [you edited.](http://stackoverflow.com/posts/34547688/revisions) – Funk Forty Niner Dec 31 '15 at 14:21
@SuperDJ Thank you for your answer! Now, at least I get an error message, I can work on (Call to a member function bind_param() on a non-object). – Schwesi Dec 31 '15 at 14:25

insert into database mysqli prepared statement

1 Answers1