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_linkcategori es 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_linkcategori es, and the link_category of table wp_links.

So in table wp_linkcategori es 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_linkcategori es 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]

Re: selecting data from two different tables

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_linkcategori es;
+--------+----------+
| 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_linkcategori es 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_linkcategori es 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]

Re: selecting data from two different tables

I noticed that Message-ID:
<1114315944.408 684.6220@o13g20 00cwo.googlegro ups.com> from justbn
contained the following:
[color=blue]
>select cat_id, cat_name, link_category, link_name from
>wp_linkcategor ies left join wp_links on cat_name = link_category order
>by cat_id desc;[/color]

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_linkcategori es,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]

Re: selecting data from two different tables

Geoff Berrow wrote:[color=blue]
> I suspect this has been done before, but why is left join better than
> where?[/color]

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]

Re: selecting data from two different tables

I noticed that Message-ID:
<426b67a2$0$165 $e4fe514c@dread er4.news.xs4all .nl> from Ewoud Dronkert
contained the following:
[color=blue][color=green]
>> I suspect this has been done before, but why is left join better than
>> where?[/color]
>
>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.[/color]

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/