Getting multiple results without query inside loop

Question

I'm trying to improve the loading times of images and need to change around the code.

I haven't had any luck finding out how to do this and I'm not sure if it is even possible. In the example below you can see that I use KEY to match it with U_KEY to get FILE_PATH which I then add to a long comma delimited $allPaths string.

I know I should not use queries inside loops but I have no idea how to change this.

<?php
$sql = "SELECT * FROM test_users, image_uploads LIMIT 0, 27";
$result = mysqli_query($mysqli, $sql);

while($value = mysqli_fetch_array($result)) {
    $files_key = $value["KEY"];
    $allPaths = "";
    
    $inner_query = "SELECT * FROM additional_uploads WHERE U_KEY = '".$files_key;
    $inner_result = mysqli_query($mysqli, $inner_query);
    
    while ($row = mysqli_fetch_array($inner_result)) {
        $allpaths .= $row['FILE_PATH'].",";
    }

    // how do I get $allPaths without using a query inside the while loop?
}
?>

If someone could tell me how I can get $allPaths with only one query instead of multiple queries inside the loop as shown above it would probably load the images much faster.

Is this possible?

Edit

I have tried to understand the problem using the suggested answer and additionally I was looking on other forums as well to find out more. However I still cannot find a solution. Since I'm using mysqli_fetch_array the suggested answer really confuses me even further.

A simple SQL query using JOIN, GROUP BY and GROUP_CONCAT will do the job. But first you need to get rid of `test_users, image_uploads` select which makes no sense. — Your Common Sense, Nov 22 '20 at 09:24

score 0 · Accepted Answer · answered Nov 22 '20 at 07:43

0

<?php
$inner_query = "SELECT * FROM additional_uploads";
$inner_result = mysqli_query($mysqli, $inner_query);
$rows = [];

while ($row = mysqli_fetch_array($inner_result)) {
    $rows[$row['U_KEY']] .= $row['FILE_PATH'].",";
}

$sql = "SELECT * FROM test_users, image_uploads LIMIT 0, 27";
$result = mysqli_query($mysqli, $sql);

while($value = mysqli_fetch_array($result)) {
    $files_key = $value["KEY"];
    $allPaths = $rows[$files_key];
}
?>

answered Nov 22 '20 at 07:43

medi code

26
5

thank you I'll try this out as soon as possible and accept your answer afterwards. – Miger Nov 25 '20 at 12:45
Not yet, please be patient this is part of a big project and there are other high priority tasks I have to work on before I'm able to get back to this issue. However your solution seems to be exactly what I had in mind and I'll accept your answer as soon as possible. Thanks for your help :) – Miger Nov 28 '20 at 16:51
I have now accepted your answer. However even with the improvements the loading times were not reduced. Which leads me to believe that the bottle neck is somewhere else.. – Miger Dec 01 '20 at 09:12

Getting multiple results without query inside loop

Edit

1 Answers1