1

I'm having trouble passing the value of a variable to a new record in MySQL database. I have created a form that the user fills out with basic information. Then they will upload an image. The PHP code renames the image with the next id to be assigned and concatenates the file type to create the new file name. I assign the new name to a variable $image with

$image = mysqli_insert_id($con) . "." . $imageFileType;

I can echo the variable and verify that is works fine - example 431.jpg would be the file name. Also, the image gets uploaded to the server with the correct renaming convention. Now I need to pass that value to the database when the form is submitted. It would be passed to the field name image in the database. So I have along with the other variables

$id = $_POST['id'];
$firstname = $_POST['firstname'];
$department = $_POST['department'];
$email = $_POST['email'];
$image = $_POST['image'];

I am trying to pass the value in the form using a hidden field that looks like this:

<input name="image" type="hidden" value="<?php echo $image; ?>" />

All the other data gets passed into the database except for the image name. I have tried many variations - even tried to use $_POST['$image'] for the value. Again, I can echo the value when the form is submitted so the value exists - I just can't pass it into the database record. I can get it to work if I enter the data in the form field manually. I have made the field type VARCHAR and TEXT in phpMyAdmin just to try different things.

If any can help that would be great.

Below is the $sqlinsert statement:

$sqlinsert = "INSERT INTO `test_2015`.`test_table` ( `id` , `firstname`, `department`,  `email`, `image` ) 
VALUES ( '$id',  '$firstname', '$department',  '$email', '$image' )";

BELOW IS THE PHP:

    <?php

if (isset($_POST['submitted'])){
include('connect.php');
// VARIABLES
    $id = $_POST['id'];
    $firstname = $_POST['firstname'];
    $department = $_POST['department'];
    $email = $_POST['email'];
    $image = $_POST['image'];
    $sqlinsert = "INSERT INTO `test_2015`.`test_table` ( `id` , `firstname`, `department`, `email`, `image` ) 
    VALUES ( '$id',  '$firstname', '$department', '$email', '$image' )";

    //NESTED IF STATEMENT
    // RUN THE QUERY
if ( !mysqli_query($con, $sqlinsert) )
{
    die('error inserting new record');

} // END OF NESTED IF STATEMENT

// START IMAGE UPLOAD HERE ******************************************

$target_dir = "../images/";
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$uploadOk = 1;
$imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);


    $check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
    if($check !== false) {
        echo "File is an image - " . $check["mime"] . ".";
        $uploadOk = 1;
    } else {
        echo "File is not an image.";
        $uploadOk = 0;
    }
//}  REMOVE THIS IF ISSET END

// Check if file already exists
if (file_exists($target_file)) {
    echo "Sorry, file already exists.   ";
    $uploadOk = 0;
}
// Allow certain file formats
if($imageFileType != "jpg" && $imageFileType != "png" && $imageFileType != "jpeg" && $imageFileType != "gif" ) {
    echo "Sorry, only JPG, JPEG, PNG & GIF files are allowed.";
    $uploadOk = 0;
}
// Check if $uploadOk is set to 0 by an error
if ($uploadOk == 0) {
    echo "<div style ='font:16px Arial,tahoma,sans-serif;color:#ff0000;font-weight:bold'>File was not uploaded.</div>";


// if everything is ok, try to upload file
} else {

        $imageName = mysqli_insert_id($con) . "." . $imageFileType;
        if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $imageName ))

    {

// CHANGE FILE NAME TO CURRENT ID
// USING mysqli_insert_id($con) TO GET ID AND CONCATENATE FILE TYPE
        echo "New IMAGE file name is : ", $imageName;

// PASS NAME FOR IMAGE TO $image HERE       
        $image = $imageName;
        echo "image =  : ", $image;
        echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
        echo "<br>";
// GET THE ASSIGNED ID USING: mysqli_insert_id()
        echo "New Record ID is : "  . mysqli_insert_id($con);

    } else {
        echo "<div style ='font:16px Arial,tahoma,sans-serif;color:#ff0000;font-weight:bold'>
        Sorry, there was an error uploading your file.</div>";
        echo "<br>";

    }
}
    // END IMAGE UPLOAD HERE   ******************************************
    $newrecord = "1 record added to the database";
    echo "<br>";
} // END MAIN IF STATEMENT
// Close connection
mysqli_close($con);
?>

BELOW IS MY FORM:

    <form method="post" action="add_record.php" enctype="multipart/form-data">
<input type="hidden" name="submitted" value="true" />
<fieldset>
    <legend>New Record</legend>
    <label><input type="hidden" name="id" /></label>
    <label>First Name : <input type="text" name="firstname" required="required" /></label><br /><br />
    <label>Department : <input type="text" name="department" required="required" /></label><br /><br />
    <label>Email Address : <input type="text" name="email" required="required" /></label><br /><br />
    <label>Image Name: <input name="image" type="hidden" value="<?php echo $image; ?>" /></label>
</fieldset>
<br />
Select image to upload:
<input type="file" name="fileToUpload" id="fileToUpload">
<input type="submit" value="add new record button"  />
</form>

RESULTS FROM var_dump($POST);

array(6) { ["submitted"]=> string(4) "true" ["id"]=> string(0) "" ["firstname"]=> string(10) "first_name" ["department"]=> string(10) "department" ["email"]=> string(5) "email" ["image"]=> string(0) "" }

