Breaking 2 CSVs containing some identical data and insert into mysql database.

Posted on 2006-04-22
Last Modified: 2011-04-14
My PHP is a little broken at the moment, i'm still learning and not having much luck with this problem.

I have 2 files both CSV files containing data.

One contains data in this format
PLU1, Cola ,53, 25

The other one contains data in this format
Pepsi, Cola, PLU1

I want to end up with
PLU, Pepsi, Cola, 53, 25

I was thinking (Pseudo Code)

Load each line into an array of file 1
Load each line into an array of file 2

Compare array_file1[1] && array_file1[2] with array_file2[3] && array_file2[3] if they match, write the SQL Query to file, when complete write the file to data.sql ready to load into SQL when required.

I assume i would just write "array_file1[1], array_file2[1], array_file2[2], array_file1[3], array_file1[4] \n" to the file.

The problem i am having is looping the data to check ALL the files against ALL the others, and then Any that dont match having them put out separately, so i end up with 5 part CSV and 2 left overs files, of data that didnt match.

I must apologise that i dont have kind of working code, as i have no idea how to start this.

Any idea's ?

Question by:Cenobitez
    1 Comment
    LVL 19

    Accepted Solution

    I think directly working with the SQL will give you far less of a headache:

    Set up an SQL table (1) to house CSV file 1.
    Set up an SQL table (2) to house CSV file 2.
    Set up an SQL table (3) to house your combined data.
    PHP script to import CSV file 1 to SQL table 1.
    PHP script to import CSV file 2 to SQL table 2.
    INSERT INTO table3 SELECT table1.field1, table2.field1, table1.field2, table1.field3, table1.field4 FROM table1, table2 WHERE table1.field1 = table2.field3

    Note that I have assumed a typo in your combined dataset:

    >I want to end up with
    >PLU, Pepsi, Cola, 53, 25
    PLU1, Pepsi, Cola, 52, 25
    ^ my assumption

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    As this topic comes over and over again in different forms, I've finally decided to write a short (yea, right...) article / tutorial about pagination with PHP with MySQL database. There are dozens of these kind of tutorials, I know - I wanted to mak…
    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    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 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 …

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now