Need help with PHP Join sql willing to pay through Paypal!

xtrax · July 18, 2012

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..

Thank you

xtrax

Barand · July 18, 2012

Something like

SELECT a.intAuthorId, c.varCategory, p.varCategory as parent
FROM tarticles a
    INNER JOIN tcategories c ON a.intCategory = c.intId
    LEFT JOIN tcategories p c.intParentId = p.intId

xtrax · July 18, 2012

Hello Barand,

Thank you for your reply; however I tried that and it didnt work. it could be because i didnt explain myself clearly..

The category name is stored in the varCategory in the table tcategories now the other table is tarticles which stores the id of just the category its submitted in which is intCategory and it also has the intAuthorId....

so I need the articles the author has submitted which is from tarticles and their intAuthorId as well as the intCategory to be linked with the tcategories table varCategory and intParentID to match the tarticles intCategory to pull the varname...

I am willing to pay you for your time or make a donation to the website for any help.

I await your reply

xtrax

Barand · July 18, 2012

Define "didn't work"

What error did it give?

xtrax · July 18, 2012

I didnt get any error Barand, however it didnt pull the Category name that the author has submitted to...

I added the SQL statement you gave me to try

$sql = "SELECT a.intAuthorId, c.varCategory, p.varCategory as parent
FROM tarticles a
    INNER JOIN tcategories c ON a.intCategory = c.intId
    LEFT JOIN tcategories p c.intParentId = p.intId  WHERE intAuthorId = '$author_ID' AND intStatus = '1' DESC LIMIT 0, 10";

then I used the following to try and display the category name

$result = $d->fetch($sql);
$cattname = $result['varCategory'];
echo "$cattname\n";

But it didnt pull any data...

xtrax

xtrax · July 18, 2012

Barand?

Would it be easier if I gave you FTP and cpanel access? And then I can pay you for your time as I have tried many things and nothing has worked..

xtrax

xyph · July 18, 2012

Could you post some sample data for both of those tables, so I can test my queries before posting them?

xtrax · July 18, 2012

Sure thing

here is the tarticle table

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=1231 ;

INSERT INTO `tarticles` (`intId`, `intAuthorId`, `intCategory`, `varArticleTitle`, `textSummary`, `varKeywords`, `textResource`, `intStatus`, `ttSubmitDate`, `word_count`, `SubmittedBy`, `declined`, `hits`, `views`) VALUES
(1, 19, 94, 'Online Heavy Equipment Rental: Things To Remember', 'These days, there are a lot of people who prefer to undertake home improvement projects on their own. While this is fine as far as simple home improvement goes, there are some building or construction projects that you may have to call in the pros for and consider construction or heavy <a href="https://www.hertzequip.com">equipment rental</a>. Besides, you won''t have difficulty finding a rental company to work with.', 'equipment rental,rental equipment,excavator,rental,equipment', 'If you want to know more about <a href="https://www.hertzequip.com">rental equipment</a> then visit https://www.hertzequip.com for more details.', 1, '2012-06-19', 431, 'USER', 0, '', 35);

Here is a tcategory table sample

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 ;

Here is the insert data for tcategories

INSERT INTO `tcategories` (`intID`, `varCategory`, `textDescription`, `intHasChild`, `intHasArticles`, `intParentID`, `intIsNew`, `ttDateCreated`, `cat_keywords`) VALUES
(1, 'Arts', 'root', 9, 4, 0, 1, '2012-07-14 08:43:07', 'arts,classics,painting,sculpture,artistic works');

Let me know if you need anything else

xtrax

xyph · July 18, 2012

It's hard to write because you've only given me one row for each table, and those two rows aren't related at all

xtrax · July 18, 2012

Yeah I notied that the article table isnt the right id for the category i gave let me get one for the proper id's, sorry ...

xtrax · July 18, 2012

Would it be easier if I gave you cpanel access?

xyph · July 18, 2012

No problem, if I'm guessing right, you want to simply grab the names of categories that any given author has posted to? So if authorID 19 has posted an article in 5 different categories, you want to get the names of those 5 categories?

