Tag Archives: Query

SQL

SQL referred to as Structured Query Language) is a special-purpose programming language designed for managing data in relational database management systems (RDBMS).
Originally based upon relational algebra and tuple relational calculus its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

SELECT – extracts data from a database
UPDATE – updates data in a database
DELETE – deletes data from a database
INSERT INTO – inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE – creates a new database
ALTER DATABASE – modifies a database
CREATE TABLE – creates a new table
ALTER TABLE – modifies a table
DROP TABLE – deletes a table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index

MySQL

MySQL is a relational database management system (RDBMS)that runs as a server providing multi-user access to a number of databases. It is named after co-founder Michael Widenius’ daughter, My. The SQL phrase stands for Structured Query Language.
The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.
Free-software-open source projects that require a full-featured database management system often use MySQL. For commercial use, several paid editions are available, and offer additional functionality.

WordPress query to fetch posts orders by date added as custom field

<?php
$postQuery = “SELECT distinct(wposts.post_title),cast(wpostmeta.meta_key as DATE),wposts.ID,wposts.post_content,wposts.post_date,wposts.comment_count
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->postmeta AS wpostmeta ON wposts.ID = wpostmeta.post_id
WHERE wpostmeta.meta_key = ‘dvd_release_date’ AND $wpdb->term_taxonomy.taxonomy = ‘category’ AND $wpdb->term_taxonomy.term_id IN(‘3952’)
ORDER BY wpostmeta.meta_value DESC LIMIT $itemPerPage”;

$postResults = $wpdb->get_results($postQuery, OBJECT);

foreach( $postResults as $post ) {
echo $post->post_title;
}
?>

WordPress query to fetch links under a given category

<?php

// ‘links’ is the slug name of the link category link

$linkeQry=”SELECT * FROM wp_links as link INNER JOIN wp_term_relationships ON (link.link_id = wp_term_relationships.object_id)

INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) AND wp_term_taxonomy.taxonomy = ‘link_category’

INNER JOIN {$wpdb->prefix}terms as c ON c.term_id=wp_term_taxonomy.term_id

WHERE c.slug=’links'”;

$linksData = $wpdb->get_results($linkeQry);

?>

<ul>

<?php foreach($linksData as $key=>$link) {  ?>

<li><a href=”<?php echo $link->link_url; ?>” target=”_blank”><?php echo $link->link_name; ?></a></li>

<?php } ?>

</ul>

WordPress query to list archives

<?php

$archiveQry= “SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts

FROM $wpdb->posts  GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC “;

$archivesData = $wpdb->get_results($archiveQry); foreach($archivesData as $key=>$archive) {  ?>

<a href=””><?php echo date(“M”, mktime(0, 0, 0,$archive->month, 1, $archive->year));  ?>

<?php echo $archive->year  ?> </a>

<?php } ?>