Probably simple, but i am new here

[palmerinla]

Hi there, I have a website built with PHP/MYSQL and I am slowly learning bits and pieces of PHP.

Most of my site is finished, but there are a few items i want that I figured I would try to write myself since it doesn't require created new tables or anything.

I have a News table and an Artists table. When we enter news, we can assign the news story to up to 5 different artists. What I would like to do is pull starting from my most recent news stories the artists names and display them in Alphabetical order and limit the list to 20 names. When user's click on the names, it takes them to the news story that artist is assigned to.

Now, the tricky part is that some of these artists appear in multiple news stories, so it would have to by default just link to the most recent story.

The fields in the table are Artist1, Artist2, Artist3, Artist4, and Artist5

I tried this: <?php echo $news2->Fields(Artist1); ?>, and it brought up the artists id #, however, I don't know hot to display the artist name, link it to the corresponding article or display the results in the order I want.

Sorry if this is so long. if i am making no sense, feel free to let me know. You can check out the page where this would be displayed at www.maximumrotation.com/news

thanks for the help

[drawmack]

we need the complete layout of all three tables to help with this.

[palmerinla]

Here are the 2 tables involved, what other table do i need to use??

phpMyAdmin MySQL-Dump

version 2.3.2

http://www.phpmyadmin.net/ (download page)

#

Host: localhost

Generation Time: Feb 11, 2004 at 11:45 AM

Server version: 3.23.58

PHP Version: 4.3.3

Database : maximumrotation_com_-_site

--------------------------------------------------------

#

Table structure for table DJS

#

CREATE TABLE DJS (
ID int(10) unsigned NOT NULL auto_increment,
Name varchar(255) NOT NULL default '',
Hometown varchar(255) NOT NULL default '',
URL varchar(255) NOT NULL default '',
Label varchar(255) NOT NULL default '',
Description mediumtext NOT NULL,
ShortDescription mediumtext NOT NULL,
Active enum('Y','N') NOT NULL default 'Y',
Featured enum('Y','N') NOT NULL default 'Y',
Image int(10) NOT NULL default '0',
PRIMARY KEY (ID)
) TYPE=MyISAM;

phpMyAdmin MySQL-Dump

version 2.3.2

http://www.phpmyadmin.net/ (download page)

#

Host: localhost

Generation Time: Feb 11, 2004 at 11:46 AM

Server version: 3.23.58

PHP Version: 4.3.3

Database : maximumrotation_com_-_site

--------------------------------------------------------

#

Table structure for table News

#

CREATE TABLE News (
ID int(10) unsigned NOT NULL auto_increment,
ClientNum varchar(10) NOT NULL default '',
Date datetime NOT NULL default '0000-00-00 00:00:00',
Headline varchar(255) NOT NULL default '',
Author varchar(40) NOT NULL default '',
Content mediumtext NOT NULL,
Image varchar(10) NOT NULL default '',
URL varchar(255) NOT NULL default '',
ActiveDate date NOT NULL default '0000-00-00',
Active enum('Y','N') NOT NULL default 'Y',
Artist1 varchar(10) NOT NULL default '',
Artist2 varchar(10) NOT NULL default '',
Artist3 varchar(10) NOT NULL default '',
Artist4 varchar(10) NOT NULL default '',
Artist5 varchar(10) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=MyISAM;

[drawmack]

SELECT news.ID,news.URL,djs.Name
FROM djs,news
WHERE djs.id = news.Artist1 OR
djs.id = news.Artist2 OR
djs.id = news.Artist3 OR
djs.id = news.Artist4 OR
djs.id = news.Artist5
ORDER BY news.Date DESC
GROUP BY djs.Name

Date is a reserved word in mysql so having a column named that may cause problems for you.