Common PHP MySQL Queries for Beginners and Advanced learners

Common MySQL Queries

Aggregates	Find child tables	What else did buyers of X buy?
Basic aggregation	Find cross-database foreign keys	Join or subquery?
Aggregate across columns	Find parent tables	Parents without children
Aggregates across multiple joins	Primary keys	Parties who have contracts with one another
Aggregates excluding leaders	Add auto-incrementing primary key to a table	The unbearable slowness of IN()
Aggregates of specified size	Auto-increment: reset next value	The [Not] Exists query pattern
All X for which all Y are Z	Find primary key of a table	What exams did a student not register for?
All X meeting multi-row conditions	Show	NULLs
Avoiding repeat aggregation	Show Create Trigger	List NULLs at end of query output
Break out sum on condition	Show Table Status equivalent from information_schema	Parents with and without children
Cascading aggregates	Show Tables	Ordering resultsets
Combine Group_Concat() with counts	Date and time	Emulate Row_Number()
Cross-aggregates	Age in years	Next row
Every paying customer wins a prize	Compute date from year, week number and weekday	Order by leading digits
Group data by datetime periods	Count business days between two dates	Order by month name
League table	Count Tuesdays between two dates	Order by numerics then alphas
Nested, banded aggregation	Date of first day of next month	Suppress repeating ordering values
Pairwise matchmaking	Date of first Friday of next month	Pagination
Show only one child row per parent row	Date of Monday in a given week of the year	Pagination
Skip repeating values	Date of Monday of this week	Relational division
Totals and subtotals--simply	Datetime difference	All possible recipes with given ingredients
Track state changes	Duration in years, months, days and time	All X for which all Y are Z (relational division)
Values linked with all values of another column	Julian date	Who makes all the parts for a given assembly?
Within-group aggregates	Last business day before a reference date	Sequences
Within-group aggregates with a wrinkle	Make a calendar table	Find adjacent unbooked theatre seats
Within-group quotas (Top N per group)	Scope to the week of a given date	Find average row-to-row time interval
Aggregates and Statistics	Sum accumulated time by date	Find blocks of unused numbers
Average the top 50% values per group	Sum time values	Find missing numbers in a sequence
Correlation	The date of next Thursday	Find missing values in a range
Count unique values of one column	Track when a value changed	Find previous and next values in a sequence
Median	What month does a week fall in?	Find row with next value of specified column
Mode	YearMonth()	Find sequence starts and ends
Rank order	Period arithmetic	Find specific sequences
Aggregates from bands of values	Audit trails and point-in-time architecture	Find the next value after a sequence
History of world records	Find overlapping periods	Gaps in a time series
Pivot tables (crosstabs)	Find sequenced duplicates	Make values of a column sequential
Pivot table basics: rows to columns	In or out at a given date and time?	Track stepwise project completion
Automate pivot table queries	Peak visit counts by datetime period	Winning Streaks
Group column statistics in rows	Sum for time periods	Spherical geometry
Monthly expenses	Scheduling	Great circle distance
Monthly sales	Appointments available	Statistics without aggregates
Pivot table using math tricks	Find available reservation periods	Moving average
Pivot table with CONCAT	Game schedule	Multiple sums across a join
Pivot table without GROUP_CONCAT	Is a given booking period available?	Percentiles
Data comparison	Pivot table schedule	Random row selection
Backslashes in data	Frequencies	Running sums, chequebooks
Compare data in two tables	Display column values which occur N times	Sum across categories
Show rows where column value changed	Display every Nth row	Top ten
Using bit operators	Graphs and Hierarchies	Stored procedures
Database metadata	Trees, networks and parts explosions in MySQL	A cursor if necessary, but not necessarily a cursor
Compare structures of two tables	Dijkstra’s shortest path algorithm	Emulate sp_exec
Compare two databases	Multiple trees in one table	Variable-length argument for query IN() clause
Database size	Tree query performance	Strings
Find the size of all databases on the server	Trees of known depth	Count delimited substrings
List databases, tables, columns	JOIN	Count substrings
List differences between two databases	Approximate joins	Levenshtein distance
List users of a database	Cascading JOINs	Proper case
Rename Database	Classroom scheduling	Retrieve octets from IP addresses
Replace text in all string columns of a database	Data-driven joins	Return digits or alphas from a string
Foreign keys	Full Outer Join	Split a string
Foreign key basics	Intersection and difference	Strip HTML tags
Change or drop a foreign key	Many-to-many joins

