?
Solved

MySQL CSV Data Import - Clear down Variables

Posted on 2016-08-27
4
Medium Priority
?
63 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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 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 …
Suggested Courses

771 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