How do I get all keywords for a certain item?

vigour

I have three tables called 'pages', 'keyword' and 'kw_t_p'.

The 'pages' table have the following fields:
p_id, p_title

The data are stored in this form:
1, Title 1
2, Title 2

The 'keyword' table have the following fields:
kw_id
kw_label

The data are stored in this form:
1, Car
2, Dog
3, Food
and so on...

The 'kw_t_p' table have the following fields:
kw_nr
p_nr

The data are stored in this form:
3, 1
2, 2
2, 1
6, 1
1, 1
5, 1
10, 2
8, 2
1, 2
7, 2

All items have 5 keywords connected to them. As you can see the first item have the following keywords 3, 2, 6, 1, 5, item two have 2, 10, 8, 1, 7

I need to run a query that returns all keywords (Car, Dog and so on) for a certain item (1, 2 and so on).

I know how to search for all kw_nr with a certain p_nr but that does not give me the keywords, just numbers that the keyword represents.

How do I build a query that can handle this?

leatherback

Vigourt: What have you tried??

$query = "select p_nr, kw_label
from kw_t_p 
left join keyword
where p_nr = ".$page_nr."
order by kw_label";

edit: And seriously.. Give it a fair change. All of us on the board were beginners once, and we know like no other: the only way to learn is to put some serious time in it. Usually when I have a coding problem, I have tried for several hours before posting (Except of course, when it is 2am and I really want to get to bed).

vigour

I looked at this example but I could not understand it.

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

I understand that you don't want people that asks for help all the time without trying anything. I always tries a lot of things before I ask questions on this forum.

I'm sure your code works, but it would be better if you explained what happens in the code because I (and others) will learn a lot by that. Just a suggestion from a newbie. Thanks for helping me.

Piranha

Did you understand the tutorials up to that one, especially the one about join? If so then you have already learned a lot. If not then try it again, and try a few other tutorials as well. And don't just stop by reading the tutorials, set up a database of your own and try it out as well. I have posted 2 links in the other thread.

leatherback

OK, point taken. And I spotted a missing join section, which I am sure you noticed 🙂

$query = "select p_nr, kw_label
from kw_t_p
left join keyword
on kw_id = kw_nr
where p_nr = ".$page_nr."
order by kw_label";

$query = : Tells the compiler: We are filling a variable, called query, with something. (Since this is a query we are creating, YOU know it will be used as a query)
" : Start of a string sequence
"; : End of string sequence, and end of command

The query:
select p_nr, kw_label: We are performing a select, and as the database to return 2 fields ('columns') from the database, bamely p_nr and kw_label
from kw_t_p: Retrieve them from table kw_t_p
left join keyword: Which should be joined to keyword, returning only matching rows
on kw_id = kw_nr: Combine the table, for all records ('rows') where kw_id and kw_nr match
where p_nr = ".$page_nr.": Make sure that for all records the p_nr value equals the value of variable $page_nr
order by kw_label";: Order the results alphabetically on kw_label

Note: I placed ". ." around the variable: It is easier to read if you use a highlighting code-editor, and can safe you some trouble (e.g., is you are using arrays, subscripted by variables, this works nicely)
Hope it helps.