It might be easier, but I'd suggest against giving just anyone cPanel access... or at least making a backup first

I guess we're both staffers here, so it's not like we're ENTIRELY random.

Did I clarify what you wanted above?

xtrax · July 18, 2012

Yes exactly what I am after - and I checked and noticed you both were staffers so I figured it would be safe enough to grant access as it may make things easier

try this

INSERT INTO `tarticles` (`intId`, `intAuthorId`, `intCategory`, `varArticleTitle`, `textSummary`, `varKeywords`, `textResource`, `intStatus`, `ttSubmitDate`, `word_count`, `SubmittedBy`, `declined`, `hits`, `views`) VALUES
(1, 110, 24, 'Competently Manage Your Internet Marketing Time', 'Time management is crucial for a positive outcome when pursuing Internet marketing. When you do not devote sufficient time to managing your internet marketing scheme, you could be jeopardizing your chance for a big accomplishment.', 'Business,home business,network marketing,online marketing,affiliate marketing,marketing,advertising,internet business,mlm', 'James Steele is a expert blogger known for writing on a varitey of subjects. His high-quality work can be seen at <a href="http://askmarcbarrett.com/mlm-reviews/qivana-review-whats-up-in-mlm-nutrition-land-have-jerry-campisi-and-mark-yarnell-lost-their-minds">operation Qivana</a> and on <a href="http://askmarcbarrett.com/mlm-marketing/ibuzzpro-review-is-ibuzz-pro-a-low-tech-mlm-lead-bonanza-or-outdated-tool">Ibuzzpro recruiting</a>', 1, '2012-07-17', 474, 'USER', 0, '', 4);

now the category

INSERT INTO `tcategories` (`intID`, `varCategory`, `textDescription`, `intHasChild`, `intHasArticles`, `intParentID`, `intIsNew`, `ttDateCreated`, `cat_keywords`) VALUES (24, 'Business', 'root', 10, 119, 0, 1, '2012-07-18 13:00:50', 'businesses,business,online business,Mom and Pop,cartel,corporation,factory,firm,fly-by-night operation,institution,market,megacorp,mill,monopoly,partnership,shoestring operation,shop,store'),

the category is ia 24 which should be buisness

xtrax

xyph · July 18, 2012

Try this SQL

SELECT DISTINCT
  cat.varCategory
FROM
  tarticles art
  LEFT JOIN
    tcategories cat
  ON
    art.intCategory = cat.intID
WHERE
  art.intAuthorId = 19

replace 19 with the AuthorID you want.

xtrax · July 18, 2012

Hummm ok Im not getting any error message yet its still not showing anything ...

I am using this to pull the data

$sql = "SELECT DISTINCT(cat.varCategory) FROM tarticles art LEFT JOIN tcategories cat ON art.intCategory = cat.intID WHERE art.intAuthorId = '$author_ID'  AND intStatus = '1' DESC LIMIT 0, 10";

$result = $d->fetch($sql);
$cattname = $result['cat.varCategory'];
echo "$cattname\n";

and i tried this as well

$sql = "SELECT DISTINCT(cat.varCategory) FROM tarticles art LEFT JOIN tcategories cat ON art.intCategory = cat.intID WHERE art.intAuthorId = '$author_ID'  AND intStatus = '1' DESC LIMIT 0, 10";
$result = $d->fetch($sql);
$cattname = $result['varCategory'];
echo "$cattname\n";

turned on error reporting and its not reporting any errors

