joining 2 mysql tables

trying to associate links with link categorys

[generic]

Hey folks,

I'm simply trying to associate links with link categories for output. I know I need to join the two tables (links and linkcats) but I'm unsure exactly how and I'm having trouble grasping the concept (and yes, I've read a ton of tutorials and the mysql manual to no avail) Can someone offer a practical example on how I could SELECT the data from the two tables for output something like:

$linktext ($linkcat): <a href="delete.php?id=$linkid">delete</a>

for example, where $linkcat 1 is "search engines" and 2 is "government":
Google (1): <a>delete</a>
White House (2): <a>delete</a>
Yahoo (1): <a>delete</a>
etc..

Here's my db:

mySQL:

-- Table structure for table `linkcats`
--

CREATE TABLE `linkcats` (
`linkcat` int(11) NOT NULL auto_increment, //category id #
`linkcattext` varchar(255) default NULL, //title of category
PRIMARY KEY (`linkcat`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `links`
--

CREATE TABLE `links` (
`linkid` int(11) NOT NULL auto_increment, //link id #
`linkcat` int(11) NOT NULL, //associated category id #
`linktext` varchar(255) default NULL, //title of link
`linkurl` varchar(255) default NULL, //url of link
PRIMARY KEY (`linkid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Hope that all makes sense. Thanks in advance!

rk

[sned]

If I'm reading your tables correctly, you could pull the data out of them with query like this:

SELECT links.*, linkcats.linkcattext FROM links INNER JOIN linkcats ON (links.linkcat = linkcats.linkcat)
ORDER BY linkcats.linkcat

-sned

[generic]

Thanks sned, it works. I don't really know 'how' it works, but it works :) Problem is, I need to do other joins too, like when I add a link, I need a dynamic dropdown of available link categories so I can associate individual links when I add them through my little cms get-up here.

Should I just be able to reuse that same select in my form and loop it into a dropdown?

Thanks so much for your help, I've been chewing on joins for too long now :)

Cheers,

rk

[generic]

Ok, I'm slowly starting to pick this up. Strange stuff for php n00bs, for sure. Thanks again sned, mucho helpful!

I've come across another problem though. When I got to output my list of links, I can easily dump it all out in a nice ordered list or whatever I might need, but I'm unsure how to flag and sort the links by category (preferably with a header)? I know there's sql to do it but I'm clueless. Can someone point out a method of breaking up all the links by link category for output? ie,

CATEGORY 1
Link 1
Link 2

CATEGORY 2
Link 3
Link 4

etc..

Any help would be appreciated.

Regards,

rk