In this chapter, we will learn how to select data from a table.

SELECT statements retrieve selected rows. They can include UNION statements, an ordering clause, a LIMIT clause, a WHERE clause, a GROUP BY…HAVING clause, and subqueries.

Review the following general syntax −

SELECT field, field2,... FROM table_name, table_name2,... WHERE...

A SELECT statement provides multiple options for specifying the table used −

• database_name.table_name

• table_name.column_name

• database_name.table_name.column_name

All select statements must contain one or more select expressions. Select expressions consist of one of the following options −

• A column name.

• An expression employing operators and functions.

• The specification “table_name.*” to select all columns within the given table.

• The character “*” to select all columns from all tables specified in the FROM clause.

The command prompt or a PHP script can be employed in executing a select statement.

The Command Prompt

At the command prompt, execute statements as follows −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl
+-------------+---------------+
| ID_number   | Nomenclature  |
+-------------+---------------+
| 12345       | Orbitron 4000 |
+-------------+---------------+

PHP Select Script

Employ the same SELECT statement(s) within a PHP function to perform the operation. You will use the mysql_query() function once again. Review an example given below −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT product_id, product_name,product_manufacturer, ship_date FROM products_tbl
   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row[''product_id'']} <br> ".
         "Name: {$row[''product_name'']} <br> ".
         "Manufacturer: {$row[''product_manufacturer'']} <br> ".
         "Ship Date : {$row[''ship_date'']} <br>".
         "--------------------------------<br>";
   }

   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully

Best practices suggest releasing cursor memory after every SELECT statement. PHP provides the mysql_free_result() function for this purpose. Review its use as shown below −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl
   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Product ID :{$row[0]} <br> ".
         "Name: {$row[1]} <br> ".
         "Manufacturer: {$row[2]} <br> ".
         "Ship Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }

   mysql_free_result($retval);
   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

The WHERE clause provides a way to retrieve data when an operation uses an exact match. In situations requiring multiple results with shared characteristics, the LIKE clause accommodates broad pattern matching.

A LIKE clause tests for a pattern match, returning a true or false. The patterns used for comparison accept the following wildcard characters: “%”, which matches numbers of characters (0 or more); and “_”, which matches a single character. The “_” wildcard character only matches characters within its set, meaning it will ignore latin characters when using another set. The matches are case-insensitive by default requiring additional settings for case sensitivity.

A NOT LIKE clause allows for testing the opposite condition, much like the not operator.

If the statement expression or pattern evaluate to NULL, the result is NULL.

Review the general LIKE clause syntax given below −

SELECT field, field2,... FROM table_name, table_name2,...
WHERE field LIKE condition

Employ a LIKE clause either at the command prompt or within a PHP script.

The Command Prompt

At the command prompt, simply use a standard command −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from products_tbl
   WHERE product_manufacturer LIKE ''XYZ%
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

PHP Script Using Like Clause

Use the mysql_query() function in statements employing the LIKE clause

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl WHERE product_manufacturer LIKE "xyz%"

   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID:{$row[''product_id'']} <br> ".
         "Name: {$row[''product_name'']} <br> ".
         "Manufacturer: {$row[''product_manufacturer'']} <br> ".
         "Ship Date: {$row[''ship_date'']} <br> ".
         "--------------------------------<br>";
   }

   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully

In previous discussions and examples, we examined retrieving from a single table, or retrieving multiple values from multiple sources. Most real-world data operations are much more complex, requiring aggregation, comparison, and retrieval from multiple tables.

JOINs allow merging of two or more tables into a single object. They are employed through SELECT, UPDATE, and DELETE statements.

Review the general syntax of a statement employing a JOIN as shown below −

SELECT column
FROM table_name1
INNER JOIN table_name2
ON table_name1.column = table_name2.column;

Note the old syntax for JOINS used implicit joins and no keywords. It is possible to use a WHERE clause to achieve a join, but keywords work best for readability, maintenance, and best practices.

JOINs come in many forms such as a left join, right join, or inner join. Various join types offer different types of aggregation based on shared values or characteristics.

Employ a JOIN either at the command prompt or with a PHP script.

The Command Prompt

At the command prompt, simply use a standard statement −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed

mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct
   FROM products
   INNER JOIN inventory
   ON products.ID_numbeer = inventory.ID_number;
+-------------+----------------+-----------------+
| ID_number   | Nomenclature   | Inventory Count |
+-------------+----------------+-----------------+
| 12345       | Orbitron 4000  | 150             |
+-------------+----------------+-----------------+
| 12346       | Orbitron 3000  | 200             |
+-------------+----------------+-----------------+
| 12347       | Orbitron 1000  | 0               |
+-------------+----------------+-----------------+

PHP Script Using JOIN

Use the mysql_query() function to perform a join operation −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT a.product_id, a.product_manufacturer, b.product_count
      FROM products_tbl a, pcount_tbl b
      WHERE a.product_manufacturer = b.product_manufacturer

   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Manufacturer:{$row[''product_manufacturer'']} <br&gt ".
         "Count: {$row[''product_count'']} <br&gt ".
         "Product ID: {$row[''product_id'']} <br&gt ".
         "--------------------------------<br&gt";
   }

   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

On successful data retrieval, you will see the following output −

ID Number: 12345
Nomenclature: Orbitron 4000
Inventory Count: 150
--------------------------------------
ID Number: 12346
Nomenclature: Orbitron 3000
Inventory Count: 200
--------------------------------------
ID Number: 12347
Nomenclature: Orbitron 1000
Inventory Count: 0
--------------------------------------
mysql> Fetched data successfully

When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.

User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.

NULL is case-insensitive, and has the following aliases −

• UNKNOWN (a boolean value)
• N

NULL Operators

Standard comparison operators cannot be used with NULL (e.g., =, >, >=, <=, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<=>” (NULL-SAFE) operator.

Other available operators are −

• IS NULL − It tests for a NULL value.

• IS NOT NULL − It confirms the absence of a NULL value.

• ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.

• COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.

Sorting NULL Values

In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.

There are two ways to do this as shown below −

SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;

The other way −

SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;

NULL Functions

Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −

• IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.

• NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.

Functions like SUM and AVG ignore NULL values.

Inserting NULL Values

On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.

When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.

UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.

NULL Values and the Alter Command

When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values.

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.

MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more.

Review the use of standard REGEXP operator syntax given below −

SELECT column FROM table_name WHERE column REGEXP ''[PATTERN]

REGEXP returns 1 for a pattern match or 0 in the absence of one.

An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons.

The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “” characters. REGEXP is also case-insensitive, with the exception of binary strings.

A table of possible patterns, which can be used are given below −

Review the pattern matching examples given below −

Products starting with “pr” −

SELECT name FROM product_tbl WHERE name REGEXP ''^pr

Products ending with “na” −

SELECT name FROM product_tbl WHERE name REGEXP ''na$

Products starting with a vowel −

SELECT name FROM product_tbl WHERE name REGEXP ''^[aeiou]

Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database.

Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability) −

• Atomicity − It ensures the success of all operations by aborting on failures and rolling back changes.

• Consistency − It ensures the database applies changes on a successful transaction.

• Isolation − It enables independent transactions operation of transactions.

• Durability − It ensures the persistence of a successful transaction in the event of system failure.

At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements −

• START TRANSACTION begins the transaction.

• COMMIT saves changes to data.

• ROLLBACK ends the transaction, destroying any changes.

On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts.

Note − Some statements cause an implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP.

MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling access to tables during sessions to prevent modifications during certain time periods.

The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an explicit COMMIT statement.

Structure of a Transaction

The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations.

Review the example given below −

START TRANSACTION;
SELECT name FROM products WHERE manufacturer = ''XYZ Corp
UPDATE spring_products SET item = name;
COMMIT;

Before attempting to run MariaDB, first determine its current state, running or shutdown. There are three options for starting and stopping MariaDB −

• Run mysqld (the MariaDB binary).
• Run the mysqld_safe startup script.
• Run the mysql.server startup script.

If you installed MariaDB in a non-standard location, you may have to edit location information in the script files. Stop MariaDB by simply adding a “stop” parameter with the script.

If you would like to start it automatically under Linux, add startup scripts to your init system. Each distribution has a different procedure. Refer to your system documentation.

