SQL syntax question with module

[jpweber]

The snippet of code in catalog/admin/categories.php on his instructions are at around line 385. On mine, because of my other contriubutions, it's around line 462. But whatever. The snippet of code starts out with:

 

$product_query = tep_db_query("select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, etc., etc., yada yada.

 

I mean, everything has a "p." before it. But the dimensions instructions given in the UPS module (which is highly respected on here) say to add:

products_length, products_width, products_height, products_ready_to_ship to this snippet of code.

 

Note that they don't have a "p." before them like the rest. Maybe this is by design, but should there be a "p." before each one of these, or did he give the proper installation instructions?

 

Thanks for any advice anyone can offer, as my PHP knowledge is limited at best.

[kgt]

In SQL, you can specify an 'alias' by which to refer to tables or field names. This is for convenience. In that SQL query, the products table has an alias of 'p'. When you use two tables in a query and they both have a field with the same name, you must specify which field you're talking about. You do this by using the following syntax:

 

table_name.field_name

 

Notice that the table products and the table products_description both have a field called products_id. So

 

products.products_id

 

means "use the products_id field in the products table." Since the products table has been given an alias of 'p', you can also just use:

 

p.products_id

 

to mean the same thing.

 

You can make it a habit to procede all field names in a query with the table name. This makes it easier when someone has to go back and read it to figure out what's going on. If the field name is not ambiguous however, you do not have to use the table name or the alias.

 

Since normally no other OSCommerce table has a field called products_length, you don't have to use the table name. MySQL understands that you must mean products_length in the products table, since that's the only table in the query that has a field called products_length. So, any one of the following are correct in this case:

 

p.products_length

products.products_length

products_length

[jpweber]

Well stated, KG, and I thank you very much for that. I'm learning more and more about PHP every day because of nice people like you and Monika, among others. That said, I now have my UPS module up and functioining well now. Thank you for taking the time out of your day to respond, teach me a little bit about PHP, and help me out in the process. Have a good one!