Avoiding a query inside a loop in my situation

Question

I have quite a complicated situation here. I can't find a better way to solve this without putting a SELECT query inside a loop that rolls over 70000 times when I enter that page (don't worry, I use array_chunk to split the array into pages). I guess this would be a resource killer if I use a query here. Because of this, here I am, asking a question.

I have this big array I need to loop on:

$images = scandir($imgit_root_path . '/' . IMAGES_PATH);

$indexhtm = array_search('index.htm', $images);
unset($images[0], $images[1], $images[$indexhtm]);

Now I have an array with all file names of the files (images) in my IMAGES_PATH. Now the problem comes here:

Some of these images are registered on the database, because registered users have their images listed on my database. Now I need to retrieve the user_id based on the image name that the array above gives me.

Inside a loop I simply did this:

foreach ($images as $image_name)
{
    $query = $db->prepare('SELECT user_id FROM imgit_images WHERE image_name = :name');
    $query->bindValue(':name', $image_name, PDO::PARAM_STR);
    $query->execute();
    $row = $query->fetch(PDO::FETCH_ASSOC);
    $user_id = $row['user_id'];

    echo $user_id;
}

This works just fine, but the efficiency equals to 0. Using that user_id I plan on getting other stuff from the imgit_users table, such as username, which would require another query inside that loop.

This is too much and I need a simpler way to deal with this.

Is there a way to get those user_ids before going inside the loop and use them IN the loop?

This is the table structure from imgit_images:

While this is the schema for imgit_users:

Answer 1

Something like this would work (I'm not sure if it's possible to prepare the WHERE IN query since the # of values is unknown... Else, make sure you sanatize $images):

$image_names = "'".implode("', '", $images)."'";
$query = $db->prepare("SELECT img.user_id, image_name, username 
                         FROM imgit_images img
                         INNER JOIN imgit_users u ON u.user_id = img.user_id 
                         WHERE image_name IN(".$image_names.")");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
    echo $row['user_id']."'s image is ".$row['image_name'];
}

You might need to tweak it a little (haven't tested it), but you seem to be able to, so I'm not worried!

Answer 2

Can you not just use an INNER JOIN in your query, this way each iteration of the loop will return details of the corresponding user with it. Change your query to something like (i'm making assumptions as to the structure of your tables here):

SELECT imgit_users.user_id 
,imgit_users.username
,imgit_users.other_column_and_so_on
FROM imgit_images 
INNER JOIN imgit_users ON imgit_users.user_id = imgit_images.user_id
WHERE imgit_images.image_name = :name

This obviously doesn't avoid the need for a loop (you could probably use string concatenation to build up the IN part of your where clause, but you'd probably use a join here anyway) but it would return the user's information on each iteration and prevent the need for further iterations to get the user's info.

Answer 3

Not sure if it is going to help, but I see a couple of optimizations that may be possible:

1. Prepare the query outside the loop, and rebound/execute/get result within the loop. If query preparation is expensive, you may be saving quite a bit of time.

2. You can pass an array as in Passing an array to a query using a WHERE clause and obtain the image and user id, that way you may be able to fragment your query into a smaller number of queries.

Answer 4

PDO makes writing your query securely a cinch.

$placeholders = implode(',', array_fill(0, count($images), '?'));
$sql = "SELECT u.username 
        FROM imgit_images i
        INNER JOIN imgit_users u ON i.user_id = u.id
        WHERE i.image_name IN ({$placeholders})";
$stmt = $db->prepare($sql);
$stmt->execute($images);

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // use $row['username']
}

Create a string of comma separated ?s and write them into IN's parentheses. Then pass the array of images to execute(). Easily done, and now all of your desired data is available in a single resultset from a single query. (Add additional columns to your query's SELECT clause as needed.)