CODEWITHSUNDEEP
mysql
Michael Grigsby
Michael Grigsby

Reputation: 12163

Understanding a MySQL query

I've been seeing queries like the one below more commonly now than ever. What I'm lost at is 'p.title', the 'as' clause, and 'FROM Posts p'. Anybody care to explain these parts to me or refer me to resources as I have no clue what to search for in Google?

SELECT p.title, SUBSTR(p.post,1,300) as post 
FROM Posts p 
WHERE p.title LIKE '%{$q}%' 
  OR p.post LIKE '%{$q}%'");

Upvotes: 0

Views: 137

Answers (9)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385144

FROM Posts p is short for FROM Posts AS p, which creates an alias for the table name in the query. It means you can write a slightly shorter query.

The author then backtracks slightly by writing out p.title for some reason, which here is equivalent to title (or Posts.title if he hadn't created the alias).

Upvotes: 0

GordonM
GordonM

Reputation: 31730

At first glance the query seems to implement a very simple search engine. SUBSTR(p.post,1,300) will return the first 200 characters of the post text. As the resultset would then have a column in it called SUBSTR(p.post,1,300) the additional AS clause will rename this column to post.

LIKE is a string matching function built into MySQL that lets you do simple pattern matching on strings. % is a wildcard matching any sequence of characters. $q is probably a placeholder that gets replaced with the string that will actually be searched for when the query is run for real.

Upvotes: 0

pinaldesai
pinaldesai

Reputation: 1815

p in your query is called Referal / Alias to the table. very comman in practice and useful when working with multiple tables having same field names.

more details are on

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p8.php

Upvotes: 1

Dennis C
Dennis C

Reputation: 13

You can use "AS" to make an alias (column name) which you can later retrieve in php $result['post']. The same goes for "Posts p" it is shorthand for "Posts as p" which in return lets you use p as the table name in the rest of the query

Upvotes: 0

CodeCaster
CodeCaster

Reputation: 151594

The dotted notation is in the format of table.column. The as keyword indicates an alias, you can select column1 as column456. FROM Posts p will let you use p as shorthand notation in the query when referring to the Posts table.

Upvotes: 0

Jan S
Jan S

Reputation: 1837

FROM Posts p - here, p is an alias of the table Posts. This means that in the rest of the query, you can use p instead of the table name Posts. Also you can also use FROM Posts as p as well.

Upvotes: 0

Rukmi Patel
Rukmi Patel

Reputation: 2561

it is alias of that table ..when I write query like select * from Post P means i have to write p. before each and every column of that table in particular that query... it is not that much confusing...

Upvotes: 0

Lars
Lars

Reputation: 5799

SELECT p.title, SUBSTR(p.post,1,300) AS post 
FROM Posts p 
WHERE p.title LIKE '%{$q}%' OR p.post LIKE '%{$q}%'"

As shown in the 2nd line, the "p" is an alias for the 'Posts'-Table. The AS-clause names the column in the result (in this case "post");

Upvotes: 2

BuZz
BuZz

Reputation: 17465

"p.title" is connected to "from Posts p". "p" is an alias. In the "from" segment, if you add such alias, you can refer everywhere in the query to columns of this table by using the alias prefix. That's useful for joins in particular, when you join on the same tables more than once or have columns with the same name in different tables.

the "as" is just about display. It will display the title specified after instead of the original column title in the database.

Upvotes: 1

Related Questions