MySQL: Select values from table where value is in array

Question

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

use IN clause in query..so file_ext will check only values which are in array of $image_exts — user2663434
– user2663434, Commented Feb 8, 2014 at 4:30
I tried SELECT file_views FROM files IN($image_exts) WHERE file_owner='$user_id' didnt work — Sorcher
– Sorcher, Commented Feb 8, 2014 at 4:37
Here: WHERE file_owner='$user_id' AND file_ext IN '$image_exts' @Sorcher - That ought to make it kick in. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 8, 2014 at 4:39
Or WHERE file_owner='$user_id' AND file_ext IN (".$image_exts.") — Funk Forty Niner
– Funk Forty Niner, Commented Feb 8, 2014 at 4:41

Alvin S. Lee · Accepted Answer · 2014-02-08 16:03:13Z

2

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.

answered Feb 8, 2014 at 16:03

Alvin S. Lee

5,19233 silver badges34 bronze badges

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

Comments

Mohanquietµ · Accepted Answer · 2014-02-08 04:53:08Z

0

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')

answered Feb 8, 2014 at 4:53

Mohanquietµ

12 bronze badges

4 Comments

Sorcher Over a year ago

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

Sorcher Over a year ago

Maybe the query works but not the mysql_fetch_assoc for this example?

Mohanquietµ Over a year ago

I think you given table name and column name Wrong, chkit

Sorcher Over a year ago

I have trippel checked it and i have manually given the $user_id but no luck, still the same error. please look at the updated post for the full php function

Collectives™ on Stack Overflow

MySQL: Select values from table where value is in array

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related