Explain this short SQL query to me please

Question

 "SELECT * from posts A, categories B where A.active='1' 
  AND A.category=B.CATID order by A.time_added desc 
  limit $pagingstart, $config[items_per_page]";

I think it says selects the rows from the 'posts' table such that the active entry in each row is equal to 1 but I don't understand the rest. Please explain. Thank you.

Answer 1

It selects the columns from Posts (referred to with the alias "A"), and the associated for from Categories (referred to as "B") for each post, where:

• Posts.Active = 1
• The post's category exist in the "Categories" table (if a post doesn't have a matching category in this table, the row won't be returned)
• Orders the results by A.Time_added (in decending order, newest to oldest)
• Returns just "$config[items_per_page]" rows, starting with "$pagingstart"

I'm not sure what brand of SQL this is, as I don't recognize the limit statement or the $variables, but that's the gist.

Answer 2

It looks like this is trying to select all active posts, order them with the newest at the top, and limit the number of records to fit on a page. The semantics of A.active='1' probably mean that the post is active, but I'm guessing.

It looks like MySQL with PHP.

Answer 3

This selects entries from posts and categories, joining them together where posts.category=categories.CATID. It filters out all rows where posts.active!=1, and then orders by descending posts.time_added, returning at most $config[items_per_page] items starting from $pagingstart.

Answer 4

It selects all the active posts (and their category), newest first. However, it has a paging mechanism, so it shows only $config[items_per_page] posts starting at number $pagingstart.

Answer 5

Select the rows from the posts table and the categories table, joined into a single table by the category ID (using what I call a lazy join, but that may just be my opinion and I'm not really a database guy), sorted in descending order by the time added, displaying only $items_per_page records starting at $pagingstart.

Answer 6

You'll get rows

• from A and B that where category and CATID match ("intersection" bit of a Venn Diagram)

• The rows for A are filtered to those where Active = 1

• sorted by time_added. latest first

• limit says y rows startig at row x. x and y are determined by the sort

Answer 7

It select all columns from table posts and categories where posts.active is equal 1 and where posts.category is joined to the categories.catid and this is ordered by posts.time_added a limit start and end is set by the two variables $pagingstart, $config[items_per_page]

Answer 8

It's saying:

1) Select everything from both Posts & Categories where Posts.Active = 1 and Posts.Category = Category.CATID.
2) The Order by statement then specifies that they should be presented (from top to bottom) with the newest Post.Time_Added first.
3) Finally, the limit clause says (I think, I don't use limit very often): Only grab $spagingstart (a variable which has been set at some point) number of items, and only display $config[items_per_page] at a time.

Answer 9

posts A, categories B is a such called "implicit JOIN". It returns all possible combinations of records from A and B which are later filtered by the WHERE conditions.

Explicit join syntax is much more readable:

SELECT  *
FROM    posts A
JOIN    categories B
ON      B.CATID = A.category
WHERE   A.active='1' 
ORDER BY
        A.time_added DESC
LIMIT   $pagingstart, $config[items_per_page]

This means: "for each record from A, take all records from B whose catid is the same as A's category".

ORDER BY A.time_added DESC makes your posts to return from latest to earliest.

LIMIT 100, 10 makes the query to return only posts from 100th to 110th.