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
Solved

import csv plus form fields

Posted on 2014-01-07
11
311 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 109

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 109

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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
 
LVL 109

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 34

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 109

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

829 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