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

Share

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.

Share

How To Reset Admin Password – Joomla

1) Login to phpmyadmin and select db and the table (jos_users) that
store users details   ie table with name end with “_users”2) Make sure that the username  exists in the table( let the user name be ‘admin’)

3) Execute the below given query  UPDATE jos_users
SET password=MD5(‘new password’) WHERE username = “admin”;

Share

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;
}
?>

Share

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>

Share

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 } ?>

Share