Okay I give up...

[Glorius Hole]

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

[the_dan]

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]

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.

[the_dan]

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]

... 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)

[pentapenguin]

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'

[Alagba]

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'])