Page 1 of 1

Formatting mySQL Statements

Posted: Wed Jan 18, 2006 6:34 am
by Slyvampy
Quick Question:

Is it better/worse/pointless for including the table name in the sql statement as a standard, even if not lisiting for an inner join.

example

option 1

Code: Select all

$get_price_info_list_returned = mysql_query ("
		SELECT bwc_products_price.products_price_id,
		   bwc_products_price.products_type_id,
		   bwc_products_price.products_price,
			bwc_products_price.products_price_quickfind_code,
			bwc_products_price.products_price_kfc_code,
			bwc_products_price.products_price_quantity,
			bwc_products_price.products_price_detail
		FROM bwc_products_price
		WHERE bwc_products_price.products_price_id = '$products_number_list_array[$random_number_for_products]' LIMIT 0,1", $connection) or die ("Error: home.php - bwc_products_price lookup");
OR

option 2

Code: Select all

$get_price_info_list_returned = mysql_query ("
		SELECT products_price_id,
		   products_type_id,
		   products_price,
			products_price_quickfind_code,
			products_price_kfc_code,
			products_price_quantity,
			products_price_detail
		FROM bwc_products_price
		WHERE bwc_products_price.products_price_id = '$products_number_list_array[$random_number_for_products]' LIMIT 0,1", $connection) or die ("Error: home.php - bwc_products_price lookup");
Yes there is no inner join here, however im trying to formulate a standard. is there any benifit of option 1?

Posted: Wed Jan 18, 2006 7:03 am
by Weirdan
I don't see the point in including the table name when there's only one table. On the other hand, I always include table names when there's more than one table used in query.

Posted: Wed Jan 18, 2006 7:55 am
by foobar
Weirdan wrote:I don't see the point in including the table name when there's only one table. On the other hand, I always include table names when there's more than one table used in query.
However, if you're lazy and you have several tables, you can use aliases.

Syntax:

Code: Select all

SELECT {field-list} FROM {table-name} AS {table-alias}
Where {table-alias} is freely chosen.

Posted: Wed Jan 18, 2006 8:22 am
by Slyvampy
Cheers for your responses,

my question is more from a standards issue
s it better/worse/pointless for including the table name in the sql statement as a standard, even if not lisiting for an inner join
More intrested in, if i was to create a standard for the company i work for, would you recommend I suggest to use the tablename.fieldname or just fieldname.

In regard to aliases, we see no point including this as we have properly formatted table structures.

Posted: Wed Jan 18, 2006 8:25 am
by foobar
Slyvampy wrote:In regard to aliases, we see no point including this as we have properly formatted table structures.
That's not the point. It just saves you the work of having to type/paste the table name every time.
Common practice is designating a single letter for each table.
It can be a lot easier to read if done properly, and a lot harder if done poorly.

Posted: Wed Jan 18, 2006 8:28 am
by JayBird
foobar wrote:It can be a lot easier to read if done properly, and a lot harder if done poorly.
Yeah, aliases are great and make it a lot more readble.

Just an example i pulled out of one of my scripts im working on

Code: Select all

SELECT `q`.`id` as `mainID`, `q`.`section`, `q`.`fieldType`, `a`.`qid`, `a`.`uid`, `a`.`answer` as `mainAnswer`
FROM `gradlife_-_gradlife`.`questions` as `q`, `gradlife_gradlife`.`answers` as `a`
WHERE`q`.`id` = `a`.`qid` AND `q`.`section` = '2'
GROUP BY `q`.`id`, `a`.`uid` ORDER BY `a`.`uid`"

Posted: Wed Jan 18, 2006 10:44 am
by raghavan20
There is no better way than aliases. Whenever you are dealing with more than one table that too particularly in JOIN statements, aliases save a lot of work and they are pretty clear and recognizable as well.