htmlstig Posted December 23, 2008 Share Posted December 23, 2008 hi everyone, hope i have posted this in the right place if not sorry i had a little dabble with php/mysql before but never needed to use it since so most of it got forgotten, so im looking for a little help. Nothing like jumping in the deepend with this one! I am using PHP version 5.2.6 & MySQL version 5.0.51 dont know if this makes any difference I have 3 tables im my mysql database and i need them to link together. This is my sql for Table 1 CREATE TABLE IF NOT EXISTS `catagories` ( `id` int(10) NOT NULL auto_increment, `title` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `title` (`title`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Table 2 CREATE TABLE IF NOT EXISTS `sub` ( `id` int(10) NOT NULL, `subtitle` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Table 3 CREATE TABLE IF NOT EXISTS `sub1` ( `subtitle` varchar(50) NOT NULL, `header` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The id fields in Table 1 & 2 are the same and the subtitle fields in Table 2 & 3 are the same. On my page i wish to view them so that i have a header using the entry of id 1 in the catagories table then a list below that from the sub table whos id matches that of the one from the catagories table. Then each item of the list i would like to make into a link that links to the pages defined in table 3 with the same subtitle as the ones from table 2. Then i would like to repeat this for each id within table 1. i have heard about INNER JOIN, LEFT JOIN & RIGHT JOIN but dont know if i need to use them or how to use them. hope you understand what i mean cheers in advance Quote Link to comment Share on other sites More sharing options...
gwydionwaters Posted December 24, 2008 Share Posted December 24, 2008 ok this should be easy to do, first to clarify. you want the table header to be the category id not the category title? and sub1, the header entry is the url you spoke of? Quote Link to comment Share on other sites More sharing options...
gwydionwaters Posted December 24, 2008 Share Posted December 24, 2008 keep in mind i am no professional i made up a replicate database and wrote a script to try and get the reults you wanted, it looks like this <html> <head> <title>My database</title> </head> <body> <? $hostname="gwaters.db"; //insert your host here $username="test"; //username for your database $password="test"; //password $database="test"; //the name of your database mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die(' unable to select database'); $query = "SELECT categories.id, sub.subtitle, sub1.header FROM categories INNER JOIN sub ON categories.id = sub.id INNER JOIN sub1 ON sub.subtitle = sub1.subtitle"; $result=mysql_query($query); $num=mysql_num_rows($result); mysql_close(); ?> <table> <? $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); $subtitle=mysql_result($result,$i,"subtitle"); $header=mysql_result($result,$i,"header"); ?> <th><? echo $id; ?></th> <tr><td><a href="<? echo $header; ?>"><? echo $subtitle; ?></a></td></tr> <? $i++ ; } ?> </table> </body> </html> the only problem i am finding is that the table comes out like this http://www.gwaters.net/test.php/ Quote Link to comment Share on other sites More sharing options...
htmlstig Posted December 24, 2008 Author Share Posted December 24, 2008 thanks for the reply looking at the page you make it looks similar to what i want ill have a play and let you know how i get on. Cheers Quote Link to comment Share on other sites More sharing options...
htmlstig Posted December 31, 2008 Author Share Posted December 31, 2008 well sorry it took so long to reply it is working but could do with a few tweekns in the rest of my code to get it how i want. Cheers Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.