Mysql syntax explanation

Question

I would like to know what a means. *, c.name, ... a.access, etc. In other words, what do I mean when I add the letter in front of the dot and the dot function.

Here is a sample code in which I found this event:

$query = "SELECT a.*, c.name as categoryname,c.id as categoryid, ". "c.alias as categoryalias, c.params as categoryparams". " FROM #__k2_items as a". " LEFT JOIN #__k2_categories c ON c.id = a.catid"; $query .= " WHERE a.published = 1" ." AND a.access <= {$aid}" ." AND a.trash = 0" ." AND c.published = 1" ." AND c.access <= {$aid}" ." AND c.trash = 0" ; 

Answer 1

If you look at the FROM , you will see the following:

 FROM #__k2_items as a 

And in the LEFT JOIN clause you will see the following:

 LEFT JOIN #__k2_categories c ON c.id = a.catid 

These are aliases of the table #__k2_items (no matter what it is actually called) for the names a and #__k2_categories to c , respectively. In this case, it is easy to maintain typing and improve readability of the request. Really,

A point associates a column name with a table name, so MySQL knows which tables to look for if you have columns with the same name in several tables participating in the query. That is, it eliminates the ambiguity of the column .

Answer 2

a.Something means the Something column in table a . In your application, you created aliases in your tables:

FROM #__k2_items as a LEFT JOIN #__k2_categories c

#__k2_items becomes an alias to a , and #__k2_categories becomes an alias to c .

So SELECT a.*, c.name as categoryname,c.id as categoryid, c.alias as categoryalias, c.params as categoryparams will select all fields from #__k2_items , and id , alias and params from #__k2_categories .

Hope this helps.

Answer 3

These are table names. Thus, you also map table names: a - all tables whose names correspond to #__ k2_items

Answer 4

The word before the dot is the name of the table, this is the word after the column name. In this example, a converted to #__k2_items , c to #__k2_categories . This is used for shorter and more readable SQL.

Answer 5

You are smoothing your tables / views. "FROM #__ k2_items like" means that #__ k2_items will be known as an alias of "a", so you don't have to use long table names.

It:

 SELECT a.*, c.name as categoryname,c.id as categoryid, ... 

means the same as this:

 SELECT #__k2_items.*, #__k2_categories.name as categoryname, #__k2_categories.id as categoryid, ... 

Answer 6

• a. * : select all fields from table a, which are actually "#__ k2_items" tables that have a different alias specified by the "as" keyword.
• c.name and other similar expressions: select the name field from the c table, which again is the #__ k2_categories table.
• c.alias as categoryalias and other similar expressions: renaming (pseudo-reduction) of the "alias" field in c.alias to "categoryalias".

In principle, as others have said:

• anything before the dot is the name of the table and anything after the name of the table is the name of the field.
• The 'as' operator used to change the name. This is useful for using a usable name as the result of an SQL query. For example, you can choose two middle ones from two tables, one of which is the average student of PhD, and the other is secondary school MSc, so you can change the name of the fields to make it easier to understand which average of this table.
• Sometimes, to simplify the syntax, especially when you select fields from many tables, you rename the table in the same way as for "a" and "c".

Answer 7

How to insert an entry into table # __k2_items:

 $db = &JFactory::getDBO(); $query = "INSERT INTO jos_k2_items (`title`, `alias`, `catid`, `published`, `introtext`, `fulltext`, `video`, `gallery`, `extra_fields`, `extra_fields_search`, `created`, `created_by`, `created_by_alias`, `checked_out`, `checked_out_time`, `modified`, `modified_by`, `publish_up`, `publish_down`, `trash`, `access`, `ordering`, `featured`, `featured_ordering`, `image_caption`, `image_credits`, `video_caption`, `video_credits`, `hits`, `params`, `metadesc`, `metadata`, `metakey`, `plugins`) VALUES ('".$title."', '".$title."', ".$catid.", 0, '<p>".$introtext."</p>', '', NULL, NULL, '".$extra_fields."', 'extra_fields_search', now(), 62, '', 0, '0000-00-00 00:00:00', '', 62, '', '0000-00-00 00:00:00', 0, 0, 5, 0, 0, '', '', '', '', 0, '', '', 'robots=\nauthor=', '', '') "; $db->setQuery($query); $db->query();