Creating a User Account

Create a new user account with the following code −

CREATE USER ''newusername''@''localhost'' IDENTIFIED BY ''userpassword

This code adds a row to the user table with no privileges. You also have the option to use a hash value for the password. Grant the user privileges with the following code −

GRANT SELECT, INSERT, UPDATE, DELETE ON database1 TO ''newusername''@''localhost

Other privileges include just about every command or operation possible in MariaDB. After creating a user, execute a “FLUSH PRIVILEGES” command in order to refresh grant tables. This allows the user account to be used.

The Configuration File

After a build on Unix/Linux, the configuration file “/etc/mysql/my.cnf” should be edited to appear as follow −

# Example mysql config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# /mysql-data-dir/my.cnf to get server specific options or
# ~/my.cnf for user specific options.

#

# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
#port = 3306
#socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
#port = 3306
#socket = /tmp/mysql.sock
temp-pool

# The following three entries caused mysqld 10.0.1-MariaDB (and possibly other
   versions) to abort...
# skip-locking
# set-variable = key_buffer = 16M
# set-variable = thread_cache = 4

loose-innodb_data_file_path = ibdata1:1000M
loose-mutex-deadlock-detector
gdb

######### Fix the two following paths

# Where you want to have your database
data = /path/to/data/dir

# Where you have your mysql/MariaDB source + sql/share/english
language = /path/to/src/dir/sql/share/english

[mysqldump]
quick
MariaDB
8
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash

[myisamchk]
set-variable = key_buffer = 128M

Edit the lines “data= ” and “language= ” to match your environment.

After file modification, navigate to the source directory and execute the following −

./scripts/mysql_install_db --srcdir = $PWD --datadir = /path/to/data/dir --
   user = $LOGNAME

Omit the “$PWD” variable if you added datadir to the configuration file. Ensure “$LOGNAME” is used when running version 10.0.1 of MariaDB.

Administration Commands

Review the following list of important commands you will regularly use when working with MariaDB −

• USE [database name] − Sets the current default database.

• SHOW DATABASES − Lists the databases currently on the server.

• SHOW TABLES − Lists all non-temporary tables.

• SHOW COLUMNS FROM [table name] − Provides column information pertaining to the specified table.

• SHOW INDEX FROM TABLENAME [table name] − Provides table index information relating to the specified table.

• SHOW TABLE STATUS LIKE [table name]G – − Provides tables with information about non-temporary tables, and the pattern that appears after the LIKE clause is used to fetch table names.

Creation or deletion of databases in MariaDB requires privileges, typically, only given to root users or admins. Under these accounts, you have two options for deleting a database: the mysqladmin binary and a PHP script.

Note that deleted databases are irrecoverable, so exercise care in performing this operation. Furthermore, PHP scripts used for deletion do not prompt you with a confirmation before the deletion.

mysqladmin binary

The following example demonstrates how to use the mysqladmin binary to delete an existing database −

[root@host]# mysqladmin -u root -p drop PRODUCTS
Enter password:******
mysql> DROP PRODUCTS
ERROR 1008 (HY000): Can''t drop database ''PRODUCTS database doesn''t exist

PHP Drop Database Script

PHP employs the mysql_query function in deleting MariaDB databases. The function uses two parameters, one optional, and returns either a value of “true” when successful, or “false” when not.

Syntax

Review the following drop database script syntax −

bool mysql_query( sql, connection );

The description of the parameters is given below −

Try the following example code for deleting a database −

<html>
   <head>
      <title>Delete a MariaDB Database</title>
   </head>

   <body>
      <?php
         $dbhost = ''localhost:3036
         $dbuser = ''root
         $dbpass = ''rootpassword
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die(''Could not connect: '' . mysql_error());
         }
         echo ''Connected successfully<br />

         $sql = ''DROP DATABASE PRODUCTS
         $retval = mysql_query( $sql, $conn );

         if(! $retval ){
            die(''Could not delete database: '' . mysql_error());
         }

         echo "Database PRODUCTS deleted successfullyn";
         mysql_close($conn);
      ?>
   </body>
</html>

On successful deletion, you will see the following output −

mysql> Database PRODUCTS deleted successfully

