Solved

import csv plus form fields

Posted on 2014-01-07
11
304 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
Comment Utility
Can you please show us the input data set and the output you want to get?  Thanks, ~Ray
0
 

Author Comment

by:KCTechNet
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Slick812, I will test the "Set" operator this evening.  Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 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

12 Experts available now in Live!

Get 1:1 Help Now