xtrax :-[

xtrax · July 18, 2012

I even tested the

$author_ID

to insure it was passing it through and it shows

author_ID = 124

so i am not sure why but I do think its something to do with ParentID as the category ID#...?

xtrax

xyph · July 18, 2012

Well, DESC needs an ORDER BY clause, I think. And intStatus should be referenced to it's table.

Ordering while using DISTINCT might create a temporary table, so it might be better to sort in PHP for such simple results.

Here's my tables and sample data

--
-- Table structure for table `tarticles`
--

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 ;

--
-- Dumping data for table `tarticles`
--

INSERT INTO `tarticles` (`intId`, `intAuthorId`, `intCategory`, `varArticleTitle`, `textSummary`, `varKeywords`, `textResource`, `intStatus`, `ttSubmitDate`, `word_count`, `SubmittedBy`, `declined`, `hits`, `views`) VALUES
(1, 19, 94, 'Online Heavy Equipment Rental: Things To Remember', 'These days, there are a lot of people who prefer to undertake home improvement projects on their own. While this is fine as far as simple home improvement goes, there are some building or construction projects that you may have to call in the pros for and consider construction or heavy <a href="https://www.hertzequip.com">equipment rental</a>. Besides, you won''t have difficulty finding a rental company to work with.', 'equipment rental,rental equipment,excavator,rental,equipment', 'If you want to know more about <a href="https://www.hertzequip.com">rental equipment</a> then visit https://www.hertzequip.com for more details.', 1, '2012-06-19', 431, 'USER', 0, '', 35);

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

--
-- Table structure for table `tcategories`
--

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 ;

--
-- Dumping data for table `tcategories`
--

INSERT INTO `tcategories` (`intID`, `varCategory`, `textDescription`, `intHasChild`, `intHasArticles`, `intParentID`, `intIsNew`, `ttDateCreated`, `cat_keywords`) VALUES
(94, 'Arts', 'root', 9, 4, 0, 1, '2012-07-18 21:42:13', 'arts,classics,painting,sculpture,artistic works');

And my query/results

mysql> SELECT DISTINCT
    ->   cat.varCategory
    -> FROM
    ->   tarticles art
    ->   LEFT JOIN
    ->     tcategories cat
    ->   ON
    ->     art.intCategory = cat.intID
    -> WHERE
    ->   art.intAuthorId = 19
    ->   AND
    ->   art.intStatus = 1
    -> ORDER BY
    ->   cat.varCategory DESC;
+-------------+
| varCategory |
+-------------+
| Arts        |
+-------------+
1 row in set (0.00 sec)

Is that what you expect?

xtrax · July 18, 2012

yes exactly ---what i am looking for

xyph · July 18, 2012

So why is it working on my replicated table, but not yours?

xtrax · July 18, 2012

I am not sure, it should be but I can give you access so you can look further ....as the file I am using is called authordetails.php

Would that help?

xyph · July 18, 2012

It might. What happens when you put the query into phpMyAdmin?

xtrax · July 18, 2012

I ran the sql query in phpmyadmin for one of our authors that has a ton of articles and it pulls the categories they submitted to...

So I tried on another author with less articles to insure it wasnt pulling all of them and it pulled just the 7 categories so the query is working!!!

However its not showing the results on the page! which is weird

is this correct...

$sql = "SELECT DISTINCT(cat.varCategory) FROM tarticles art LEFT JOIN tcategories cat ON art.intCategory = cat.intID WHERE art.intAuthorId = '$author_ID' AND art.intStatus = '1'";
$result = $d->fetch($sql);
$cattname = $result['varCategory'];
echo "$cattname\n";

xtrax

xyph · July 18, 2012

Well, in the snippet you've posted, $d is undefined, so from what I can tell it's not correct.

xtrax · July 18, 2012

so how should I have written it?

Barand · July 18, 2012

I didnt get any error Barand, however it didnt pull the Category name that the author has submitted to...

I added the SQL statement you gave me to try
$sql = "SELECT a.intAuthorId, c.varCategory, p.varCategory as parent
FROM tarticles a
    INNER JOIN tcategories c ON a.intCategory = c.intId
    LEFT JOIN tcategories p c.intParentId = p.intId  WHERE intAuthorId = '$author_ID' AND intStatus = '1' DESC LIMIT 0, 10";
then I used the following to try and display the category name
$result = $d->fetch($sql);
$cattname = $result['varCategory'];
echo "$cattname\n";
But it didnt pull any data...

xtrax

You need to execute the statement before you canfetch data

Sign In

Need help with PHP Join sql willing to pay through Paypal!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information