Importing Wordpress Site
From Aikiframework wiki
|
Importing Wordpress sites is not really accurate. Rather, with Aiki we can directly read content commonly stored in a database and use it simply. Thus, to read a website that is based upon wordpress. This is a guide to reading posts and displaying them with Aiki Framework.
Using Aiki to display Wordpress Posts
This section covers how to install aiki ontop of an existing wordpress installation or importing a wordpress posts table into an aiki site. Moderate knowledge of SQL is prefered, but not required.
If you are able to have multiple databases on a single host, with the same login credentials, then the database name can preceed sql select statements. Optionally, Wordpress tables my be imported into the Aiki database using a tool such as phpmyadmin or from the mysql cli application.
For example we have a Wordpress database named 'mywp_1' , and would like to select all published posts, ordered by date (newest first) from the table wp_1posts:
select post_title, post_content, post_date from mywp_1.wp_1posts where post_status = 'publish' and post_type != 'page' ORDER BY post_date DESC
The retrieved fields above can be used to build the page. If you get fancier, you can join multiple tables to extract data to display, and recreate an entire Wordpress site to a new or existing specification.
<div class="post" id="((id))"> <h2>((post_title))</h2> <small>((post_date))</small> <div class="entry"> ((post_content)) </div> </div>
Also to mention here is issues with pagination. Pay close attention to the 'Records in Page' widget field, and the link example field, as they must contain valid entries for pagination to function, otherwise all records are returned to the screen unless a 'LIMIT' option is specified in the sql select statement.
Setting up automatic URLs - URL Processing with Aiki
This section will explain how to set up a widget to retrieve a post based on a url that is passed to the widget. With this you can simply type in the post_name and retrieve the selected post!
SELECT post_content, post_title, post_type,id FROM mywp_florica.wp_1posts WHERE post_status = 'publish' and post_name= '(!(0)!)' or post_name = '(!(1)!)' and post_status= 'publish'
The above checks the url entered. '(!(0)!)' is aiki markup for the first section of the url, while '(!(1)!)' is the second ... ex:
http://www.blog.com/category/a_post [root] = www.blog.com (!(0)!) = category (!(1)!) = a_post
We are comparing both values incase a user enters a url such as /category/film/a_post , will return the same as /category/a_post or /category/a_post/whatever You could do this to make your sites 'prettier'. Feel free to further customize your match options using URL's. You can the above to filter /retrieve categories as well when combined with the statement that retrieves a posts' category.
Accessing a Post Category
After an hour or two I was able to craft this SQL statement that will return a posts' category, along with the fields in the query above. This assumes your post contains only one category...
SELECT post_title, post_content, post_date, name as category
FROM mywp_1.wp_1posts posts join mywp_1.wp_1term_relationships rel on posts.id = rel.object_id
join mywp_1.wp_1term_taxonomy tax on tax.term_taxonomy_id = rel.term_taxonomy_id
join mywp_1.wp_1terms term on term.term_id = tax.term_id
WHERE post_status = 'publish' and post_type != 'page' ORDER BY post_date DESC
This statement makes use of a MySQL shorthand to alias a table name to reduce clutter, simply append the name of the alias after the table name in the "FROM" clause to refer to that table by the alias throughout the rest of the sql statement. You could also use the word 'as'; select statement aliases the column 'name' as 'category' to ease confusion in the Aiki markup.
As you may have noticed we are accessing three tables to determine what the category of a the post is using joins that link each table to the next to retrieve the category name. You can now work with the aiki markup variable ((category))
Potentially you could continue adding joins to this statement until the point you have access to every field that is associated with the post.. more on this in the future... One must simply have to find the corresponding fields to each table.
Accessing Wordpress Pages and Children
This section covers how to select Wordpress Pages and Children, and includes examples of different SQL calls that can be made to refine and combine records and tables.
This statement selects posts that are published (no need to post a revision/etc), and are 'Pages'. A sub query retrieves the correct id to be matched to select the children of the page category 'film', if a page hierarchy is present.
SELECT id, post_name, post_parent, post_title, post_content, post_date FROM `mywp_1.wp_1posts` WHERE post_type="page" and post_status="publish" and post_parent = (select id from mywp_1.wp_1posts WHERE post_name = 'film')
This statement will not account for multiple levels of hierarchy. This may be a task that a plug-in may handle better.
Otherwise you would match posts to the name of the post using where post_name="name of page" This statement selects a page by name, the page name (for example is) a_page
SELECT id, post_name, post_parent, post_title, post_content, post_date FROM `mywp_1.wp_1posts` WHERE post_type="page" and post_status="publish" and post_name ="a_page"
Alternatively you can access it by the 'post_title' field, below, we are accessing "A Page"
SELECT id, post_name, post_parent, post_title, post_content, post_date FROM `mywp_1.wp_1posts` WHERE post_type="page" and post_status="publish" and post_title ="A Page"
Maybe you want to include more than one page by using the or operator;
SELECT id, post_name, post_parent, post_title, post_content, post_date FROM `mywp_1.wp_1posts` WHERE post_type="page" and post_status="publish" and post_title ="A Page" or post_title="Another Page" or post_title="Yet Another One"
More SQL Handy SQL Statements
This statement returns all post categories's slug's, feel free to select other variables as well, but this was created to get a list of the category slugs.
SELECT distinct slug
FROM mywp_1.wp_1posts posts join mywp_1.wp_1term_relationships rel on posts.id = rel.object_id
join mywp_1.wp_1term_taxonomy tax on tax.term_taxonomy_id = rel.term_taxonomy_id
join mywp_1.wp_1terms term on term.term_id = tax.term_id
WHERE post_status = 'publish' and post_type != 'page' and taxonomy = "category" ORDER BY post_date DESC
This one is really cool, this one will return 'url's as generated by a post category and post_name.
SELECT CONCAT_WS('/',slug,post_name) as url
FROM mywp_1.wp_1posts posts join mywp_1.wp_1term_relationships rel on posts.id = rel.object_id
join mywp_1.wp_1term_taxonomy tax on tax.term_taxonomy_id = rel.term_taxonomy_id
join mywp_1.wp_1terms term on term.term_id = tax.term_id
WHERE post_status = 'publish' and post_type != 'page' and taxonomy = "category" ORDER BY post_date DESC
