mysql query advice needed

porco

Hi
Here is a code snippet with my query...

// retrieve the two most recent articles from technology
$result = mysql_query("select TBArticle.ArticleSubject, TBArticle.ArticleDateTime from TBArticle, subjectlist where subjectlist.subject_name='Technology'");

		 $rows = mysql_num_rows($result); 

   for ($i = 0; $i < $rows; $i++) 
   { 
       $data = mysql_fetch_row($result); 

       echo "$data[0], $data[1], $data[2]<br>\n"; 
   }

......................................................................................
Its the query that concerns me. I am wondering if it is actually possible to get the correct result from the database in the first place. Here are the table specs....

subjectlist
subjectid: integer, Primary Key
subject_name: 8 Categories for Technology, Enterprise, etc.

TBArticle
ArticleId int(11) NOT NULL auto_increment
NodeId bigint(20) NOT NULL default '0'
ArticleWriter varchar(255) default NULL
ArticleDateTime datetime NOT NULL default '000-00-00 00:00:00
ArticleSubject varchar(255) NOT NULL default ''
ArticleBody longtext
PRIMARY KEY (ArticleId)

TBTree
CategoryName varchar(255) default NULL
NodeId bigint(20) NOT NULL default '0'
ParentNodeId bigint(20) NOT NULL default '0'
Terminal enum ('N','Y') NOT NULL default 'Y'
NodeName varchar(255) NOT NULL default
NodeTip varchar(255) NOT NULL default

......................................................................

TBTree.CategoryName is actually the same as subjectlist.subject_name but in the first one it repeats.

I seem to get variant output depending on which one of these I use in the query. CategoryName hits me with sometimes 20 of each match.

Thanks to anyone who even looks at this one... 🙂

onion2k

select TBArticle.ArticleSubject, TBArticle.ArticleDateTime from TBArticle, subjectlist where subjectlist.subject_name='Technology'

You don't have anything joining the two tables in your query.

select TBArticle.ArticleSubject, TBArticle.ArticleDateTime from TBArticle, TBTree where TBArticle.NodeID = TBTree.NodeID and TBTree.CategoryName='Technology'

I think thats about right. Your DB structure is rather different to the way I'd have designed it.

porco

Yes, the tables that are related have been tried too. I queried for where CategoryName='Technology' but got the same results only multiplied by the number of matches. So 23 matches gave me (I think) all the results 23 times. 🙂

I woke up and thought of querying the nodes..... This is a legacy database, would rather make it work than remake everything. What I would probably like to do is put a foreign key for the subjectlist.subjectid into the TBArticle table. Worth a try as a last resort. But don't want to jeapordise the information already inside there.

Anyway thanks, I thought so. I am not really very experienced with mysql or database management systems so its a bit of a curve going on at the moment.

Thanks.