[SOLVED] Explode function

[Walker33]

I'm trying to clean up a DB and separate full name entries into firstname/lastname fields, and I believe I should use explode, but I can't seem to get it right, no matter what I've tried.

 

Table is customers, full name field is Name, names are currently inserted

first, space, last.  New fields I want them in are FirstName, LastName.

 

Any help on this would be greatly appreciated.  Thanks in advance.

 

[Ken2k7]

Could you post the code you have? I don't see why you don't just have a column for first name and a column for last name. But anyways, explode would work. Can you post the code you have thus far?

[Maq]

Like Ken mentioned, we need to see some of your code.  But here is a pseudo version:

 

You would have to go through each record and do an Insert for each one, which may take a while depending on the size of your DB.

 

// Query
$pieces = explode(" ", $row['FullName']);
SET FirstName = '$pieces[0]'
SET LastName = '$pieces[1]'

[PFMaBiSmAd]

You could use the mysql LOCATE() function to get the position of the space, then use the mysql LEFT() and RIGHT() functions to get the first and last name into an UPDATE query.

 

Untested but should work (edit: using a single query to update all records in the table) -

 

UPDATE customers SET FirstName = LEFT(Name,LOCATE(' ',Name)), LastName = RIGHT(Name,LOCATE(' ',Name))

[ignace]

SET LastName = '$pieces[1]'

 

would be not sufficient what if the last name consist out of more then one word? So i would suggest something similar like:

 

<?php
$firstname = array_shift($pieces);
$lastname = implode(" ", $pieces);
SET LastName = '$lastname'
?>

 

Or try the option provided by PFMaBiSmAd

[premiso]

would be not sufficient what if the last name consist out of more then one word? So i would suggest something similar like:

 

It is all based on guesses. The OP failed to provide any code or extra information that may help him get the correct solution to his problem.

 

Frankly the fact that the last name could contain a space, is null. As they never stated it could be a possibility, thus I would not worry about it. As the OP needs to learn how to ask a proper question and show relevant code to get their code working like they would want.

[Walker33]

I apologize in advance, but I'm very new to PHP.  Dumped code last night when frustrated, but I was along the lines of this:

 

<?php

 

$query = mysql_query ("SELECT * FROM Failures");

$result = mysql_fetch_assoc ($query);

$parts = explode(" ", $result['Name']);

SET FirstName = '$parts[0]';

SET LastName = '$parts[1]';

 

echo "confused and frustrated";

}

?>

 

Not even getting the echo output.  And I'm not sure once it's separated how to Insert it into proper new fields, but some of these suggestions are already helping.

[Maq]

Not even getting the echo output

 

Because your code is invalid, looks like you just CnP'd my code from above...

 

Anyway, we need to know the exact format of the names.  Like premiso mentioned, are the names always going to be [first, space, last], is there ever a middle initial, are there spaces allowed in the first/last name?

 

That's the most important question.

[premiso]

Maq's code was more for entering it into a database, hence the psuedo mention.

 

<?php

$query = mysql_query ("SELECT * FROM Failures");
$result = mysql_fetch_assoc ($query);
$parts = explode(" ", $result['Name']);
echo 'First Name: '. $parts[0] . '<br />';
echo 'Last Name: ' . $parts[1];

?>

 

Should work.

[Ken2k7]

If you only need the Name column, you should only select that one column. It's more efficient that way. Be careful in calling $parts[1]. I'm going to assume that every entry in the Name column has a first and last name (at the very least) and they are separated by a single space.

[Walker33]

Yes, every name is always first, space, last, no middle initial, no hyphens.  And yes, CnP'd that, as I dumped what I had, but it was along those same lines.  Finally, first time in a forum, first time posting questions, so very new to asking questions.  Thanks for bearing with me.  Just trying to learn.

[Maq]

You should test PFMaBiSmAd's UPDATE query first as it will be A LOT faster then selecting all the rows, exploding them, and inserting in 2 different columns.

[PFMaBiSmAd]

Test it to make sure you don't need to add/subtract 1 to get the right starting positions... You can simply execute that using your favorite database management tool, no need to write any php code.

 

I thought the table name was customers, it is apparently Failures and the query would need to be adjusted to match.

[Walker33]

Beautiful!  Yes, I tested the update function (failures is my test table, customers is the one I'm actually wanting to change once I get it right), and it did what I wanted!  "Jennifer Barber" inserted it as Jennifer in FirstName and er Barber in LastName, so I do think I need to subtract.  Where do I put that?

[PFMaBiSmAd]

Make sure it did not put the space with the first or the last name in the new fields.

[Walker33]

what it did exactly was put "Jennifer" in FirstName and "er Barber" in LastName column.  So it's grabbing the additional 'er',space and putting it into LastName.

[PFMaBiSmAd]

The RIGHT() function needs to use the LENGTH() function as well. Again, untested but should be closer than first post -

 

UPDATE customers SET FirstName = LEFT(Name,LOCATE(' ',Name)), LastName = RIGHT(Name,LENGTH(Name) - LOCATE(' ',Name))

[Walker33]

Absolutely beautiful!  Worked like a champ!  Thanks especially to PFMaBiSmAd, but thankyou to everyone who offered some advice.  You've saved us an unbelievable amount of work, and it is GREATLY appreciated!!!