Creation or deletion of databases in MariaDB requires privileges typically only given to root users or admins. Under these accounts, you have two options for creating a database − the mysqladmin binary and a PHP script.

mysqladmin binary

The following example demonstrates the use of the mysqladmin binary in creating a database with the name Products −

[root@host]# mysqladmin -u root -p create PRODUCTS
Enter password:******

PHP Create Database Script

PHP employs the mysql_query function in creating a MariaDB database. The function uses two parameters, one optional, and returns either a value of “true” when successful, or “false” when not.

Syntax

Review the following create database script syntax −

bool mysql_query( sql, connection );

The description of the parameters is given below −

Try the following example code for creating a database −

<html>
   <head>
      <title>Create a MariaDB Database</title>
   </head>

   <body>
      <?php
         $dbhost = ''localhost:3036
         $dbuser = ''root
         $dbpass = ''rootpassword
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die(''Could not connect: '' . mysql_error());
         }

         echo ''Connected successfully<br />
         $sql = ''CREATE DATABASE PRODUCTS
         $retval = mysql_query( $sql, $conn );

         if(! $retval ) {
            die(''Could not create database: '' . mysql_error());
         }

         echo "Database PRODUCTS created successfullyn";
         mysql_close($conn);
      ?>
   </body>
</html>

On successful deletion, you will see the following output −

mysql> Database PRODUCTS created successfully
mysql> SHOW DATABASES;
+-----------------------+
| Database              |
+-----------------------+
| PRODUCTS              |
+-----------------------+

A database application exists separate from the main application and stores data collections. Every database employs one or multiple APIs for the creation, access, management, search, and replication of the data it contains.

Databases also use non-relational data sources such as objects or files. However, databases prove the best option for large datasets, which would suffer from slow retrieval and writing with other data sources.

Relational database management systems, or RDBMS, store data in various tables.Relationships between these tables are established using primary keys and foreign keys.

RDBMS offers the following features −

• They enable you to implement a data source with tables, columns, and indices.

• They ensure the integrity of references across rows of multiple tables.

• They automatically update indices.

• They interpret SQL queries and operations in manipulating or sourcing data from tables.

RDBMS Terminology

Before we begin our discussion of MariaDB, let us review a few terms related to databases.

• Database − A database is a data source consisting of tables holding related data.

• Table − A table, meaning a spreadsheet, is a matrix containing data.

• Column − A column, meaning data element, is a structure holding data of one type; for example, shipping dates.

• Row − A row is a structure grouping related data; for example, data for a customer. It is also known as a tuple, entry, or record.

• Redundancy − This term refers to storing data twice in order to accelerate the system.

• Primary Key − This refers to a unique, identifying value. This value cannot appear twice within a table, and there is only one row associated with it.

• Foreign Key − A foreign key serves as a link between two tables.

• Compound Key − A compound key, or composite key, is a key that refers to multiple columns. It refers to multiple columns due to a column lacking a unique quality.

• Index − An index is virtually identical to the index of a book.

• Referential Integrity − This term refers to ensuring all foreign key values point to existing rows.

MariaDB Database

MariaDB is a popular fork of MySQL created by MySQL”s original developers. It grew out of concerns related to MySQL”s acquisition by Oracle. It offers support for both small data processing tasks and enterprise needs. It aims to be a drop-in replacement for MySQL requiring only a simple uninstall of MySQL and an install of MariaDB. MariaDB offers the same features of MySQL and much more.

Key Features of MariaDB

The important features of MariaDB are −

• All of MariaDB is under GPL, LGPL, or BSD.

• MariaDB includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources.

• MariaDB uses a standard and popular querying language.

• MariaDB runs on a number of operating systems and supports a wide variety of programming languages.

• MariaDB offers support for PHP, one of the most popular web development languages.

• MariaDB offers Galera cluster technology.

• MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features impacting performance negatively.

Getting Started

Before you begin this tutorial, make sure you have some basic knowledge of PHP and HTML, specifically material discussed in our PHP and HTML tutorials.

This guide focuses on use of MariaDB in a PHP environment, so our examples will be most useful for PHP developers.

We strongly recommend reviewing our PHP Tutorial if you lack familiarity or need to review.