How to put sql query result into an array? [closed]

Question

I want to put a sql query result into an array. I tried the code below but it shows the 1st record for $count times. Obviously it's something wrong at the "$dept[$i]= $row['name'];". But i have no idea how to fix it. Somebody help please?

$sql="SELECT name FROM system_dept ORDER BY id";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
$count=mysql_num_rows($result);
if (!mysql_query($sql,$con))
{
    die('Error: ' . mysql_error());
}
else
{
    $dept = array();
    $i=0;

    for($i=0;$i<$count;$i++)
    {
        $dept[$i]= $row['name'];
        echo $dept[$i];
    }
}

Ok, i tried to use mysqli but it doesnt work. the web server shows that: MySQL client version: 4.1.22 PHP extension: mysql Can mysqli works in mysql php extension?

Answer 1

Why are you only fetching one row, when you actually want all rows?

With mysqli:

$db = new mysqli("localhost", "foo_dbo", "pass", "foo_db");    
$result = $db->query("SELECT name FROM system_dept ORDER BY id");    
$names = $result->fetch_all(MYSQLI_ASSOC);

With PDO:

$db = new PDO('mysql:dbname=foo_db;dbhost=localhost', 'foo_dbo', 'pass');
$stmt = $db->query("SELECT name FROM system_dept ORDER BY id");
$names = $stmt->fetchAll(PDO::FETCH_ASSOC);

This leads to the same result as the other answers but without the loop! It's faster! If you fetch row by row, you're slower but depending on what you do, you'll need less memory. If you're storing each row in an array, this will not be the case. So fetching all is the way to go.