Solved

Validate Contents of Column in file based on another column

Posted on 2014-09-09
13
127 Views
Last Modified: 2014-09-19
Hi,

I am trying to validate two things before I upload a csv file in my database table. This I want to achieve with PHP and MYSQL
1. that the content in the file is of csv format
2. I want to validate that the contents of two particular columns is as per the contents of my existing tables in the database.

I know the first point is clear but for second point you are a bit confused. Let me explain:

There are two existing tables in my database for example table_1 and table_2. The file that I am uploading will be imported into a third table table_3.
table_1	
id	name
1	name_1
2	name_2
3	name_3
4	name_4
5	name_5

Open in new window

table_2	
id	person
1	person_1
2	person_2
3	person_3
4	person_4
5	person_5

Open in new window

table_3						
id	number	name	location	person		date
1	12345	name_1	loc_1		person_1	09-09-2014
2	23456	name_2	loc_1		person_1	10-09-2014
3	34567	name_1	loc_1		person_4	11-09-2014
4	45678	name_5	loc_1		person_5	12-09-2014
5	56789	name_3	loc_1		person_5	13-09-2014

Open in new window


Before I import the table_3 data using a csv file into database. I want to validate that the entries in the name and person columns be exactly the same way that they are in the table_1 and table_2 respectively. The name and person columns in table_3 should not have an entry which is not present in the table_1 and table_2 respectively. OTHER COLUMNS CAN HAVE ANY DATA DOESN'T MATTER

I hope I have cleared myself. If any more inputs required then do let me know.
0
Comment
Question by:Vipin Kumar
  • 7
  • 3
  • 3
13 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40312805
1. You cannot really validate that the entire file is in a CSV format. For all you know, a file could contain a corrupted CSV file, where some of the beginning data is good but it all goes wrong about halfway through.

So you need to validate on a row-by-row basis in the file. You can use fgetcsv() on a file handle to try and extract a record, and you can check to see if it returned a valid row.

2. You should not check the database to see if the fields from the file are in the database. That leads to a huge security problem (someone could put in an unexpected field and start unexpected injecting data into your database). Instead, you should be hardcoding what fields YOU EXPECT, and then loop through the file to see if it has records with data in those fields. YOU should be defining the rules, not the file's contents.
0
 
LVL 1

Author Comment

by:Vipin Kumar
ID: 40312814
Can u provide an example or a link where the first and second point are achieved using php
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40312838
I can't really give you the full solution. You can Google for examples of fgetcsv to get an idea of how that works, but a full solution just isn't in the scope of this question. You need to be able to write the code yourself, or else you're not going to be able to understand important parts of the solution.

If you're only looking for a tool to import CSVs into the database, there are several of those. I think HeidiSQL is a free Windows application that can do this for you.
0
 
LVL 1

Author Comment

by:Vipin Kumar
ID: 40312964
i really want to implement the second point... how should i approach towards that... any example or link from where i can write my own code... i just need an idea to achieve the second point
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40313169
I tend to agree with gr8gonzo; this may not be a feasible design pattern.  But if you can give us a little more to go on, we may be able to help.

1. Please post the CREATE TABLE statements for the tables in question
2. Please post the data that is expected to be in the tables when the application starts
3. Please post the data that is expected to be read from CSV files
4. Please show us the final state of the tables after the application has run.

Armed with a clear picture of what you want to achieve we may be able to show you the code, or suggest an alternative design that would work well.
0
 
LVL 1

Author Comment

by:Vipin Kumar
ID: 40313518
@Ray Paseur,

The answers to your questions are below:
1. The table_1 and table_2 are pre-populated tables, they have a column which is id INT  and other columns is name and person respectively, both are VARCHAR. Similarly for table_3 the id and number columns are INT and the other columns are VARCHAR.

2. I have already shown the data that will be present in the table_1 and table_2 when application starts. Initially table_3 will be empty and data will be imported into it via csv files every week.

3. The data I have show for table_3 above will be present in the csv file and will be imported into table_3, but before import I want to create a check/validation that the data in the columns of name and person in the csv file contains the entries that are present in name and person columns of table_1 and table_2 respectively i.e. they should not contain a different content like name_6 or person_6 etc. If those columns in csv file contain even a single content which is not present in table_1 and table_2 it should return a error and not import the csv file contents into table_3

4. After the application runs the table_1 and table_2 are like that only, but the csv file contents are imported into table_3 post content  validation mentioned in above (3).
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40314626
Thanks for the description.  I would like to try to show you how to write the programming, but it's too much unpaid work for me to create the entire application from your description.  Please refer back to this comment.  If you can create the data requested and you can post it here, I'll be glad to try to move the ball forward.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28514758.html#a40313169
0
 
LVL 1

Author Comment

by:Vipin Kumar
ID: 40317209
@Ray Paseur,

Can you tell me is there a way to check that the contents of xyz column in table_1 are present in the xyz column of table_2 using php or mysql.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40317639
Yes, there is.  And as soon as you show me the information I asked for, I'll give you a code sample that shows how to do it.  Bear in mind that data is not in tables and columns; it is in tables, columns and rows.  The MySQL WHERE clause can help you find the data.
0
 
LVL 1

Accepted Solution

by:
Vipin Kumar earned 0 total points
ID: 40322337
I have been able to create the code to validate the contents of specific columns in the csv file with the columns from table_1 and table_2. To validate I first imported the csv file into a temporary table, then from there I called the columns which needed to be validated in to separate arrays. Then I called the columns from table_1 and table_2 in arrays and then finally I compared the values of arrays using the below condition:

count($tempcol1)==count(array_intersect($tempcol1, $table_1col)

Open in new window

0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40323250
That's unfortunate that you believe you found the solution on your own. I guess I won't be participating in your questions in the future.
0
 
LVL 1

Author Comment

by:Vipin Kumar
ID: 40323323
@gr8gonzo, you yourself told that you need to  write the code and google for the same. If I have to google then why ask anyone here. Atleast I would have got any direction from your end, I would have definitely have rewarded the points. Rest is your wish..
0
 
LVL 1

Author Closing Comment

by:Vipin Kumar
ID: 40332193
Before anyone could provide a solution I found mine and then I am sharing with everyone so that they can use it if required
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

760 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

26 Experts available now in Live!

Get 1:1 Help Now