Jump to content


table join.

Started by _*Creative Insanity_*, Aug 29 2007 01:42 AM

9 replies to this topic

#1 _*Creative Insanity_*

  • Guests

Posted 29 August 2007 - 01:42 AM

I am wondering if it is possible. I have only started using table joins and so far so good.
But I am wondering is this possible.

I have these three tables.

CREATE TABLE `members` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `avatar` varchar(30) NOT NULL,
  `pass` text NOT NULL,
  `msn` varchar(100) NOT NULL,
  `yahoo` varchar(100) NOT NULL,
  `icq` varchar(100) NOT NULL,
  `skype` varchar(100) NOT NULL,
  `aol` varchar(100) NOT NULL,
  `real_name` varchar(40) NOT NULL,
  `genre` varchar(30) NOT NULL,
  `location` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` varchar(4) NOT NULL,
  `www` varchar(100) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM

CREATE TABLE `buddies` (
  `record_ID` int(11) NOT NULL auto_increment,
  `mem_ID` varchar(35) NOT NULL,
  `bud_ID` varchar(11) NOT NULL,
  PRIMARY KEY  (`record_ID`)
) ENGINE=MyISAM

CREATE TABLE `music` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `file` varchar(20) NOT NULL,
  `img` varchar(20) NOT NULL,
  `genre` varchar(40) NOT NULL,
  `txt` varchar(150) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM

The buddies and members I can join and works a treat, but is it possible to join 3 tables?

Ta muchly

#2 Balor

    PHP Nerd

  • Members
  • Pip
  • 63 posts
  • Gender:Male
  • Location:Germany->Frankfurt
  • Interests:My beautyful girl, my son and webcoding. I'm also very interested in art but I'm not really good at art... it's sad but true ^^

Posted 29 August 2007 - 01:58 AM

Sure why not, don't know why it shouldn't work. But you have to be careful using the right references as for example ID is in 2 tables. So something like this would be good(if you don't already do this):

SELECT members.*, b.*, m.* FROM members LEFT JOIN buddies AS b ON b.mem_ID = members.ID LEFT JOIN music AS m ON m.cat = members.cat WHERE members.ID = '12345'

I hope you know what I mean, as I don't know how good you know how to use this.

#3 _*Creative Insanity_*

  • Guests

Posted 29 August 2007 - 02:09 AM

Ok thanks Balor. Seems workable to me. But since I have just started on joins may be a wise move for me to find another way to get the data filtered for the user I want.

#4 Balor

    PHP Nerd

  • Members
  • Pip
  • 63 posts
  • Gender:Male
  • Location:Germany->Frankfurt
  • Interests:My beautyful girl, my son and webcoding. I'm also very interested in art but I'm not really good at art... it's sad but true ^^

Posted 29 August 2007 - 02:10 AM

Well you can just use many sql queries for testing purposes, is much easier and you can still optimize it later... if it isn't a too complex project.

It helped me a lot to look through the source of big projects. You can learn so much from those things. Just a small note.^^

Edited by Balor, 29 August 2007 - 02:12 AM.

#5 _*Creative Insanity_*

  • Guests

Posted 29 August 2007 - 04:01 AM

Well I will try and explain what I am trying to do.

I wrote a buddy system for an application I am writing. A member can view all members and click a button to add a member to their buddy system. From anywhere on the site, once they login their buddies follow them around the site as links and they can click on one of the names and view info about that buddy.
Currently all the data that is displayed is from the buddies and members table which are joined.
This is a detailed query from a master page. But now I need to get data from other tables that are not part of the detail query. This is somewhat of a challenge to me and seems a little over my head for the knowledge I have with sql to date.
The thing I don't understand is how to filter the non detail data with the name or ID of the detailed data for the buddy selected.

So if you can see a way and an idea I could try to get this to happen I would be greatful.

ta muchly

#6 Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 970 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 29 August 2007 - 08:02 PM

I just don't understand how you are trying to link the 'music' table with the other two. You need some column that is similar so you can connect it with atleast one of the other tables. I only one I see is the 'cat' column in the members and music table, but I don't see logically how these fit...

#7 _*Creative Insanity_*

  • Guests

Posted 29 August 2007 - 10:47 PM

yeah I hear you demonslay. It is starting to effect the sleep also. I have no idea how to do this and get the other data.
I have 7 tables I wanted to get data from and I think I am really biting off way more than I can cope with.

I really need to get this data from all the tables but have no idea how.
If you want me to print the tables I can.. I have tried so much now and only seems to disappoint.
This can be very frustrating at times.

Just to save time I will print the full database.

The tables I need the data from are
album
questions
answers
music
ttuts
vtuts
videos

CREATE TABLE `actions` (
  `act_ID` int(11) NOT NULL auto_increment,
  `cat` varchar(35) NOT NULL,
  `action` text NOT NULL,
  PRIMARY KEY  (`act_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

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

-- 
-- Table structure for table `album`
-- 

