Multiple Tables Select and Display

[nomadhacker]

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['name'] 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:

 

(Toggle Plain Text)

 

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

)

 

[teng84]

one huge query will perform faster.. because it takes several condition in your coding to get what you really want

Left join > will return all the records from the first table even if it doesnt find matches on the second table

Inner join> it will only return the rows of each tables that matches your joining condition

 

maybe

select * from usertable
inner join userhomepagetable on userhomepagetable. userid   = usertable.userid
inner join userblogtable on userblogtable.userid  = usertable.userid
//where condition here 

 

to retrieve data look read <a href="http://www.w3schools.com/php/php_mysql_select.asp">here</a>

[nomadhacker]

So between the two, I would definitely need to use a LEFT JOIN instead of an INNER JOIN, since I want to return the user info, regardless of whether the homepage exists. 

 

Do you think it would be faster or more efficient to include the homepage and blog info in the main user table and just allow NULL values?

 

I discovered that I can use aliases in the sql statement to make referencing the fields from php easier, so that part is no longer a problem.

 

Thank you for your reply.  I appreciate the help.