Solved

MySQL CSV Data Import - Clear down Variables

Posted on 2016-08-27
4
31 Views
Last Modified: 2016-08-28
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
0
Comment
Question by:Ridgejp
  • 2
  • 2
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41773034
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.
0
 

Author Comment

by:Ridgejp
ID: 41773639
Thanks Ray - will review this info now. Was my initial thoughts about unsetting the array inappropriate at this point?
J
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41773650
Here's the issue as I see it.  First a little background.  

When a client posts information to your import script, the client makes an HTTP request.  The request contains all the information that the server needs to run the import script, including the URL of the script, the POST and FILES data, any cookies, etc.  When the server receives the request, it runs the script using the request data (this is usually instantaneous), then it disconnects and waits for the next request.  As I understand it, you only want to run the script once for each HTTP request.

If a client hits the browser "back" button or resubmits the form, this can result in a separate, identical request being sent to the server.  Since the first request has already been run and completed, nothing you change in the code will affect only the second request -- unless you somehow know that this is a second request.  That's where the message digest algorithm comes into play.  Using the PHP session we can know, from one request to the next, exactly what was submitted the first time.  We can be pretty sure that identical requests are redundant.

In the code example above, $fileop  is an array that is created by reading a row from the CSV file.   Each row creates a new set of content in $fileop.  Then $fileop  is copied into other variables and these other variables are used to create the SQL query.  So two factors are in play here.  First, the order of instructions makes $fileop  into an unnecessary item after the variables have been copied from the array.  Unsetting it would have no effect on the SQL query.  Second, the HTTP request sequence is probably what would be causing any duplicate database updates.  So it looks like what we want to do is avoid processing an identical duplicate request.

Why not just use a random form token?  Because the form token will only be generated once when the HTML form is requested.  If I submit the form and the server stores the form token, I cannot submit the form again without requesting the form again from the server.  I other words, if a client used the back button, changed the form, and resubmitted the new data, the client browser would still have the old form token, and the form token strategy would cause a legitimate request to fail.  The client would be forced to request the HTML form again, and start over, filling in all the original data, as well as the desired changes.  That's kind of an anti-pattern in UX!  This sort of thing would be painful if you wanted to (for example) register several family members on a long form.  If the only difference was the first name, why would you make the client retype the last name, address, city, state, etc?  The message digest strategy is better than the form token in almost every instance.
0
 

Author Closing Comment

by:Ridgejp
ID: 41773670
Thanks again Ray.
J
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now