Results 1 to 6 of 6

Thread: loops in MySQL

  1. #1
    Join Date
    May 2004
    Posts
    28

    Unanswered: loops in MySQL

    Is there any looping commands in MySQL. I need to go through a bunch of rows and check certain fields against all other existing fields in the table, and if a duplicate field exists, I will then need to change that field based on other data in the table. Something like a "for" loop or a "do while" loop would be nice.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, no such thing

    use a flat file if you want to loop

    databases are for set-at-a-time processing, not record-at-a-time

    if you describe your requirements a little bit, we might be able to help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    28
    Ok, well here is a small script that I am running that pulls basic data from a table of students and outputs each students data to a txt file in a specific format that can then be imported into some web portal software called blackboard:

    Code:
    SELECT DISTINCT concat_ws(       
    	 '',
    	 '"',
    	 (	 
    	    concat_ws(            
     	      '","',
    	      concat_ws('',LEFT(first_name,1),last_name),
    	      last_name,
    	      first_name,
    	      concat_ws('',LEFT(first_name,1),last_name,'@','christianheritage.edu'),
    	      concat_ws('',LOWER(LEFT(first_name,1)),LOWER(LEFT(last_name,1)),student_id),
    	      student_id,
    	      middle_name
                )
             ),
             '"'
           )
    FROM chc_students INTO OUTFILE "/blackboard_create_users.txt";
    The outputed txt file contains lines that look like this:

    Code:
    "AAnderson","Anderson","Alexia","AAnderson@christianheritage.edu","aa113732","113732","C"
    "DBlair","Blair","Daniel","DBlair@christianheritage.edu","db113734","113734","K"
    "JWest","West","Jonathan","JWest@christianheritage.edu","jw113717","113717","G"
    "KGoodhart","Goodhart","Karen","KGoodhart@christianheritage.edu","kg113710","113710","M"
    "MSaechao","Saechao","May","MSaechao@christianheritage.edu","ms113715","113715","C"
    "DWatson","Watson","Debra","DWatson@christianheritage.edu","dw113672","113672","L"
    Each line contains information for creating an account in the web portal software. The first field is the student's username (first initial + lastname). Since there are hundreds of students, there will most definitely be duplicate usernames, which poses a problem. So I need a method to check each record against all other records, and if a duplicate exists, alter the current record's username with something like a middle initial as well or a one at the end of the user's name.

    I have programmed C before and understand how I could do it there, but I am relatively new to SQL. Thanks for the help,

    Brian


    Web Administrator
    Shadow Mountain Ministries

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I use PERL (via DBD/DBI) to this type of work.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add an ORDER BY clause to your SELECT, and post-process the output file

    alternatively, if you want to assign unique userids in your chc_students table, there's another SELECT you can run to tell you which ones will have duplicates, if you decide you want to maintain the uniqueness in that table from now on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2002
    Location
    Nottingham, UK
    Posts
    37

    Question how do you recommend

    I'm having a similar issue.

    I have been using PHP with a browser to select all records, modify/format some varchar data, and insert the formatted data into a different date field, one record at a time. (i'm basically repairing a poorly planned database so that it is usable) It is really slow to update thousands of records with concatenated information this way.

    How do you recommend I "post-process the output file"?

    I dont have control over whats installed on the server which hosts my mysql database. I just ssh'd in to check processes with the "top" command, and mysql is peaking the processor out at 98-99 percent, during my php UPDATE query process, one for each row in my resultset.

    Thanks,

    G
    ----
    system:
    Mac Powerbook 1GHz

Posting Permissions

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