MySQL CONCATE_WS() Function

CONCATE_WS()

This function was used to join two or more string with separator to make a single string.
SELECT CONCAT_WS(‘,’, id, country_code, country_name)
FROM apps_countries
where country_name =’Pakistan’

Description

In the above example, was used as a separator and it join the id, country_name and country _code and make a single string.

Output

CONCAT_WS(‘,’,id,country_code,country_name)
167,PK,Pakistan

MySQL REGEXP Functions

REGEXP

The regular expression was used to match a string expression against a pattern. The pattern is used as an argument. If expression or pattern is null then, the function return null.
SELECT * FROM apps_countries WHERE country_name REGEXP’^A’;

Description

In the above example the regular expression matches only string that start with A.

Output

The output of the above program will be:
id  country_code   country_name
1    AF                               Afghanistan
2    AL                              Albania
3    DZ                             Algeria
4    DS                             American Samoa
5     AD                           Andorra

Example#2

SELECT * FROM apps_countries WHERE country_name REGEXP ‘na’;

Description

In the above example the query will find all name which contains ‘na’ in the string.

Output

id   country_code   country_name
10   AR                        Argentina
27   BA                        Bosnia and Herzegovina
28    BW                       Botswana
34    BF                         Burkina Faso
38    CA                         Canada
44    CN                         China
68     FK                          Falkland Islands (Malvinas)
74     GF                          French Guiana
81     GH                           Ghana
86     GD                           Grenada
92     GY                            Guyana
94     HM                           Heard and Mc Donald Islands
145    MC                           Monaco

Example#3

SELECT * FROM apps_countries WHERE country_name REGEXP ‘^[aeiou]’

Description

In the above example the query will show only string that that’s stars from vowel words.

Output

id   country_code   country_name
1     AF                      Afghanistan
2    AL                      Albania
3    DZ                      Algeria
4    DS                      American Samoa
5    AD                      Andorra
6   AO                       Angola
7   AI                        Anguilla
8   AQ                      Antarctica
9   AG                       Antigua and Barbuda
10  AR                      Argentina
11  AM                      Armenia
12  AW                     Aruba
13  AU                      Australia
14  AT                      Austria
15  AZ                      Azerbaijan
60  TP                      East Timor
61  EC                       Ecuador
62  EG                      Egypt
63  SV                      El Salvador
64  GQ                       Equatorial Guinea

MySQL MATCH() Functions

MATCH()

SELECT * FROM apps_countries
WHERE MATCH(country_code, country_name) AGAINST(‘Bangladesh’ IN BOOLEAN MODE)

Description

In this function we can search a value using match() and against() in Boolean mode. It can match the column from our table against the value which can we given.

Output

The output of the above program will be:
id   country_code   country_name
18     BD                     Bangladesh

Example#2

SELECT country_code FROM apps_countries
WHERE MATCH(country_code, country_name) AGAINST(‘pakistan’ IN BOOLEAN MODE)

Description

In the above example the query will search the country code against the country name.

Output

The output of the above example will be:
country_code
PK

MySQL LCASE() & UCASE() Functions

LCASE()

The LCASE() convert the character of string into lowercase.

$sql = “SELECT country_code, LCASE(\’BAHRAIN\’)\n”
. ” FROM `apps_countries`\n”
.”where country_name=\’Bahrain\'”;

Description

The above example converts the BAHRAIN into lower character.

Output

The output of the above program will be:
country_code     LCASE(‘BAHRAIN’)
BH                               bahrain

UCASE()

The UCASE() convert the character of string into uppercase.

$sql = “SELECT UCASE(country_name)\n”
. “FROM apps_countries”;

Description

The above example converts the BAHRAIN into lower character.

Output

UCASE(country_name)

AFGHANISTAN

ALBANIA

ALGERIA

AMERICAN SAMOA

ANDORRA

ANGOLA

ANGUILLA

ANTARCTICA

ANTIGUA AND BARBUDA

ARGENTINA

ARMENIA

ARUBA

AUSTRALIA

AUSTRIA

AZERBAIJAN

BAHAMAS

BAHRAIN

BANGLADESH

BARBADOS

BELARUS

BELGIUM

BELIZE

BENIN

BERMUDA

BHUTAN

BOLIVIA

MySQL MD5() Functions

MD5()

MySQL MD5() Calculates an MD5 128-bit checksum for a string. The value is returned as a binary string of 32 hex digits, or NULL if the argument was NULL. The example of MD5() function are given below:
$sql = “SELECT MD5(‘AZAM’)”;

Description

