Need help with PHP and MYSQL Join Code

I hope I am posting this in the right forum and please accept my appolgieze if I made an error.

I have two MySQL Tables I am tring to get information from the one which uses the Category Name where the other one only uses the category ID. What I am tring to do is list what categories a user has submitted the articles to as its associated with the USERS AuthorID…

The first table is called tarticles and this one stores the category ID and the AuthorID that submitted it.

The second table is called tcategories and this stores the Name of the category as well as the ParentID of the category…

What I am tring to do is associate the AuthorID who submitted to whatever category with the category name if that makes any sense…

I am probably making this more confusing then what it needs to be but I hope someone gets what I am referring too…

Here is the structure of table 1

table tarticles structure

CREATE TABLE IF NOT EXISTS `tarticles` ( `intId` int(11) NOT NULL auto_increment, `intAuthorId` int(11) NOT NULL default '0', `intCategory` int(11) NOT NULL default '0', `varArticleTitle` varchar(255) NOT NULL default '', `textSummary` mediumtext NOT NULL, `varKeywords` varchar(255) NOT NULL default '', `textResource` mediumtext NOT NULL, `intStatus` tinyint(1) NOT NULL default '0', `ttSubmitDate` date NOT NULL, `word_count` mediumint(9) NOT NULL default '0', `SubmittedBy` varchar(50) NOT NULL default 'live', `declined` int(1) NOT NULL default '0', `hits` varchar(12) NOT NULL, `views` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`intId`), UNIQUE KEY `varArticleTitle` (`varArticleTitle`), KEY `intCategory` (`intCategory`), KEY `intAuthorId` (`intAuthorId`), KEY `varKeywords` (`varKeywords`), KEY `ttSubmitDate` (`ttSubmitDate`), KEY `word_count` (`word_count`), KEY `SubmittedBy` (`SubmittedBy`), KEY `intStatus` (`intStatus`), KEY `declined` (`declined`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1083 ;

Second table structure tcategory and the row called varCategory stores the Name of the Category

CREATE TABLE IF NOT EXISTS `tcategories` ( `intID` int(11) NOT NULL auto_increment, `varCategory` varchar(200) NOT NULL default '', `textDescription` mediumtext NOT NULL, `intHasChild` int(11) NOT NULL default '0', `intHasArticles` int(12) NOT NULL default '0', `intParentID` int(11) NOT NULL default '0', `intIsNew` tinyint(1) NOT NULL default '1', `ttDateCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `cat_keywords` varchar(255) NOT NULL, PRIMARY KEY (`intID`), KEY `intParentID` (`intParentID`), KEY `varCategory` (`varCategory`), KEY `intHasChild` (`intHasChild`), KEY `intHasArticles` (`intHasArticles`), KEY `intIsNew` (`intIsNew`), KEY `ttDateCreated` (`ttDateCreated`), KEY `cat_keywords` (`cat_keywords`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=418 ;

Any help would be extremly appreciated and I am willing to pay through Paypal for anyones help that can come up with the solution…

xtrax :frowning:

Try something like this…

[php]SELECT tarticles., tcategories.

FROM tcategories

LEFT JOIN tarticles ON tarticles.intCategory = tcategories.intID

WHERE tarticles.intAuthorId = 1[/php]

1 Like

thank you this did work sorry for the very late reply

Sponsor our Newsletter | Privacy Policy | Terms of Service