Mysql array values repeated in while loop

Question

I have a query where output from unique option getting repeated in while loop belp query with mysql dump

CREATE TABLE `txt` (
      `option` varchar(10) NOT NULL,
      `username` varchar(100) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `txt` VALUES ('a', 'ashok');
INSERT INTO `txt` VALUES ('a', 'sachin');
INSERT INTO `txt` VALUES ('b', 'parvez');
query:
$result = mysql_query("SELECT option, name from txt");

while ($row = mysql_fetch_array($result)) {
 $option = $row['option'];
 $name   = $row['username'];
 $array[$option] = $name;
}

print_r($array);

Actual output:

Array ( [a] => Array ( [name] => Array ( [0] => ashok [1] => sachin ) 
Array ( [b] => Array ( [name] => Array ( [0] => ashok [1] => sachin [2] => parvez)

Expected output:

Array ( [a] => Array ( [name] => Array ( [0] => ashok [1] => sachin )
Array ( [b] => Array ( [name] => Array ( [0] => parvez)

Please tell me what mistake I have made in this query

Answer 1

Your code does not produce the result you claim it does under "actual" at all. You keep overwriting $array[$option] in the loop while what you want to do is add the value to the array.

Nevertheless, following code will produce the result you name under "expected":

$result = mysql_query("SELECT `option`, `username` from `txt`");
while ($row = mysql_fetch_array($result)) {
    $option = $row['option'];
    $name   = $row['username'];
    $array[$option]['name'][] = $name;
}

Result:

Array
(
    [a] => Array
        (
            [name] => Array
                (
                    [0] => ashok
                    [1] => sachin
                )
        )
    [b] => Array
        (
            [name] => Array
                (
                    [0] => parvez
                )
        )
)