Database queries

robertgreen · July 16th, 2010, 06:39 AM

Hi,

I've found this book immensely helpful towards understanding what makes Wordpress tick. You guys are great.

I've got a question about the MySQL command line examples on page 113:

Code:

SELECT wc.* FROM wp_posts wp 
INNER JOIN wp_comments wc ON wp.ID = wc.comment_post_ID 
WHERE wp.ID = â1554â

I didn't understand the part about wc.* because there don't seem to be any tables or columns with a wc- prefix. Couldn't get anything useful with Googling it either. I think I'm lost in figuring this one out.

Thanks for any help.

williamsba · July 17th, 2010, 11:12 AM

Hi Robert,

wc in the query example you posted is a short way of referencing the wp_comments table in your query. In that example we are also referencing wp_posts using wp. This is just a quick and easy way to make a short reference to a table name. Here's another example:

PHP Code:


			
SELECT * FROM wp_posts wp WHERE wp.ID = '5'

Notice how I set wp directly after I define the table name to access. Once I have defined wp I can use it going forward (wp.ID is the same as wp_posts.ID).

robertgreen · July 17th, 2010, 12:24 PM

Hi Brad,

Firstly, is that the same as:

Code:

SELECT * FROM wp_posts AS wp WHERE wp.ID = '5'

Secondly, what does the .* after

Code:

SELECT wc.*

do?

Thanks for answering my questions, I'm very new to all this database stuff.

williamsba · July 17th, 2010, 12:29 PM

Yes they are the same, it's basically an alias for the table name.

wc.*, or just *, selects all fields from a given table. So this query:

PHP Code:


			
SELECT * FROM wp_posts

Would retrieve all records and all fields in the wp_posts table. Generally it's best to define exactly what fields you need for optimization reasons like so:

PHP Code:


			
SELECT ID, post_title FROM wp_posts

robertgreen · July 17th, 2010, 12:38 PM

Thanks, Brad. That was helpful. :)