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
– Your Common Sense, Commented Nov 22, 2020 at 9:24

medi code · Accepted Answer · 2020-11-22 07:43:37Z

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, 2020 at 7:43

medi code

265 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Miger Over a year ago

thank you I'll try this out as soon as possible and accept your answer afterwards.

Miger Over a year ago

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 Over a year ago

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..

Collectives™ on Stack Overflow

Getting multiple results without query inside loop

Edit

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related