Jump to content


Photo

Join tables


  • Please log in to reply
3 replies to this topic

#1 chris_davidsonuk

chris_davidsonuk
  • Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 12:29 PM

I am creating a music website. Users must be verified by the admin before their songs are published on the site. I have 2 tables:

USERS
- Username
- Status (This is set to 1 if verified 0 if not)

TRACKS
- Username

I am wanting to query the database and show the tracks (from the tracks table) by users that have been verified (taken from the users table).

I have looked at many tutorials but can't get this to word. Does anybody know the correct syntax for this query?

Any help much appreciated.


---------------------------------------
[a href="http://www.studioeight.net" target="_blank"]www.studioeight.net[/a]

#2 Techbot

Techbot
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 27 August 2006 - 03:32 PM

Is this what you are after?

SELECT tracks.*, status FROM tracks, users where user.status = 1;

ps I don't see why username is in the tracks database should it be songname?

#3 Techbot

Techbot
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 27 August 2006 - 03:36 PM

If you are using Username in both tables to do the join then try

Select * FROM tracks NATURAL JOIN users;

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 10:14 PM

Where are the UIDs? Tell me your primary key isn't a (VAR)CHAR field... that's a no-no, at least for MyISAM tables.  Besides, you should use a proper JOIN:

SELECT tracks.*FROM tracks INNER JOIN users ON ( tracks.username = user.username ) where user.status = 1;

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users