selecting data from two different tables

[John]

Hello.

I am just wondering if someone can help me with my PHP/MySql code as I
am not too proficient with it yet.

What I am wanting to do, is display data from two tables.

The first table is called wp_linkcategories and has a list of category
names for my different links.

The second table is called wp_links and is where the actual links are
stored.

I would like to have the category name displayed from the first table
and then all the links that fall in that category listed underneath
from the second table, and then the next category link displayed and
its links until all the category names and links have been displayed.

I believe I need to do an inner join between the two tables and have a
repeatable region until all the data is displayed?

The only items in the two tables that are the same are cat_id of table
wp_linkcategories, and the link_category of table wp_links.

So in table wp_linkcategories I have cat_id and cat_name which I would
need.

In table wp_links I have link_category, link_name, link_url and
link_rating.

I would like the cat_name from table wp_linkcategories listed by
cat_id descending.

Following that I would like to have the link_name from table wp_links
along with the url to make it a hyper link listed by link_rating
descending.

Any ideas how to go about this? I am a bit befuddled on where to
start as I've not done anything this complicated with PHP before.

John

[justbn]

John,

You don't need to do any nested loops to accomplish this. The trick
can be done with the SQL statement. I built some simple tables that
represent your data definition. Here they are :

-----------------------------------------------
mysql> select * from wp_linkcategories;
+--------+----------+
| cat_id | cat_name |
+--------+----------+
| 1 | cats |
| 2 | dogs |
| 3 | birds |
| 4 | horses |
+--------+----------+
4 rows in set (0.00 sec)

-----------------------------------------------
mysql> select * from wp_links;
+---------------+-----------+
| link_category | link_name |
+---------------+-----------+
| cats | siamese |
| horse | shetland |
| cats | manx |
| horses | mule |
| cats | tabby |
| dogs | chow chow |
| birds | cockateel |
| dogs | shepherd |
| birds | parrot |
| dogs | hound |
| cats | bobcat |
| horses | palamino |
+---------------+-----------+
12 rows in set (0.00 sec)
-----------------------------------------------

Now, here is the SQL statement to use :

select cat_id, cat_name, link_category, link_name from
wp_linkcategories left join wp_links on cat_name = link_category order
by cat_id desc;

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

Here are the results :

mysql> select cat_id, cat_name, link_category, link_name from
wp_linkcategories left join wp_links on cat_name = link_category order
by cat_id desc;
+--------+----------+---------------+-----------+
| cat_id | cat_name | link_category | link_name |
+--------+----------+---------------+-----------+
| 4 | horses | horses | palamino |
| 4 | horses | horses | mule |
| 3 | birds | birds | cockateel |
| 3 | birds | birds | parrot |
| 2 | dogs | dogs | shepherd |
| 2 | dogs | dogs | chow chow |
| 2 | dogs | dogs | hound |
| 1 | cats | cats | siamese |
| 1 | cats | cats | tabby |
| 1 | cats | cats | manx |
| 1 | cats | cats | bobcat |
+--------+----------+---------------+-----------+
11 rows in set (0.00 sec)


I hope this helped.
justbn

[Geoff Berrow]

I noticed that Message-ID:
<11********************@o13g2000cwo.googlegroups.c om> from justbn
contained the following:

select cat_id, cat_name, link_category, link_name from
wp_linkcategories left join wp_links on cat_name = link_category order
by cat_id desc;

I suspect this has been done before, but why is left join better than
where?

eg
select cat_id, cat_name, link_category, link_name from
wp_linkcategories,wp_links where cat_name = link_category order
by cat_id desc;

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Ewoud Dronkert]

Geoff Berrow wrote:

I suspect this has been done before, but why is left join better than
where?

It's different; using where is the same as inner join. Left join
includes all records from the first table, even if there's no match in
the second table.
--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/

[Geoff Berrow]

I noticed that Message-ID:
<42*********************@dreader4.news.xs4all.nl > from Ewoud Dronkert
contained the following:

I suspect this has been done before, but why is left join better than
where?

It's different; using where is the same as inner join. Left join
includes all records from the first table, even if there's no match in
the second table.

Ah..thanks.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/