LOAD DATA help (I need if/elseif/else syntax)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

LOAD DATA help (I need if/elseif/else syntax)

Post by Luke »

I have this query I am working on to import data from a text file into my mysql database. This is what I have so far:

Code: Select all

LOAD DATA LOCAL INFILE 'C:\\htdocs\\members.txt'
  REPLACE
  INTO TABLE `users`
  FIELDS
        TERMINATED BY '\t'
  (@id, username, password, @passwordHint, first_name, last_name, middle_name, nick_name, spouse_name, @gender, @phone, @phone_b, @phone_c, @phone_type_a, @phone_type_b, @phone_type_c, @fax_a, @fax_b, @fax_type_a, @fax_type_b, @email, @email_b, @email_type_a, @email_type_b, @pref_contact, @date_join, @date_born, @date_resign, @address_a, @address_b, @city_a, @city_b, @zip_a, @zip_b, @state_a, @state_b, @address_type_a, @address_type_b, @image_ad, @image_name, business_website, @website_b, business_name, @business_classification, business_keywords, business_description, badge_number, @badge_plate, @full_perc, @badge_color, @team_id, @team_admin, @team_captain, @bio)
  SET
	gender = IF(@gender = 1, 'm', 'f')
Now I need a statement sort of like this (nested ternary):

Code: Select all

home_phone = IF(@phone_type_a = 'Home', @phone_type_a, IF(@phone_tybe_b = 'Home', @phone_type_b, IF(@phone_type_c = 'Home', @phone_type_c, null)))
I tried this but it does not work. Is there a way to do what I want?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

looks like it was due to a syntax error (forgot a comma)

Code: Select all

LOAD DATA LOCAL INFILE 'C:\\htdocs\\members.txt'
  REPLACE
  INTO TABLE `users`
  FIELDS
        TERMINATED BY '\t'
  (@id, username, password, @passwordHint, first_name, last_name, middle_name, nick_name, spouse_name, @gender, @phone, @phone_b, @phone_c, @phone_type_a, @phone_type_b, @phone_type_c, @fax_a, @fax_b, @fax_type_a, @fax_type_b, @email, @email_b, @email_type_a, @email_type_b, @pref_contact, @date_join, @date_born, @date_resign, @address_a, @address_b, @city_a, @city_b, @zip_a, @zip_b, @state_a, @state_b, @address_type_a, @address_type_b, @image_ad, @image_name, business_website, @website_b, business_name, @business_classification, business_keywords, business_description, badge_number, @badge_plate, @full_perc, @badge_color, @team_id, @team_admin, @team_captain, @bio)
  SET
	gender = IF(@gender = 1, 'm', 'f'),
        home_phone = IF(@phone_type_a = 'Home', @phone,
			IF(@phone_tybe_b = 'Home', @phone_b, 
				IF(@phone_type_c = 'Home', @phone_c, null)
			)
		     )
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Alright, a related question:
how would I remove all dashes/spaces/anything except numbers from the phone number? I looked in the string functions for mysql, but nothing jumps out at me except regexp and I don't really know how I would use that.

EDIT: Another one:
How would I turn a date formatted like mm/dd/yyyy to yyyy-mm-dd?
User avatar
mpeacock
Forum Newbie
Posts: 10
Joined: Thu Apr 12, 2007 9:07 am
Location: Mobile AL

Post by mpeacock »

The Ninja Space Goat wrote:Alright, a related question:
how would I remove all dashes/spaces/anything except numbers from the phone number? I looked in the string functions for mysql, but nothing jumps out at me except regexp and I don't really know how I would use that.

EDIT: Another one:
How would I turn a date formatted like mm/dd/yyyy to yyyy-mm-dd?
You probably have a couple options. First, I'm not sure if you could do this during the load, but it would be trivial to either preprocess the input file before the load or load the data as is, and postprocess the various fields in your database. Of course, you may be stuck preprocessing since your load might not work if the date format is whacked.

The main benefit of preprocessing is that it would keep your loader script as simple as possible.

Something like this might work for you:

Code: Select all

$in = fopen('raw.data', 'r') or die($php_errormsg);
$out = fopen('cleaned.data', 'w') or die($php_errormsg);

while (! feof($in) ) 
{
    if ($line = fgets($in, 4096))
    {
       $fields = preg_split('\t', $line);
       // now process each field as needed
       $outFields = convert($fileds);
       fwrite($out, implode('\t', $outFields) . "\n");

    }


}

fclose($in);
fclose($out);

function convert($someArray)
{
   
// Fix the elements you need fixed
  while (list($foo, $bar, $baz, $theDate) = each($someArray))
 {
   $theDate = convertDate($theDate);
   ...
 }

 return $someArray;


}


function convertDate($date)
{
   return date("Y-m-d H:i:s", strtotime($date) );
}
Cheers
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Use the mysql date functions to help manage dates. As for handling the phone number, you could use the REPLACE() function.

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
http://dev.mysql.com/doc/refman/5.1/en/ ... on_replace
Post Reply