Solved

MySQL CSV Data Import - Clear down Variables

Posted on 2016-08-27
4
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 110

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 110

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Node.js 11 80
clicking to find my folders on wamp 8 46
Why isn't my DIV and Form centering? 1 28
Posting and receiving data with JSON in PHP 12 36
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

734 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