8 Replies - 3503 Views - Last Post: 10 December 2011 - 09:12 AM Rate Topic: -----

#1 clarkeash   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 56
  • Joined: 04-February 11

PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 07:30 AM

I have a database of movies and genres, as 1 movie may have multiple genres I have created 3 tables.
a table that contains the movie info (tbl_film)
Attached Image

a table that contains a list of genres (tbl_genre)
Attached Image

and a table that links a genre to a film (link_genre)
Attached Image

As an example i have tried the following SQL statement
SELECT tbl_genre.name as genre
FROM tbl_film, tbl_genre, link_genre
WHERE tbl_film.id_film=link_genre.id_film
AND link_genre.id_film ='4'
AND link_genre.id_genre = tbl_genre.id_genre


From this i get the following output
Attached Image

My problem now is use php to display all the genres in a list.
I have thought about using a while loop and using the id_lg to get the data (incrementing by 1 each time) however i feel that this would be inefficient due to the fact that it could potentially have to go through thousands of items.

Your help is very much appreciated.

Thanks

This post has been edited by clarkeash: 10 December 2011 - 07:43 AM


Is This A Good Question/Topic? 0
  • +

Replies To: PHP/MySQL - Get data from multiple rows

#2 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,994
  • Joined: 08-August 08

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 07:33 AM

http://dev.mysql.com...timization.html
Was This Post Helpful? 0
  • +
  • -

#3 clarkeash   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 56
  • Joined: 04-February 11

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 07:48 AM

Thanks for the link, but I am not sure how i would use this (I am not very familiar with using 'distinct', it seems to just remove repeated items.
Any advice?

Although the title is repeated, there are no repeats in that query
Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,994
  • Joined: 08-August 08

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 08:30 AM

View Postclarkeash, on 10 December 2011 - 10:30 AM, said:

My problem now is use php to display all the genres in a list.

This made me think that you want only unique genres returned from your query. Isn't that what you got?
Was This Post Helpful? 0
  • +
  • -

#5 clarkeash   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 56
  • Joined: 04-February 11

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 08:34 AM

The genres will always be unique as i have designed the database so that there will be no duplication.
I just need to display on the screen all the genres associated with a particular film.
Was This Post Helpful? 0
  • +
  • -

#6 clarkeash   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 56
  • Joined: 04-February 11

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 08:39 AM

sorry ignore me all i need to use mysql_fetch_assoc
Was This Post Helpful? 0
  • +
  • -

#7 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,994
  • Joined: 08-August 08

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 08:42 AM

So then you'd do something like:
SELECT genre WHERE name='Toy Story'
Of course you'd use prepared statements so it would look more like:
SELECT genre WHERE name= ?
and you might want to get other fields, but that's basically it.

View Postclarkeash, on 10 December 2011 - 11:39 AM, said:

sorry ignore me all i need to use mysql_fetch_assoc

NOOOOO!!! Do not use mysql_* functions.
Use prepared statements.
Was This Post Helpful? 0
  • +
  • -

#8 clarkeash   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 56
  • Joined: 04-February 11

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 08:45 AM

That link looks confusing,
I have the following
$query = "SELECT tbl_genre.name as genre
FROM tbl_film, tbl_genre, link_genre
WHERE tbl_film.id_film=link_genre.id_film
AND link_genre.id_film ='4'
AND link_genre.id_genre = tbl_genre.id_genre"; 
	 
$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
	echo $row['genre'];
	echo "<br/>";
}

?>


what should i do instead of using mysql_fetch_array???
Was This Post Helpful? 0
  • +
  • -

#9 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3837
  • View blog
  • Posts: 13,994
  • Joined: 08-August 08

Re: PHP/MySQL - Get data from multiple rows

Posted 10 December 2011 - 09:12 AM

This should be close:
$query = "SELECT tbl_genre.name as genre
FROM tbl_film, tbl_genre, link_genre
WHERE tbl_film.id_film=link_genre.id_film
AND link_genre.id_film = ?
AND link_genre.id_genre = tbl_genre.id_genre"; 
$id = array(4);
$db = new PDO($dsn, $username, $password);
$genre = $db->prepare($sql);
$genre->setFetchmode(PDO::FETCH_ASSOC);
$genre->execute($id);

foreach($genre as $g) {
	echo $g['genre']."<br>";
}


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1