SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How to avoid executing mysql queries in nested loop?

    Hi,

    I'm having a bit of a problem with a task I was assigned.

    I have this old phpmynewsletter database (MySQL backend).

    Basically all emails are saved in a table with this structure:

    email varchar(255)
    list_id int(11)
    hash varchar(255)
    disclaimer1 tinyint(1)
    disclaimer2 tinyint(1)
    gender char(1)
    dob date
    region varchar(50)

    So one same email can be present several times in the table, this is, subscribed in different newsletters (identified by list_id)

    What I need to do is update the records so the fields disclaimer1, disclaimer2, gender, dob and region for a specific list_id are the same for the same email registered with other list_id's.

    For example:


    email: mjprpub(at)gmail.com
    list_id: 1
    disclaimer1: 0
    disclaimer2: 0
    gender: M
    dob: 1975-05-02
    region: Portugal

    and

    email: mjprpub(at)gmail.com
    list_id: 33
    disclaimer1: 1
    disclaimer2: 0
    gender: M
    dob: 1975-05-02
    region: Portugal

    How do I update the record with list_id 33 with the correct values from record with list_id 1?

    I'm using PHP and I believe that running updates queries in loops is bad practise. The table has around 610000 records.

    Hope somebody can give me some insight on how to accomplish this task.

    Thanks,
    Mário
    Yours truely
    Mário Ramos

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do you know it is values in listid 1 that are correct and not the values in listid 33? what happens if there are different values for list 23 and 55 for the same user? what record do you use then?

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi guelphdad!

    Quote Originally Posted by guelphdad View Post
    how do you know it is values in listid 1 that are correct and not the values in listid 33? what happens if there are different values for list 23 and 55 for the same user? what record do you use then?
    List_id 1 is the oldest.
    I'll just have to consider it the correct one and use it's values to update the others.
    Yours truely
    Mário Ramos


Tags for this Thread

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
  •