prepared statement in the loop

Question

I have a problem that i cant figure out for too long. Basically I have table with usernames (1 unique username in the table) with 1 respective image file. Some of users have image file and some dont. usernames are stored in the array $Users[]. I am trying to fetch filenames of images for each user using:

$stmt = $db->prepare("SELECT file_name FROM images WHERE BINARY username=?"); 
    for($temp1=0; $temp1<count($Users); $temp1++){
        $stmt->bind_param("s", $Users[$temp1]);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($ImgFileName);
        $stmt->fetch();
        $imageURL[$temp1]=$ImgFileName;
    }

however this has annoying behavior. Say as code loops through, if user User[0] has $ImgFileName related to it, but User[1], User[2] etc doesnt, then $ImgFileName is used of the last user with available image, instead of null. This happens until some user in the loop again has image filename in the table. So if i print $imageURL[] array after loops goes through, it looks smth like:

$Users[]=[user1,user2,user3,user4,user5]
$imageURL[]=[img001.png,img001.png,img001.png,img231.png,img124.png,img124.png]

instead of

$Users[]=[user1,user2,user3,user4,user5]
$imageURL[]=[img001.png,,,img231.png,img124.png,]

anyone knows why?

I'm pretty sure it would be 10x easier to just do it with PDO. Any reason why you are doing this with mysqli? — Dharman, Dec 21 '21 at 20:10
am actually just learning php, javascript by doing some project and havent really used pdo . but will have a look. thank you — luca amarelli, Dec 21 '21 at 20:18
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection — Dharman, Dec 21 '21 at 20:18
Thanks. Is one way of another better from sql injection point of view? Or maybe any other safety features? — luca amarelli, Dec 21 '21 at 20:34
No, security-wise they are the same. Both offer prepared statements, PDO has just more features which makes coding much easier. — Dharman, Dec 21 '21 at 20:36

score 1 · Accepted Answer · answered Dec 21 '21 at 20:17

1

The reason why is because you never reset $ImgFileName inside the loop.

Put $ImgFileName = null; inside the loop.

You can also unset the variable, which will result in the same outcome.

for($temp1=0; $temp1<count($Users); $temp1++){
    $stmt->bind_param("s", $Users[$temp1]);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($ImgFileName);
    $stmt->fetch();
    $imageURL[$temp1]=$ImgFileName;

    unset($ImgFileName);
}

answered Dec 21 '21 at 20:17

Dharman

30,962
25
85
135

hmmm, weird, i tried and indeed it works. i expected that whenever sql statement gives null, which it does if i just run it for one user with no filename, then it would automatically bind $ImgFileName = null;. Glad I asked, because I wasnt expecting that. Many Thanks, Ser. – luca amarelli Dec 21 '21 at 20:27
@lucaamarelli When a prepared statement doesn't find any rows, it won't return any value, not even null. – Dharman Dec 21 '21 at 20:31
yea, now i can see. lesson learned. thank you again. – luca amarelli Dec 21 '21 at 20:36

score 1 · Answer 2 · answered Dec 21 '21 at 20:27

An alternative is to store the file name in an array with the name as the index (along with ensuring that the image name is blanked out each time)...

for($temp1=0; $temp1<count($Users); $temp1++){
    $ImgFileName = '';
    $userName = $Users[$temp1];
    $stmt->bind_param("s", $userName);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($ImgFileName);
    $stmt->fetch();
    $imageURL[$userName] = $ImgFileName;
}

If you also combine this with using in (from How do you use IN clauses with mysqli prepared statements) you could just fetch a list of the known users and filenames in one execution...

$stmt = $db->prepare("SELECT username, file_name
                       FROM images 
                       WHERE username in...

just loop through the results and add them into an array.

prepared statement in the loop

2 Answers2