Validate Contents of Column in file based on another column
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.
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.
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_5table_2
id person
1 person_1
2 person_2
3 person_3
4 person_4
5 person_5table_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-2014Before 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.
ASKER
Can u provide an example or a link where the first and second point are achieved using php
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.
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.
ASKER
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
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.
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.
ASKER
@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).
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).
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.
https://www.experts-exchange.com/questions/28514758/Validate-Contents-of-Column-in-file-based-on-another-column.html?anchorAnswerId=40313169#a40313169
https://www.experts-exchange.com/questions/28514758/Validate-Contents-of-Column-in-file-based-on-another-column.html?anchorAnswerId=40313169#a40313169
ASKER
@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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
@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..
ASKER
Before anyone could provide a solution I found mine and then I am sharing with everyone so that they can use it if required
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.