Solved

import csv plus form fields

Posted on 2014-01-07
11
307 Views
Last Modified: 2014-01-09
I can import a csv file with no problem.  But how do I add the values in form fields to each record? The added the two fields to the end of table if that helps.

<?php
    require_once('config.php');
    if (!empty($_POST["addData"]) )
    {
        $sql = "LOAD DATA LOCAL INFILE 'dealerdata.csv' INTO TABLE `dealerdata`
          FIELDS TERMINATED  BY ',' ENCLOSED BY '\"'
          LINES TERMINATED BY '\r\n'
          IGNORE 1 LINES"; 
        if (!$res = mysql_query($sql)) die( mysql_error() );
    }
?>
    <form method="post" id ="loadData" name="loadData" action="loadCSV.php" >
        Region <input name="region" id = "region" />
        Data for period: (mm/yyyy) <input name="period" id = "period" />
        <input type="submit" value="Import Data" name="addData" id="addData" />
    </form>

Open in new window

0
Comment
Question by:KCTechNet
  • 6
  • 4
11 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39764181
Can you please show us the input data set and the output you want to get?  Thanks, ~Ray
0
 

Author Comment

by:KCTechNet
ID: 39764207
there are 47 fields in the mySQL table and in each line of the csv file.

The code I posted successfully populates the 47 fields with the contents of the CSV file.

 I then added two fields to the end of the mySQL table.  One field is called Region and the other Period.  When I run the existing code, these fields are null for each record.  For each line that is appended from the csv file, I want to add the values that are in the Region and Period form fields.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39765540
Do you want to add the same information to every row of the data base table?  In other words, when you do this import you want all of the imported lines to contain the same data for Region and Period?
0
 

Author Comment

by:KCTechNet
ID: 39767088
Yes.  I know that is repetitive.  I could create a "header" record, but I would still need a way to append the header ID to the cvs data.
0
 

Author Comment

by:KCTechNet
ID: 39767181
I also know I can import, then update the null fields after the fact.  This would be ok for now because it will be a single-user process.  However, I figure if there is a way to do this I should learn it in case this turns into  multi-user in the future.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39767957
OK, there are probably several ways to solve this and most of them would suggest redesigning the scripts.  Here is the narrative of one way that would not require a gross redesign.  Before you read the rest of this post, be sure you understand the nature of the client/server realtionship so that you have a good understanding of the request and the response.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

When the form is submitted, it creates an HTTP request to the URL in the action= attribute of the form tag (this is called the action script).  The PHP action script on the server gets the request, including the data from the HTML form with "region" and "period."

The action script can run the load-data-infile to populate the MySQL table.  Once that is complete, the action script can run a query to update the rows of the table.  The SQL UPDATE query will update every row of the table if you do not tell it a condition, like WHERE or LIMIT.  So the query will look something like this...

$r = mysql_real_escape_string($_POST['region']);
$p = mysql_real_escape_string($_POST['period']);
$q = "UPDATE dealerdata SET region = '$r', period = '$p'";
MySQL_Query($q) or trigger_error(mysql_error(), E_USER_ERROR);

Open in new window

Now for some important news.  PHP is eliminating MySQL, so you have a conversion facing you.  This article explains why, and what you must do to keep your scripts running.   In my experience, the easiest conversion is to MySQLi using the object-oriented notation.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 33

Accepted Solution

by:
Slick812 earned 500 total points
ID: 39768727
greetings KCTechNet, according to this page about using LOAD DATA INFILE -
    http://docs.oracle.com/cd/E17952_01/refman-5.6-en/load-data.html

you may can use the  SET operator to have values placed into specific columns -


$region = mysql_real_escape_string($_POST['region']);
// you should VERIFY these $_POST['region'] values to be sure they are NOT empty, and contain the correct length and characters
$period = mysql_real_escape_string($_POST['period']); // I assume these are String values, not integer

$sql = "LOAD DATA LOCAL INFILE 'dealerdata.csv' INTO TABLE `dealerdata`
          FIELDS TERMINATED  BY ',' ENCLOSED BY '\"'
          LINES TERMINATED BY '\r\n'
          IGNORE 1 LINES
          SET Region = '$region', Period = '$period' ";

I am not sure if the older versions of MySQL support this? Sorry I could not test this to see if it works?
0
 

Author Comment

by:KCTechNet
ID: 39768738
After I posted the question I did the work-around you mentioned which would update all the records without a region.  As I mentioned in my previous post, this works in a single-user environment, but the minute this goes multi-users it will stop working because one user could have added one region while another adds a second set of data for another region.  They both could exist at the same time but the update statement wouldn't know which ones are for the which region/period.
0
 

Author Comment

by:KCTechNet
ID: 39768751
Slick812, I will test the "Set" operator this evening.  Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39769128
You might consider marking the region column NOT NULL DEFAULT '' then you could use a WHERE clause in the UPDATE query with something like this:

WHERE region = ''
0
 

Author Comment

by:KCTechNet
ID: 39770121
SET Region = '$region', Period = '$period' "     --  worked like a charm.  Thanks

Ray, I was doing it the way you mentioned, however, if two people were to import at the same time, for two different regions/periods, whoever got to the update code first would get all the records, not just theirs.

thanks Slick
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

914 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

18 Experts available now in Live!

Get 1:1 Help Now