jameskook jameskook - 4 years ago 134
MySQL Question

Putting MYSQL results into PHP array

I got a problem with the retrieval of data in a mysql database into a php array. If I execute the code below, the query fetches some corresponding rows twice or even 3 times.
I got a database of files listed with the name and the date of the upload.
Let's say I have 4 files (This was tested):
File1, File2, File3, File4.
The code would echo (if it helps):

File1 and its date
File1 and its date
File4 and its date
File2 and its date
File2 and its date
File3 and its date
File3 and its date
File4 and its date
File4 and its date


Here is the code:

$query = $pdo_con->query("SELECT * FROM uploads WHERE active = 1 and owner = '$current_user' Order By upload_id DESC");

while($r = $query->fetch(PDO::FETCH_ASSOC)) {
$new_array[] = $r;
$new_array[$r['name']] = $r;
$new_array[$r['uploaded']] = $r;
}
foreach($new_array as $array){
echo $array['name'];
echo $array['uploaded'];
}


I would like to have the code echo each file only once and in the correct order (descending order of upload_id(auto incrementing))

Answer Source

The way you are creating your array is giving you 3 elements each time through the loop. Try:

$i;
while($r = $query->fetch(PDO::FETCH_ASSOC)) {
    $new_array[$i][$r['name']] = $r['name'];
    $new_array[$i][$r['uploaded']] = $r['uploaded'];
    $i++;
}

However, if you specify the columns in your query it's much easier:

SELECT name, uploaded FROM uploads
    WHERE  active = 1 AND owner = '$current_user'
    ORDER BY upload_id DESC

Then:

$new_array = $query->fetchAll(PDO::FETCH_ASSOC);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download