PHP dependant foreach loop

Question

What I am trying to accomplish here is to separate a firstname, lastname combo to store in a database. I already added the the new columns in the database to hold the first and last names. What I need to do now is to actually separate them and run an SQL update against the changes.

Can someone please give me a hand? Thanks.

Here is my code

<?php
$link = mysql_connect('localhost', 'root', '');
mysql_select_db("test",$link);

$sql = "SELECT * FROM new_users";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result))
{
    $foo[] = $row;
}

foreach($foo as $name)
{
    $_name[] = explode(',',$name['Name']);
    $_pan[] = $name['PANumber'];
}

foreach($_pan as $pan)
{
    foreach($_name as $name)
    {
        echo'<pre>';
        print_r($pan);
        echo'</pre>';
    }
}

Answer 1

If I'm understanding your question, you've got a Name column which is "first,last" and you want to replace it with First and Last columns.

SQL's string manipulation is probably easiest:

UPDATE new_users SET First=SUBSTRING_INDEX(Name, ",", 1), Last=SUBSTRING_INDEX(Name, ",", -1)

... then, after you're sure it did the right thing:

ALTER TABLE new_users DROP Name

Answer 2

Something like this aught to work (I assume PANumber is your primary key here?):

foreach($foo as $name)
{
    $_name[] = explode(',',$name['Name']);
    $_pan = $name['PANumber'];

    $sql = "UPDATE new_users SET " . 
           "FirstName = '" . $_name[0] . "' " .
           "LastName = '" . $_name[1] . "' " .
           "WHERE PANumber = '" . $_pan;

    mysql_query($sql);
}