SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Washing two database tables with MYSQL and PHP

    Hi All,

    I need a bit of help with something I am trying to do.
    I am building a 'macro' type solution so that a txt file and a csv file can be compared for duplicate numbers.

    What I have done so far is create two upload functions that load the data from the files to their own tables in a database.
    So, one table for the txt file and one for the csv file.

    What I need to do now is 'wash' the tables so that any duplicates are removed, leaving a set of numbers that can be exported to an excel file for the user.

    I'm thinking that I need to do a select query for the first table and run the results, individually, through a loop. In that loop I do a second select query for the other table and compare the two. Whatever is a duplicated gets deleted from both tables and whatever isn't is saved for the export.

    Is this the right method?

    Is anyone able to help me get started on it at all, or 'point me in the right direction'?!

    Thanks

    Em

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what you describe can be accomplished with a FULL OUTER JOIN, but unfortunately, if you're using mysql (as so many php applications do) then it's just a bit more complicated

    what exactly do you mean by duplicates? how many columns are you comparing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response.

    I'm just comparing the one column of data.

    Basically, I have two files of number ranges that need to have duplicated numbers removed.
    I was also thinking that something similar to the below might work:

    SELECT * FROM textDoc WHERE serviceNumberRange IN (SELECT * FROM excelDoc)????

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    and when you say "duplicates removed" do you mean from both sides? or keep one of them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will need to remove both instances of the number.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    SELECT n FROM table1 WHERE n NOT IN ( SELECT n FROM table2 )
    UNION ALL
    SELECT n FROM table2 WHERE n NOT IN ( SELECT n FROM table1 )

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •