asked on

MySQL CSV Data Import - Clear down Variables

I'm using the attached php to import data into mysql db. I need to include within the php an unset command to clear the variable as currently when the browser is refreshed it reruns the mysql queries and re-inserts data from the previous submission.

<?php include("connection_1.php"); 


    if(isset($_POST['submit']))
        
    {
        // purge existing table data
        $query = "DELETE from holding_ebay";
        mysqli_query($link, $query);
            
        $file = $_FILES['file']['tmp_name'];
        
        $handle = fopen($file,"r");
        // fgets = misses the header row.
        fgets($handle);
        
        while(($fileop = fgetcsv($handle,1000,",")) !== false)
        
            {
            
            $sales_record_number = $fileop[0];
            $user_id = $fileop[1];
            $buyer_email = $fileop[3];
            $buyer_name = $fileop[2];
            $add_1 = $fileop[4];
            $add_2 = $fileop[5];
            $city = $fileop[6];
            $state = $fileop[7];
            $post_code = $fileop[8];
            $country = $fileop[9];
            $item_number = $fileop[11];
            $item_title = $fileop[13];
            $custom_label = $fileop[32];
            $qty = $fileop[14];
            $sale_price = $fileop[15];
            $pp = $fileop[17];
            $insurance = $fileop[18];
            $total_price = $fileop[19];
            $payment_method = $fileop[20];
            $postage_service = $fileop[28];
            $sale_date = $fileop[24];
            $despatch_date = $fileop[27];
            $newpost = $fileop[8];
                
            $query = "INSERT into holding_ebay 
                                        
                                        (    sales_record_number
                                            ,user_id
                                            ,buyer_email
                                            ,buyer_name
                                            ,add_1
                                            ,add_2
                                            ,city
                                            ,state
                                            ,post_code
                                            ,country
                                            ,item_number
                                            ,item_title
                                            ,custom_label
                                            ,qty
                                            ,sale_price
                                            ,pp
                                            ,insurance
                                            ,total_price
                                            ,payment_method
                                            ,postage_service
                                            ,sale_date
                                            ,despatch_date
                                            ,newpost) 
                                            
                                            VALUES
                                            
                                        (   '$sales_record_number'
                                            ,'$user_id'
                                            ,'$buyer_email'
                                            ,'$buyer_name'
                                            ,'$add_1'
                                            ,'$add_2'
                                            ,'$city'
                                            ,'$state'
                                            ,'$post_code'
                                            ,'$country'
                                            ,'$item_number'
                                            ,'$item_title'
                                            ,'$custom_label'
                                            ,'$qty'
                                            ,'$sale_price'
                                            ,'$pp'
                                            ,'$insurance'
                                            ,'$total_price'
                                            ,'$payment_method'
                                            ,'$postage_service'
                                            ,'$sale_date'
                                            ,'$despatch_date'
                                            ,'$newpost')";
            mysqli_query($link, $query);
                        
            }
            unset($fileop);
            
                
            if (mysqli_query($link, $query))    
            
                    {
                        echo "Query passed through..";
                    }
                        else {
                                echo "Query failed with error : " . mysqli_error($link);
                             }   
    }
    
?>

Open in new window

I though I could use

unset($fileop)

Open in new window

to reset the variable if placed outside the while loop but that isn't working.

Please help?

J

Ray Paseur

There is a general design that will prevent accidental re-submit of identical data. Here's an example.

<?php // demo/prevent_multi_submit.php
/**
 * Prevent repeated data submissions due to browser refresh, resubmit,
 * or browser back-button.
 *
 * GET-method requests must be idempotent and nullipotent; GET must not
 * disrupt the data model.  POST (PUT) requests can change the data model,
 * but for client convenience, good design will make POST, PUT, and DELETE
 * requests modifiable or reversible.
 *
 * This function can test either $_GET or $_POST request variables.
 *
 *    if ( multi_submit() )
 *    {
 *       // handle duplicate inputs
 *    }
 *    else
 *    {
 *       // handle original inputs
 *    }
 */
error_reporting(E_ALL);


// A FUNCTION TO RETURN TRUE OR FALSE ABOUT MULTI-SUBMIT CONDITIONS
function multi_submit($type="POST")
{
    // MAKE THE FUNCTION WORK FOR EITHER GET OR POST SUBMITS
    $input_array = (strtoupper(trim($type)) == "GET") ? $_GET : $_POST;

    // GATHER THE CONTENTS OF THE SUBMITTED FIELDS AND MAKE A MESSAGE DIGEST
    $string = 'X';
    foreach ($input_array as $val)
    {
        $string .= $val;
    }
    $string = md5($string);

    // IF THE SESSION VARIABLE IS EMPTY THIS IS NOT A MULTI-SUBMIT
    if (empty($_SESSION["multi_submit"]))
    {
        $_SESSION['multi_submit'] = $string;
        return FALSE;
    }

    // IF THE SESSION DATA MATCHES THE MESSAGE DIGEST THIS IS A MULTI-SUBMIT
    if ($_SESSION['multi_submit'] == $string)
    {
        return TRUE;
    }

    // IF THE SESSION DATA DOES NOT MATCH THIS IS NOT A MULTI-SUBMIT
    else
    {
        $_SESSION['multi_submit'] = $string;
        return FALSE;
    }
}


// ALWAYS START THE PHP SESSION AT THE LOGICAL TOP OF EVERY PAGE
session_start();


// SHOW HOW TO USE THE FUNCTION
if (!empty($_POST))
{
    if (multi_submit())
    {
        echo "ALREADY GOT THAT";
    }
}


// CREATE THE FORM FOR THE DEMONSTRATION
$form = <<<FORM
<form method="post">
ENTER SOMETHING, THEN REENTER IT
<input name="mydata" />
<input type="submit" />
</form>
FORM;

echo $form;

Open in new window

Although you didn't ask about it, you might want to rethink the design strategy that has a script reading from $_FILES. This is a security hole. A better strategy would be to use move_uploaded_file() to copy the uploaded file(s) from the temporary server storage to your own storage. This will help ensure that you're actually using an uploaded file, and not something that is bogus.

Ridgejp

ASKER

Thanks Ray - will review this info now. Was my initial thoughts about unsetting the array inappropriate at this point?
J

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ridgejp

ASKER

Thanks again Ray.
J