Insert into one Table, while updating another & File Upload

Question

I am trying to update data in one table, and at the same time to insert a new row into a second table from a PHP form.

When hitting submit I get an error message saying:

Uncaught Error: Call to undefined method mysqli::exec()

Below is my query on the PHP submit form:

    $link->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

try {
    $link->beginTransaction();

    $q1 = $link->prepare("INSERT INTO 
                            liquor_licenses_2
                            (   username
                            ,   outlet_number
                            ,   license_date
                            ,   license
                            ,   scanned_license
                            ,   permit_renewal
                            ,   scanned_permit_renewal
                            ,   identity_document
                            ,   scanned_identity_document
                            ) 
                            VALUES 
                            (   :username
                            ,   :outlet_number
                            ,   :license_date
                            ,   :license
                            ,   :scanned_license
                            ,   :permit_renewal
                            ,   :scanned_permit_renewal
                            ,   :identity_document
                            ,   :scanned_identity_document
                            ");
    $q1->bindValue(':username', $username);
    $q1->bindValue(':outlet_number', $outlet_number);
    $q1->bindValue(':license_date', $license_date);
    $q1->bindValue(':liquor_license', $license);
    $q1->bindValue(':scanned_license', $scanned_license);
    $q1->bindValue(':permit_renewal', $permit_renewal);
    $q1->bindValue(':scanned_permit_renewal', $scanned_permit_renewal);
    $q1->bindValue(':identity_document', $identity_document);
    $q1->bindValue(':scanned_identity_document', $scanned_identity_document);
    $q1->execute();

    $q2 = $link->prepare("UPDATE 
                            outlet_details
                                SET 
                                username = :username
                            ,   outlet_number = :outlet_number                          
                            ,   mega_region = :mega_region 
                            ,   outlet_name = :outlet_name 
                            ,   address_0 = :address_0 
                            ,   address_1 = :address_1 
                            ,   address_2 = :address_2 
                            ,   address_3 = :address_3 
                            ,   address_4 = :address_4 
                            ,   contact_number_1 = :contact_number_1 
                            ,   contact_number_2 = :contact_number_2 
                        WHERE 
                            outlet_number = :outlet_number");
    $q1->bindValue(':username', $username);
    $q1->bindValue(':outlet_number', $outlet_number);                           
    $q2->bindValue(':mega_region', $mega_region);
    $q2->bindValue(':outlet_name', $outlet_name);
    $q2->bindValue(':address_0', $address_0);
    $q2->bindValue(':address_1', $address_1);
    $q2->bindValue(':address_2', $address_2);
    $q2->bindValue(':address_3', $address_3);
    $q2->bindValue(':address_4', $address_4);
    $q2->bindValue(':contact_number_1', $contact_number_1);
    $q2->bindValue(':contact_number_2', $contact_number_2);
    $q2->execute();


        $link->commit();
    } catch (Exception $e) {
        $link->rollback();
    }




    if(mysqli_query($link, $q1)){
        echo "Records added / updated successfully.";
    } else{
        echo "ERROR: Could not able to execute $q1. " . mysqli_error($link);
    }

    header("refresh:2;url=../outlet_capture.php"); 
    // close connection
    mysqli_close($link);
    ?>

I also want to add to this the uploading of 3 files (scanned license, scanned_permit_renewal, scanned_identity_document). I have found the below code to handle this, but I am not sure how to implement it.

foreach($_FILES['file'] AS $key=>$file) {
    $filename = $file['tmp_name'];
    $size = $file['size'];
    $newfile = $_SERVER['DOCUMENT_ROOT'] . "/uploads/" . date("Ymd_his") . "_" . $filename;
    move_uploaded_file($filename, $newfile);
}

I need the file name to be stored in my DB so that I can then reference it to the file that has been uploaded to the Server.

Devi Veeramalla · Accepted Answer · 2017-01-16T11:46:16.227

1

       $link->set_charset("utf8"); 
    try {
         $link->begin_transaction();  
        $q1 = $link->prepare("INSERT INTO liquor_licenses_2
                                (   username
                                ,   outlet_number
                                ,   license_date
                                ,   license
                                ,   scanned_license
                                ,   permit_renewal
                                ,   scanned_permit_renewal
                                ,   identity_document
                                ,   scanned_identity_document
                                ) 
                                VALUES 
                                (   ?,?,?,?,?,?,?,?,?)");
                                                   --^--(you missed closed bracket here)
        $q1->bind_param("sdsssssss", $username, $outlet_number, $license_date,$license,$scanned_license,$permit_renewal,$scanned_permit_renewal,$identity_document,$scanned_identity_document);
        $res1=$q1->execute();
        $q2 = $link->prepare("UPDATE 
                                outlet_details
                                    SET 
                                    username = ?
                                ,   outlet_number = ?                        
                                ,   mega_region = ?
                                ,   outlet_name = ?
                                ,   address_0 = ?
                                ,   address_1 = ?
                                ,   address_2 = ?
                                ,   address_3 = ?
                                ,   address_4 = ?
                                ,   contact_number_1 = ?
                                ,   contact_number_2 = ?
                            WHERE 
                                outlet_number = ?");
    $q2->bind_param("sdsssssssssd", $username, $outlet_number, $mega_region,outlet_name,$address_0,$address_1,$address_2,$address_3,$address_4,$contact_number_1,$contact_number_1,$outlet_number);

    $q2->execute();

            $link->commit();
        } catch (Exception $e) {
            $link->rollback();
        }

        if($res1){
            echo "Records added / updated successfully.";
        } else{
            echo "ERROR: Could not able to execute $q1. " . mysqli_error($link);
        }

        header("refresh:2;url=../outlet_capture.php"); 
        // close connection
       $link->close();
        ?>

edited Jan 16 '17 at 11:46

answered Jan 16 '17 at 11:33

Devi Veeramalla

406
3
7

Thanks for the above. I updated the code as per your example and am now getting a new error message: Catchable fatal error: Object of class mysqli_stmt could not be converted to string in. The error is referring to this line: echo "ERROR: Could not able to execute $q1. " . mysqli_error($link); – Leon Claassen Jan 16 '17 at 11:51
MySQLi, mainly as I am not at all familiar with PDO. From my understanding though, is that you can't run multiple queries on MySQLi the way I am trying to. – Leon Claassen Jan 16 '17 at 13:10
PDO uses bindValue, whereas mysqli uses bind_param : http://php.net/manual/en/mysqli-stmt.bind-param.php – Devi Veeramalla Jan 16 '17 at 13:37
Check whether you are passing correct data type in "sdsssssssssd" i.e., i - integer,d - double,s - string, b - BLOB – Devi Veeramalla Jan 16 '17 at 13:37
All fields are strings as you had them listed. The only ones I did try changing but still got the same error was on the outlet_number which is an integer, so basically I replaced "d" with "i" and also tried them as a "s". – Leon Claassen Jan 16 '17 at 13:39
It appears that the second query is executing by updating the table, but the first query is not inserting into the required table. Could this be that I have a mod_timestamp column in my table which is set to auto update? – Leon Claassen Jan 16 '17 at 14:02
Check all variables i.e., $username, $outlet_number, $license_date... return null or an empty string? If it is an empty string it works fine.But, if it is null then it should work as long as the fields are allowed to be null in the database. Check your table scheme and make sure the fields are allowed to be null. – Devi Veeramalla Jan 16 '17 at 14:09
Ok, silly mistake from my side which resolved my error. I had two columns which I was not using set to "NOT NULL" and there is no form data for these columns hence the issue. My only issue now is the attachment upload. Notice: Undefined index: file in \submit\outlet_details.php on line 109 Warning: Invalid argument supplied for foreach() in \submit\outlet_details.php on line 109 – Leon Claassen Jan 16 '17 at 14:14
I updated my answer, please check it. if nothing is uploaded then the first assignment will throw a warning. – Devi Veeramalla Jan 16 '17 at 14:26
Awesome thanks... So no errors were thrown out, but also no file uploaded. If it's ok with you, perhaps I can open another string which refers to only the image upload part, and close this one off. I can then place a link to the image issue from here. – Leon Claassen Jan 16 '17 at 14:30
I think the only problem is upload path. check whether you have upload directory and print $_SERVER['DOCUMENT_ROOT'] . "/uploads/" . date("Ymd_his") . "_" . $file_name; in brower to check what path you are using. – Devi Veeramalla Jan 16 '17 at 15:43
I used the below to get my root path and created an uploads directory in the root folder, and no files are being uploaded to this directory. Also there are no other error messages. Everything else is working. What I am going to do is close this thread as the rest is solved and start a new one only looking at the upload issue. Could be that I have my code sequencing or something wrong. $filePath = realpath(dirname(__FILE__)); $rootPath = realpath($_SERVER['DOCUMENT_ROOT']); $htmlPath = str_replace($root, '', $filePath); echo $htmlPath; – Leon Claassen Jan 17 '17 at 07:28
New thread: http://stackoverflow.com/questions/41691657/uploading-multiple-files-to-server-from-form – Leon Claassen Jan 17 '17 at 07:52

Devi Veeramalla · Answer 2 · 2017-01-16T14:24:30.160

0

Try this code for multiple file uploading:

if (isset($_FILES["file"]["name"])) {
foreach($_FILES['file']['tmp_name'] as $key => $tmp_name)
{
    $file_name = $key.$_FILES['file']['name'][$key];
    $file_size =$_FILES['file']['size'][$key];
    $file_tmp =$_FILES['file']['tmp_name'][$key];
    $file_type=$_FILES['file']['type'][$key];  
    $new_file = $_SERVER['DOCUMENT_ROOT'] . "/uploads/" . date("Ymd_his") . "_" . $file_name;
    //echo $new_file;
    move_uploaded_file($file_tmp,$new_file);
}
}

edited Jan 16 '17 at 14:24

answered Jan 16 '17 at 12:12

Devi Veeramalla

406
3
7

Do I put this code in the HTML file or would this also go into the PHP "Submit" file? And where about to I put it? – Leon Claassen Jan 16 '17 at 13:11
If you want to upload files after submitting the form, then it also goes in PHP submit. – Devi Veeramalla Jan 16 '17 at 13:23

Insert into one Table, while updating another & File Upload

2 Answers2

Linked