13
  • 1
    If your file name doesn't start with a number and you are not using an AUTO_INCREMENTing PRIMARY KEY this won't work anyways. Commented Jun 9, 2015 at 22:59
  • I should have mentioned that I am using auto increment for the ID field which is set to primary key. All that works great - the image uploads with the file name that is the ID and appended with the file type. I just can't get that file name passed into the field in the newly created row/record. Commented Jun 9, 2015 at 23:05
  • 1
    Can you post your mysql insert statement?, Edit the question and include it Commented Jun 9, 2015 at 23:08
  • 1
    apart from the fact that you're wide open to sql injection it should work fine. I suspect that you're simply not populating the hidden input with any value before posting the form, how is this handled? do you have some javascript? Normally you'd get the file name from the $_FILES array, not quite sure what your hidden input is for Commented Jun 9, 2015 at 23:16
  • I'm no PHP expert - this is about as complex as I have tried so far. I used the hidden input only from samples I found on this site. I also tried applying mysqli_real_escape_string() but received error messages when the form was submitted. If I can echo the new filename shouldn't I be able to pass it to the database? I even tried a separate isset but same issue - no value passed. Commented Jun 9, 2015 at 23:23

2 Answers 2

1

The problem is when you run the insert query $image is just an empty string (derived from an empty hidden input). I kind of understand why its there, something to do with validation failure, not that it helps much as the file would need to be re-selected if that were the case.

Add this:

$image = mysqli_real_escape_string ($con , $image);
$queryStr = "update `test_2015`.`test_table` set `image`= '$image' where `id`=" . mysqli_insert_id($con);
mysqli_query($con, $queryStr);

After:

 $image = $imageName;

(about 20 lines up from the bottom of your script)

Sign up to request clarification or add additional context in comments.

1 Comment

Added the suggested code and it works! I had pulled most of my hair out on this part. Now I understand that I needed to go back and update the record. Checked everything and it's working out great for different image file formats. THANKS.
0

Since your primary goal is to save that file name generated in image field consider the following php code:

    <?php

if (isset($_POST['submitted'])){
include('connect.php');
// VARIABLES
    $id = $_POST['id'];
    $firstname = $_POST['firstname'];
    $department = $_POST['department'];
    $email = $_POST['email'];
    $image = $_POST['image'];
    $sqlinsert = "INSERT INTO `test_2015`.`test_table` ( `id` , `firstname`, `department`, `email`, `image` ) 
    VALUES ( '$id',  '$firstname', '$department', '$email', '$image' )";

    //NESTED IF STATEMENT
    // RUN THE QUERY
if ( !mysqli_query($con, $sqlinsert) )
{
    die('error inserting new record');

} // END OF NESTED IF STATEMENT

// START IMAGE UPLOAD HERE ******************************************

$target_dir = "../images/";
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$uploadOk = 1;
$imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);


    $check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
    if($check !== false) {
        echo "File is an image - " . $check["mime"] . ".";
        $uploadOk = 1;
    } else {
        echo "File is not an image.";
        $uploadOk = 0;
    }
//}  REMOVE THIS IF ISSET END

// Check if file already exists
if (file_exists($target_file)) {
    echo "Sorry, file already exists.   ";
    $uploadOk = 0;
}
// Allow certain file formats
if($imageFileType != "jpg" && $imageFileType != "png" && $imageFileType != "jpeg" && $imageFileType != "gif" ) {
    echo "Sorry, only JPG, JPEG, PNG & GIF files are allowed.";
    $uploadOk = 0;
}
// Check if $uploadOk is set to 0 by an error
if ($uploadOk == 0) {
    echo "<div style ='font:16px Arial,tahoma,sans-serif;color:#ff0000;font-weight:bold'>File was not uploaded.</div>";


// if everything is ok, try to upload file
} else {

        $imageName = mysqli_insert_id($con) . "." . $imageFileType;
        if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $imageName ))

    {

// CHANGE FILE NAME TO CURRENT ID
// USING mysqli_insert_id($con) TO GET ID AND CONCATENATE FILE TYPE
        echo "New IMAGE file name is : ", $imageName;

// PASS NAME FOR IMAGE TO $image HERE       
        $image = $imageName;
        echo "image =  : ", $image;
        echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
        echo "<br>";
// GET THE ASSIGNED ID USING: mysqli_insert_id()
        echo "New Record ID is : "  . mysqli_insert_id($con);

// SAVE New IMAGE file name

        $sqlupdate = "UPDATE `test_2015`.`test_table` SET `image` = '$image' WHERE id = " . mysqli_insert_id($con);

        if ( !mysqli_query($con, $sqlupdate) )
        {
            die('error updating new record');
        }

    } else {
        echo "<div style ='font:16px Arial,tahoma,sans-serif;color:#ff0000;font-weight:bold'>
        Sorry, there was an error uploading your file.</div>";
        echo "<br>";

    }
}
    // END IMAGE UPLOAD HERE   ******************************************
    $newrecord = "1 record added to the database";
    echo "<br>";
} // END MAIN IF STATEMENT
// Close connection
mysqli_close($con);
?>

The code that I added are the following:

// SAVE New IMAGE file name

$sqlupdate = "UPDATE `test_2015`.`test_table` SET `image` = '$image' WHERE id = " . mysqli_insert_id($con);

if ( !mysqli_query($con, $sqlupdate) )
{
    die('error updating new record');
}

The idea here is to update your newly inserted record when you've successfully generated the new file name of your image. You can't save the image file name when inserting the new record since id is not yet generated so your only choice is to update it.

I don't think you need that hidden input named 'image' in your form if your only goal is to save the newly generated image file name unless you have other use for it.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.