SQL interpretation needed, please

[ohduh!]

$product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

I'm still an SQL beginner and am confused about the "shorthand" used in the above code.

 

If the TABLE_PRODUCTS is the products table, what field is "p," ?

 

And in the TABLE_PRODUCTS_DESCRIPTION, what field of that table is "pd"?

 

Is there a file where those letters are defined? What am I missing here?

[Guest]

p and pd are used to reference the products and products_description tables respectively - they are declared within the query itself :D

 

Matti

[241]

It is a Matti has stated.

 

the query is declaring that TABLE_PRODUCTS is reference p and that TABLE_PRODUCTS_DESCRIPTION is referenced pd therefore a query containg for example p.products_id is refering to the product_id field in the products table which is TABLE_PRODUCTS like wise a pd.products_name would be refering to the products name field in the products description table which is TABLE_PRODUCTS_DESCRIPTION

 

so an example query of:

 

$product_name_query = tep_db_query("select p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd-products_id");

 

would check the table products for products id and the table products description for products name where the products id matches in both the products and products description tables.

[ohduh!]

Thanks for responding. Am still feeling a bit lost here.

 

How is

"select p.products_id, pd.products_name from " . TABLE_PRODUCTS

 

different from:

 

select products_id, products_name from TABLE_PRODUCTS

 

I guess I'm still not understanding where the p. and the pd. comes from. Are you saying that the programmer just arbitrarily makes up a definition?

 

If yes, Why?

 

And if yes, how does the program know where to get the data from the db? (There's no field or table called "p.products_id" in the db.)

[Rob123]

$product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

I'm still an SQL beginner and am confused about the "shorthand" used in the above code.

...

<{POST_SNAPBACK}>

 

Ok... let me try:

In the statement above, the alias (or nickname or reference) for the products table is "p". It "comes from" the fact that "p" is immediately after the "TABLE_PRODUCTS" reference in the code above.

 

You asked why would someone do this.... think of it as a shorthand that the coder made up so the code is shorter to write and may be easier to read.

 

Does that kind of make sense?

Hopefully I didn't make things worse.

Robert

[ohduh!]

In the statement above, the alias (or nickname or reference) for the products table is "p". It "comes from" the fact that "p" is immediately after the "TABLE_PRODUCTS" reference in the code above.

 

Ok, thanks! That kind of makes sense.

So TABLE_PRODUCTS . " p, " means "everytime there's a p, insert TABLE_PRODUCTS"?

 

Why is there a comma after the p?

 

So the alias in the example above only applies to the query or code that the alias sits in? Not anywhere else?

[Rob123]

So  TABLE_PRODUCTS . " p, "  means "everytime there's a p, insert TABLE_PRODUCTS"?

Yes, exactly

Why is there a comma after the p?

In SQL, commas are usually used to separate things. In this case, the comma separates the two tables of TABLE_PRODUCTS and TABLE_PRODUCTS_DESCRIPTION

So the alias in the example above only applies to the query or code that the alias sits in? Not anywhere else?

Yes.

 

HTH,

Robert

[ohduh!]

Thank you Robert!