Jump to content

[SOLVED] Joining MySQL Tables

htmlstig

By htmlstig
in MySQL Help

Recommended Posts

htmlstig Newbie

htmlstig

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/138255-solved-joining-mysql-tables/
Share on other sites
gwydionwaters Newbie

gwydionwaters

Posted
Posted

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/

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.