Page 1 of 1
Need Help Sorting Data from Multiple Tables
Posted: Wed Jun 17, 2009 10:48 pm
by jballou
I have a relational database set up. One table has projects. One table has keywords. And a third table contains the relational data of which keywords are related to which projects. My problem is that I don't know how to use that data to actually list out a project's keywords!
The project table has 3 fields, id, title, and content. The keyword table has id and keyword. The third relational database has projectid and keywordid.
I know to pull in the project info I use $sql = "SELECT * FROM project"; but how would I also pull in the keyword and relational data at the same time and use one while loop to display each project's keywords?
I know this is a newbie question, but any help at all would be greatly appreciated, even a nudge in the right direction. Thanks so much in advance!
Re: Need Help Sorting Data from Multiple Tables
Posted: Wed Jun 17, 2009 11:21 pm
by omniuni
Try this:
note that this is kind of pseudo code, because I don't know how you're connecting to your db....
Code: Select all
//first get all key words for a project
$results = SELECT * FROM relations WHERE projectid='3';
//now, get a list of all the keyword id's...
$keywords;
foreach($results as $result){
$getKeywordWithID = $result['keywordid'];
//now SQL
$keyWord = SELECT * FROM keyword WHERE id='$getKeywordID';
$keyWordList[] = $keyWord['keyword'];
}
print_r($keyWordList); //your keywords
Sorry for the generic pseudo code sort of stuff, but basically the idea is to use the WHERE clause to select your specific keywords.
Good Luck!
-OmniUni
Re: Need Help Sorting Data from Multiple Tables
Posted: Wed Jun 17, 2009 11:24 pm
by requinix
How about a
JOIN? Two, actually.
Code: Select all
SELECT p.*, k.keyword FROM projects p
JOIN relationaltable r ON p.id = r.projectid # now you have keywordid
JOIN keywords k ON r.keywordid = k.id # now you have the keyword
Then you get a resultset that looks like
Code: Select all
id | title | content | keyword
---+-------+---------+----------------
1 | Title | Content | Keyword
1 | Title | Content | Another keyword
1 | Title | Content | Third keyword
2 | tITLE | cONTENT | Keyword
2 | tITLE | cONTENT | kEYWORD
It means duplicated data, but it's easier (and most likely faster) than issuing a SELECT for every single project - because SQL engines can do it better than your code can.
Re: Need Help Sorting Data from Multiple Tables
Posted: Thu Jun 18, 2009 10:35 am
by jgadrow
The solution above is correct. Also, depending upon the SQL server that you are using, there's usually some form of group concatenation available. Basically, this would allow you to remove the duplicate rows but your keyword data would be concatenated with some sort of separator. This comes with a little more of a performance cost inside of the database however, if you're going to be doing some of this processing in PHP anyways, it may perform faster because the SQL code is from a compiled binary whereas the PHP code is interpretatively processed (which, generally, takes longer). However, only timing the execution would tell which is right for your environment. If your database is under considerable strain and the web server has idle cycles, it would be better for you to not make your database work to concatenate these for you.
Anyways, the output of something like this could look like (using above example):
Code: Select all
id | title | content | keyword
---+-------+---------+----------------
1 | Title | Content | Keyword,Another keyword,Third keyword
2 | tITLE | cONTENT | Keyword,kEYWORD
Re: Need Help Sorting Data from Multiple Tables
Posted: Thu Jun 18, 2009 7:09 pm
by jballou
Thanks so much for your help guys! I got it running very well. I'm curious jgadrow- what command did you use to concatenate the data that way? I think that would be most helpful to know! Thanks so much