CODEWITHSUNDEEP
phpmysqlarrays
Sorcher
Sorcher

Reputation: 41

MySQL: Select values from table where value is in array

I've been looking around for my solution for a while now with no success. I figured i'd ask the elite of web development instead..

I will try to explain this simple.

Array:

$image_exts = array('jpg','jpeg','gif','png', 'bmp','webp');

What i need to do is something like this:

SELECT file_views 
FROM files 
WHERE file_owner='$user_id' 
AND file_ext='$image_exts'

the "file_ext" field in the database can contain "jpg" for one file and "mp4" for another...i only want to count views in this example for images..

Thank you for your time!

Update 2:

function count_totalviews($user_id){
    $image_exts = array('jpg','jpeg','gif','png','bmp','webp');
    $image_array=implode(",", $image_exts);
    $query = mysql_query("SELECT file_views FROM files WHERE file_ext IN ($image_array) AND file_owner='$user_id'");
        $count=0;
            while($row = mysql_fetch_assoc($query)){
                $count = $row['file_views'] + $count;
            }
        return $count;
    }

PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result

No clue what to do...

Upvotes: 2

Views: 3726

Answers (2)

Alvin S. Lee
Alvin S. Lee

Reputation: 5182

Use mysql's IN, but ensure that the parenthesized list of image extensions contains strings which are in quotes.

Your code at Update 2 is almost there, but you have a problem at this line:

$image_array=implode(",", $image_exts);

$image_array ends up being jpg,jpeg,gif,png,bmp,webp when really you need it to be 'jpg','jpeg','gif','png','bmp','webp' for it to be usable in your query.

Replace that line of code with:

$image_array = "'" . implode("','", $image_exts) . "'";

Keep the rest of your code, and see if that works.

Upvotes: 2

Mohanquietµ
Mohanquietµ

Reputation: 1

Use IN:

you can use $image_Array=implode(",", $image_exts) to get the list together from the array.

SELECT file_views FROM files WHERE file_owner='$user_id' AND file_ext IN ('$image_Array')

Upvotes: 0

Related Questions