Member Avatar for Vai

Here are the tables in my database.

music (
  id int(11) NOT NULL auto_increment,
  catid int(11) default NULL,
  song_title varchar(255) default NULL,
  artist varchar(75) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Category

category (
catid int(11) NOT NULL auto_increment,
name varchar(30) default NULL,
PRIMARY KEY  (`catid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

category  VALUES(1, 'Bridal Songs');
category  VALUES(2, 'Party Songs');

Now, Here is my question:
How would I display information based on the category that the songs are in.

Thanks once again for the help.

  • 2 Contributors
  • 9 Replies
  • 151 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Vai
Member Avatar for somedude3488

i would use:

<?php
$catid = '1';
$sql = "SELECT * FROM `music` WHERE `music`.`catid` = '" . $catid . "'";
$query = mysql_query($sql, $con) or die('Error: ' . mysql_error());
while ($row = mysql_fetch_assoc($query)) {
    display information
}
?>
Member Avatar for Vai

Keith: Thanks for the help. I have one more question. Is there a way I can display the category name as well for example:

Category Name 
Song Title         Artist
Member Avatar for somedude3488 
$catid = '1';
$sql = "SELECT `music`.`song_title`,`music`.`artist`,`category`.`name` FROM `music`,`category` WHERE `music`.`catid` = `category`.`catid` AND `music`.`catid` = '" . $catid . "' ORDER BY `category`.`catid` DESC";
$query = mysql_query($sql,$con);
while ($row = mysql_fetch_assoc($query)) {
    $cat_name = $row['name'];
    $song_title = $row['song_title'];
    $artist = $row['artist'];
}

i think that should work. i cant remember if my syntax is correct or not. let me know if it works or not. i will fix it if it doesn't.

Member Avatar for Vai

keith
Here is what I get when I load it:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in party.php on line 5

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in party.php on line 6
Member Avatar for somedude3488

try this.

$catid = '1';
$sql = "SELECT `music`.`song_title`,`music`.`artist`,`category`.`name` FROM `music`,`category` WHERE `music`.`catid` = `category`.`catid` AND `music`.`catid` = '" . $catid . "' ORDER BY `category`.`catid` DESC";
$query = mysql_query($sql);
while ($row = mysql_fetch_assoc($query)) {
    $cat_name = $row['name'];
    $song_title = $row['song_title'];
    $artist = $row['artist'];
}
Member Avatar for Vai

Keith,
I got it work, I had to take $con. Thanks again for the help.

:)

Member Avatar for Vai

This is what I get:

Bridal Songs
Song Title                     Artist 
test                              test 

Bridal Songs
Song Title                     Artist 
A Song For Moma          Boys II Men

This is what I need:

Bridal Songs
Song Title                     Artist 
test                              test 
A Song For Moma          Boys II Men
Member Avatar for somedude3488

you will have to query the database 2 seperate times to do that. one to get the category name and one to display the data.

Member Avatar for Vai

ok thanks man. have a good night.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.