Formatting mySQL Statements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Formatting mySQL Statements

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Post 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.
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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`"
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
Post Reply