CREATE TABLE `album` (
  `ID` int(11) NOT NULL auto_increment,
  `img` varchar(40) NOT NULL,
  `title` varchar(75) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15;

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

-- 
-- Table structure for table `buddies`
-- 

CREATE TABLE `buddies` (
  `record_ID` int(11) NOT NULL auto_increment,
  `mem_ID` varchar(35) NOT NULL,
  `bud_ID` varchar(11) NOT NULL,
  PRIMARY KEY  (`record_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4;

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

-- 
-- Table structure for table `forum_answer`
-- 

CREATE TABLE `forum_answer` (
  `question_id` int(4) NOT NULL default '0',
  `a_id` int(4) NOT NULL default '0',
  `a_name` varchar(65) NOT NULL default '',
  `a_email` varchar(65) NOT NULL default '',
  `a_answer` longtext NOT NULL,
  `a_datetime` varchar(25) NOT NULL default '',
  KEY `a_id` (`a_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `forum_question`
-- 

CREATE TABLE `forum_question` (
  `id` int(4) NOT NULL auto_increment,
  `topic` varchar(255) NOT NULL default '',
  `detail` longtext NOT NULL,
  `name` varchar(65) NOT NULL default '',
  `email` varchar(65) NOT NULL default '',
  `datetime` varchar(25) NOT NULL default '',
  `view` int(4) NOT NULL default '0',
  `reply` int(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

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

-- 
-- Table structure for table `members`
-- 

CREATE TABLE `members` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `avatar` varchar(30) NOT NULL,
  `pass` text NOT NULL,
  `msn` varchar(100) NOT NULL,
  `yahoo` varchar(100) NOT NULL,
  `icq` varchar(100) NOT NULL,
  `skype` varchar(100) NOT NULL,
  `aol` varchar(100) NOT NULL,
  `real_name` varchar(40) NOT NULL,
  `genre` varchar(30) NOT NULL,
  `location` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` varchar(4) NOT NULL,
  `www` varchar(100) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=28;

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

-- 
-- Table structure for table `moderate`
-- 

CREATE TABLE `moderate` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `avatar` varchar(30) NOT NULL,
  `pass` text NOT NULL,
  `msn` varchar(100) NOT NULL,
  `yahoo` varchar(100) NOT NULL,
  `icq` varchar(100) NOT NULL,
  `skype` varchar(100) NOT NULL,
  `aol` varchar(100) NOT NULL,
  `real_name` varchar(40) NOT NULL,
  `genre` varchar(30) NOT NULL,
  `location` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` varchar(4) NOT NULL,
  `www` varchar(100) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5;

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

-- 
-- Table structure for table `music`
-- 

CREATE TABLE `music` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `file` varchar(20) NOT NULL,
  `img` varchar(20) NOT NULL,
  `genre` varchar(40) NOT NULL,
  `txt` varchar(150) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;

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

-- 
-- Table structure for table `ttuts`
-- 

CREATE TABLE `ttuts` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `dis` varchar(175) NOT NULL,
  `txt` text NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

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

-- 
-- Table structure for table `videos`
-- 

CREATE TABLE `videos` (
  `vid_ID` int(11) NOT NULL auto_increment,
  `heading` varchar(150) NOT NULL,
  `url` varchar(180) NOT NULL,
  `views` varchar(4) NOT NULL,
  `comms` varchar(11) NOT NULL,
  `img` varchar(60) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`vid_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

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

-- 
-- Table structure for table `vid_comments`
-- 

CREATE TABLE `vid_comments` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `txt` varchar(200) NOT NULL,
  `vid_ID` varchar(11) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30;

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

-- 
-- Table structure for table `views`
-- 

CREATE TABLE `views` (
  `ID` int(11) NOT NULL auto_increment,
  `audio` varchar(5) NOT NULL,
  `album` varchar(5) NOT NULL,
  `video` varchar(5) NOT NULL,
  `bio` varchar(5) NOT NULL,
  `disc` varchar(5) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;

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

-- 
-- Table structure for table `vtuts`
-- 

CREATE TABLE `vtuts` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `file` varchar(30) NOT NULL,
  `img` varchar(30) NOT NULL,
  `txt` varchar(175) NOT NULL,
  `cat` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Edited by Creative Insanity, 29 August 2007 - 10:51 PM.

#8 rc69

    PHP Master PD

  • P2L Staff
  • PipPipPipPip
  • 3,827 posts
  • Gender:Male
  • Location:Here
  • Interests:Web Development

Posted 29 August 2007 - 11:16 PM

Personally, i've never liked the "JOIN" statement. As mySQL provides it by default in some cases, its just easier to do this:
SELECT actions.act_ID, album.id, buddies.record_ID FROM actions, album, buddies WHERE album.id = buddies.record_ID
Sometimes that can be annoying though, the IN statement seems to be a better way of doing that kind of thing (with JOIN of course). As a brief note, think of the column names after the SELECT statement as JS objects (table.field).

Ref:
http://www.mysql.org.../en/select.html
http://www.mysql.org....0/en/join.html

Trust me when i say those are about as confusing as they come (but if you read it/experiement enough, you eventually start to make sense of it, or enough sense to make something work then forget how/why you did it).

#9 _*Creative Insanity_*

  • Guests

Posted 30 August 2007 - 02:24 AM

Oh now my brain hurts. ta RC I will read that join one I think.
This buddy system started out rather easy, but at the last date they ask for more info. Either that or I got it wrong from the start.

#10 _*Creative Insanity_*

  • Guests

Posted 30 August 2007 - 03:36 PM

Well I have been up since 3am working on this and I am happy to say I got the sucker.

The brain was blocking common logic.. daa.. hehe

Well since the site had a profile page for members that showed all their info I thought why not just use that?
So I copied that page from the members dir to the public dir and created the link from the buddy system to use that page. Works a treat.

Thanks for you the input guys. :rolleyes:
Back to Programming and General Web Design · Next Unread Topic →


1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users

  1. Pixel2Life Forum
  2. → Help Section
  3. → Programming and General Web Design