PHP help needed

Discussion in 'PHP' started by tronik, Apr 12, 2012.

0
  1. #1
    Okay so I created a script that stores multiple ID's in one field in a sql table. So the ID's look this in a table 1,2,3(seperated with a comma). I was able to get the ID's using explode because it stores the data in a array, but I wan't to use mysql_num_rows(in a loop) to count how many movies are in a specific genre.

    
$select_genres = mysql_query("SELECT * FROM genres ORDER BY position") or die(mysql_error());
while($genres = mysql_fetch_array($select_genres)){

$count_movies = mysql_num_rows(mysql_query("SELECT * FROM movies WHERE genre_ids='{$genres['genre_id']}'"));
}
    Code (markup):
    Basically I don't know how to get the genre id so the mysql_num_rows will run without errors. Anyone know a function or a way to do this?
     
    Last edited: Apr 12, 2012
    tronik, Apr 12, 2012 IP
  2. grbrains123

    grbrains123 Member

    Messages:
    180
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    26
    #2
    i dont what exactly your requrement. can you give me more explanation on this so can get more idea and give you suggestion.
     
    grbrains123, Apr 12, 2012 IP
  3. Basti

    Basti Active Member

    Messages:
    625
    Likes Received:
    6
    Best Answers:
    3
    Trophy Points:
    90
    #3
    your issue is this part
    
WHERE genre_ids='{$genres['genre_id']}'
    PHP:
    this cant work cos genre_ids (1,2,3) cant match with genre_id ( 1 )

    
$select_genres = mysql_query("SELECT * FROM genres ORDER BY position") or die(mysql_error());
while($genres = mysql_fetch_array($select_genres)){

   $find_movies = mysql_query("SELECT * FROM movies WHERE FIND_IN_SET('{$genres['genre_id']}', genre_ids)");
   $count_movies = mysql_num_rows($find_movies);

}
    PHP:
    not sure if find_in_set would work for you though, but this should search for genre_id within the list of 1,2,3. Normally it would return the position of the find, but since you use num_rows this might work
     
    Basti, Apr 13, 2012 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    A better way to do this whole thing would be to use a separate table for genres, and link each movie to each genre (JOINed QUERIES).

    So you'd have another table with two (or more) columns, named "movie_id" (or something similar) and "genre", and then one entry for each genre the movie matches. Easily picked out with queries afterwards. Storing multiple values in one field is usually frowned upon (it sorta defeats the purpose of a database to begin with)
     
    PoPSiCLe, Apr 18, 2012 IP