eMail  ITClassSL@gmail.com

Call  +94 777 33 7279

Skype ITClassSL



Common PHP MySQL Queries for Beginners and Advanced learners

PHP Resource

Basic syntax

Types

Variables

Constants

Expressions

Operators

Control Structures

Functions

Classes and Objects

Namespaces

Exceptions

Generators

References Explained

Predefined Variables

Predefined Exceptions

Predefined Interfaces and Classes

Context options and parameters

Supported Protocols and Wrappers

Security

Introduction

General considerations

Installed as CGI binary

Installed as an Apache module

Filesystem Security

Database Security

Error Reporting

Using Register Globals

User Submitted Data

Magic Quotes

Hiding PHP

Keeping Current

Features

HTTP authentication with PHP

Cookies

Sessions

Dealing with XForms

Handling file uploads

Using remote files

Connection handling

Persistent Database Connections

Safe Mode

Command line usage

Garbage Collection

DTrace Dynamic Tracing

Function Reference

Affecting PHP's Behaviour

Audio Formats Manipulation

Authentication Services

Command Line Specific Extensions

Compression and Archive Extensions

Credit Card Processing

Cryptography Extensions

Database Extensions

Date and Time Related Extensions

File System Related Extensions

Human Language and Character Encoding Support

Image Processing and Generation

Mail Related Extensions

Mathematical Extensions

Non-Text MIME Output

Process Control Extensions

Other Basic Extensions

Other Services

Search Engine Extensions

Server Specific Extensions

Session Extensions

Text Processing

Variable and Type Related Extensions

Web Services

Windows Only Extensions

XML Manipulation

 

 

 

 

 

12.7 Date and Time Functions

This section describes the functions that can be used to manipulate temporal values. See Section 11.3, “Date and Time Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.

Table 12.13 Date/Time Functions

Name	Description
ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
CONVERT_TZ()	Convert from one timezone to another
CURDATE()	Return the current date
CURRENT_DATE(), CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME	Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Synonyms for NOW()
CURTIME()	Return the current time
DATE_ADD()	Add time values (intervals) to a date value
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract a time value (interval) from a date
DATE()	Extract the date part of a date or datetime expression
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT()	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format UNIX timestamp as a date
GET_FORMAT()	Return a date format string
HOUR()	Extract the hour
LAST_DAY	Return the last day of the month for the argument
LOCALTIME(), LOCALTIME	Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()	Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME()	Create time from hour, minute, second
MICROSECOND()	Return the microseconds from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to 'HH:MM:SS' format
SECOND()	Return the second (0-59)
STR_TO_DATE()	Convert a string to a date
SUBDATE()	Synonym for DATE_SUB() when invoked with three arguments
SUBTIME()	Subtract times
SYSDATE()	Return the time at which the function executes
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIME()	Extract the time portion of the expression passed
TIMEDIFF()	Subtract time
TIMESTAMP()	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()	Add an interval to a datetime expression
TIMESTAMPDIFF()	Subtract an interval from a datetime expression
TO_DAYS()	Return the date argument converted to days
UNIX_TIMESTAMP()	Return a UNIX timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()	Return the calendar week of the date (0-53)
YEAR()	Return the year
YEARWEEK()	Return the year and week

 

 

Reference http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

Blog http://localedxcelcambridgeictcomputerclass.blogspot.com
Join Facebook Fan page https://www.facebook.com/pages/Itsoftware-Classin-Srilanka/380768322055448
Join Linkedin http://www.linkedin.com/pub/it-class-sri-lanaka/7b/948/a26
Join Yahoo Group http://groups.yahoo.com/neo/groups/itclasssl/info
Google plus https://plus.google.com/+itclassFaazSoftwareProjectsAssignmentsSriLanka
https://plus.google.com/+LocaledxcelcambridgeictcomputerclassBlogspot/posts
http://itcomputerclasslk.deviantart.com/
Wordpress http://computerclassinsrilanka.wordpress.com/author/itclasssl/
Website http://itcomputertuitionclass.site88.net/

BIT BSc HND Edxcel AS A2 Local A/L O/L ICT Classes Individual / Group / Online classes in English / Sinhala / Tamil.

Sample Projects/Assignments Exam Papers, Tutorials, Notes and Answers will be provided. Call+94 777 33 7279 | eMail itclasssl@gmail.com | Skype ITClassSL