Okay I give up...

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
Glorius Hole
Registered User
Posts: 31
Joined: Sun Sep 18, 2005 5:39 pm

Okay I give up...

Post by Glorius Hole » Sat Jan 07, 2006 6:39 pm

... Why do the table collums have a prefix in them when you select?

Code: Select all

$sql = 'SELECT t.table_thingy
 t.table_thingy
t.table_thingy
 t.table_thingy
 t.table_thingy
t.table_thingy'
etc.
What's with the t.? I just don't get it.

User avatar
the_dan
Registered User
Posts: 700
Joined: Thu Apr 01, 2004 7:36 pm

Re: Okay I give up...

Post by the_dan » Sat Jan 07, 2006 6:51 pm

It shortens the query, and makes it easier to understand...

When I started working on my current non-community coding project, I didn't know about it, and as such, there are queries like this in my app:

Code: Select all

'SELECT '.POSTS_TABLE.'.post_id, '.POSTS_TABLE.'.post_text, '.POSTS_TABLE.'.post_deleted, '.POSTS_TABLE.'.topic_id, '.POSTS_TABLE.'.post_date, '.POSTS_TABLE.'.user_id, '.USERS_TABLE.'.user_name, '.USERS_TABLE.'.user_id, '.USERS_TABLE.'.user_sig, '.USERS_TABLE.'.dominant_group, '.USERS_TABLE.'.post_count, '.GROUPS_TABLE.'.group_colour, '.GROUPS_TABLE.'.group_image, '.GROUPS_TABLE.'.group_id, '.USERS_TABLE.'.user_avatar, '.EDITS_TABLE.'.edit_id, '.EDITS_TABLE.'.last_edit_user, '.EDITS_TABLE.'.last_edit_time 
		FROM '.POSTS_TABLE.'
		LEFT JOIN '.USERS_TABLE.' ON '.POSTS_TABLE.'.user_id = '.USERS_TABLE.'.user_id
		LEFT JOIN '.GROUPS_TABLE.' ON '.USERS_TABLE.'.dominant_group = '.GROUPS_TABLE.'.group_id 
		LEFT JOIN '.EDITS_TABLE.' ON '.POSTS_TABLE.'.post_id = '.EDITS_TABLE.'.post_id
			WHERE '.POSTS_TABLE.'.topic_id='.$topic_data['topic_id'].' '.$where.' 
		ORDER BY post_date ASC'
It works, but it's a mess.

Glorius Hole
Registered User
Posts: 31
Joined: Sun Sep 18, 2005 5:39 pm

Re: Okay I give up...

Post by Glorius Hole » Sat Jan 07, 2006 7:09 pm

Good god, it's so simple it's unbelievable. 8O I must be at the hieght of newbieness.

Still at least I'm learning. Thanks very much i appreciate the help, as always. :D

User avatar
the_dan
Registered User
Posts: 700
Joined: Thu Apr 01, 2004 7:36 pm

Re: Okay I give up...

Post by the_dan » Sat Jan 07, 2006 7:16 pm

I didn't know about it a few months back, and I like to think I'm a pretty reasonable coder :)
Just one of those things you pick up, and are all the better for.

Dan

BioALIEN
Registered User
Posts: 120
Joined: Tue Jan 25, 2005 3:46 pm
Location: London, UK

Re: Okay I give up...

Post by BioALIEN » Tue Jan 10, 2006 3:35 am

Glorius Hole wrote: ... Why do the table collums have a prefix in them when you select?

Code: Select all

$sql = 'SELECT t.table_thingy
 t.table_thingy
t.table_thingy
 t.table_thingy
 t.table_thingy
t.table_thingy'
etc.
What's with the t.? I just don't get it.

From my understanding, its to prepare the query when working on multiple tables. If you have the same attributes (columns) on two different database tables how would you tell which one's which?

There you go, you've answered your own question 8)
BioALIEN
█ Secure your name before someone else does: TheMillionDollarAdvert.com
█ Get your permanent listing from $10 USD. You too can own a piece of internet history.
The moment you master the art of programming is the moment you program the art itself! - BioALIEN

User avatar
pentapenguin
Registered User
Posts: 618
Joined: Sun Jul 18, 2004 6:07 pm
Location: GA, USA
Contact:

Re: Okay I give up...

Post by pentapenguin » Tue Jan 10, 2006 11:47 pm

It's a SQL alias.
After reading that article, you may wonder where the AS bit is?
The AS is optional but it never hurts to include it (I do to help readability).
So this

Code: Select all

$sql = 'SELECT t.* FROM table t'
can be rewritten as

Code: Select all

$sql = 'SELECT t.* FROM table AS t'
phpBB.com Support Team Member and Incident Investigation Team Member
My phpBB MODs || Professional phpBB Customization, Transfers, and Conversions

Alagba
Registered User
Posts: 68
Joined: Thu May 22, 2003 1:32 pm

Re: Okay I give up...

Post by Alagba » Wed Jan 11, 2006 12:17 am

Code: Select all

$sql = "SELECT 
	     a.story_id,
	     a.story_intro, 
	     a.story_body, 
	     u.username AS author_username,
	     u2.username AS informant_username
	FROM 
	     ". ARTICLES_TABLE ." a, 
	     ". ARTICLES_TOPICS_TABLE ." t
	LEFT JOIN 
             ". USERS_TABLE ." u ON (a.author_id = u.user_id)
	LEFT JOIN 
             ". USERS_TABLE ." u2 ON (a.story_informant = u2.user_id)				WHERE a.story_id = '$story_id'";
1. Using an alias for the table name shortens the code and makes it more readable.

2. An alias can be used to identity different copies of the same table (for
example USERS_TABLE) in the SQL query. This lets you differentiate the columns
with the same name in both tables.

Code: Select all

$sql = "SELECT
              f.staff_id,
	      COUNT(DISTINCT e.exam_id) AS total_exams
	  FROM
             ". EXAMS_TABLE ." e 
3. The COUNT() function introduces a new column to the query result. With the
alias we can easily refer to this column later. (for example: $row['total_exams'])

Post Reply