I have a web based program that I am trying to create, in which I will have a user create a profile, which optionally includes a homepage URL and/or blog URL. Since not every user will have a blog or a homepage to enter, I have moved that information to a separate table. However, when displaying the user profile page, I will obviously need to display both, and would like to use as few queries as possible to keep the speed snappy. My questions are: What syntax should I use for my multiple table query? Will a JOIN be the fastest? Or would a simple multi-table query work just as quickly? What is the difference between using INNER and LEFT Joins? And finally, when I access the information using php fetch_row functions, how do I designate which piece of data I am trying to access, since the syntax is $selectedrow and I am selecting from multiple tables, which may have some overlap of fields?
Any help would be appreciated.
So my table structure looks kind of like this:
usertable ( userid char(20) NOT NULL PRIMARY KEY userfirstname char(50) NOT NULL userlastname char(50) NOT NULL useremail char(60) NOT NULL INDEX userrating int UNSIGNED NOT NULL userhandle char(50) NOT NULL INDEX usersignupdate datetime NOT NULL INDEX ) userhomepagetable ( userid char(20) UNSIGNED NOT NULL INDEX userhomepageurl char(100) NOT NULL userhomepagetitle char(80) NOT NULL ) userblogtable ( userid char(20) UNSIGNED NOT NULL INDEX userblogurl char(100) NOT NULL userblogtitle char(80) NOT NULL )