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.

    if (!empty($_POST["addData"]) )
        $sql = "LOAD DATA LOCAL INFILE 'dealerdata.csv' INTO TABLE `dealerdata`
          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" />

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Can you please show us the input data set and the output you want to get?  Thanks, ~Ray
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.
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?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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.
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.
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.

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.
greetings KCTechNet, according to this page about using LOAD DATA INFILE -

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`
          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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
KCTechNetAuthor Commented:
Slick812, I will test the "Set" operator this evening.  Thanks.
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 = ''
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.