The above example returns the string of 32 hex digit. If we give the argument NULL then it give the output NULL.

Output

The output of the above function will be:
MD5(‘AZAM’)
f30f2b83d482145c20dd590110d7ad15

MySQL Date Functions

1. Date()

$sql = “SELECT DATE(from_date) FROM salaries”;

Description

The DATE() function extracts the date part of a date.

Output

Output of the above example will be:
DATE(from_date)
2016-10-03
2016-10-03
2016-11-02

2. DATE_ADD()

$sql = “SELECT  DATE_ADD(FROM_date,INTERVAL 30 DAY) FROM salaries”;

Description

The DATE_ADD() function adds a specified time interval to a date.

Output

Output of the above example will be:
DATE_ADD(FROM_date,INTERVAL 30 DAY)
2016-11-02
2016-11-02
2016-12-02

MySQL String Function

CONCAT()

$sql = ” SELECT  CONCAT(country_code,\’–> \’,country_name)\n”
. “FROM apps_countries”;

Description

MySQL CONCAT function is used to concatenate two strings to form a single string. In this example it can concat the country code and country name and give the result in single string.

Output

The output of the above example will be like this:
AF–> Afghanistan
AL–> Albania
DZ–> Algeria
DS–> American Samoa
AD–> Andorra
AO–> Angola
AI–> Anguilla
AQ–> Antarctica
AG–> Antigua and Barbuda
AR–> Argentina
AM–> Armenia
AW–> Aruba
AU–> Australia
AT–> Austria
AZ–> Azerbaijan
BS–> Bahamas
BH–> Bahrain
BD–> Bangladesh
BB–> Barbados
BY–> Belarus
BE–> Belgium
BZ–> Belize
BJ–> Benin
BM–> Bermuda

PHP array functions with mysql

1.array_diff()

add_action(‘init’, ‘fixing’);
function fixing(){

$mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

$arr1 = array(“a” => “Cat”,”b” => “Dog”, “c” => “Horse”);
extract($arr1);
echo”\$a = $a; \$b = $b; \$c = $c”;
pre($arr1);
global $wpdb;
$result=$mysqli->query(“SELECT * from wp_posts where post_type=’post'”);

while ($row = $result->fetch_object()){
$arr = (array)$row;
$diff = array_diff($arr, $arr1);
pre($diff);

}

exit;

}

Description

Array_diff() takes a values from database and compare these value, and give the different vaule.The same values or data will be eliminated.

2.array_intesect

add_action(‘init’, ‘fixing’);
function fixing(){

$mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

$arr1 = array(“a” => “Cat”,”b” => “Dog”, “c” => “Horse”);
extract($arr1);
echo”\$a = $a; \$b = $b; \$c = $c”;
pre($arr1);

global $wpdb;
$result=$mysqli->query(“SELECT * from wp_posts where post_type=’post'”);

while ($row = $result->fetch_object()){
$arr = (array)$row;
$diff = array_intersect($arr, $arr1);
pre($diff);

}

exit;

}

Description

In this example array_intersect() compare the value and he match value.

MySQL Query Function in WordPress

SELECT

global $wpdb;
$result = $wpdb->get_results (

SELECT *
FROM wp_posts
WHERE post_title = ‘Hello world!’

);

print_r($result);

Description:

WordPress provides a class wpdb which is present in the file – wp-includes\wp-db.php. This class abstracts the database functions for WordPress and most WordPress functions directly or indirectly use this class. This class is based on the ezSQL class. You can create an object of this class to perform database operations but WordPress creates an object of this class during the load of WordPress. This query give the output of Hello world! which can be found from the table wp_posts.

Another Example of SELECT Query

global $wpdb;
$sql=”SELECT * from wp_posts where post_type=’post'”;
$results = $wpdb->get_results($sql) or die(mysql_error());
if (count($results) > 0) {
$display_row = null;
foreach ($results as $res) {
print_r($result);

Description

In this example we used a foreach loop. It can check the post_type from the table wp_posts, and show the result which can be true.

Update

$update_post=array(‘ID’=>’1’, ‘post_title’=>’Andriod Bubbes’);
$result=wp_update_post($update_post);
print_r($result);

Description

wp_update allow you to easily update your wordpress theme. It will update a single id at a time. In the above example we can update the post_title, which have ID=1. It update our table according to our ID, which we given.

DELETE

lobal $wpdb;
$result= $wpdb->delete( ‘wp_posts’, array( ‘ID’ => 1 ) );
print_r($result);

Description

This query will delete the post which have id=1. It will delete only post which we define by id.