Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

import csv plus form fields

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
KCTechNet
Asked:
KCTechNet
  • 6
  • 4
1 Solution
 
Ray PaseurCommented:
Can you please show us the input data set and the output you want to get?  Thanks, ~Ray
0
 
KCTechNetAuthor Commented:
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
 
Ray PaseurCommented:
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
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!

 
KCTechNetAuthor Commented:
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
 
KCTechNetAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Slick812Commented:
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
 
KCTechNetAuthor Commented:
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
 
KCTechNetAuthor Commented:
Slick812, I will test the "Set" operator this evening.  Thanks.
0
 
Ray PaseurCommented:
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
 
KCTechNetAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now