MariaDB – Select Database

MariaDB – Select Database ”; Previous Next After connecting to MariaDB, you must select a database to work with because many databases may exist. There are two ways to perform this task: from the command prompt or through a PHP script. The Command Prompt In choosing a database at the command prompt, simply utilize the SQL command ‘use’ − [root@host]# mysql -u root -p Enter password:****** mysql> use PRODUCTS; Database changed mysql> SELECT database(); +————————-+ | Database | +————————-+ | PRODUCTS | +————————-+ Once you select a database, all subsequent commands will operate on the chosen database. Note − All names (e.g., database, table, fields) are case sensitive. Ensure commands conform to the proper case. PHP Select Database Script PHP provides the mysql_select_db function for database selection. The function uses two parameters, one optional, and returns a value of “true” on successful selection, or false on failure. Syntax Review the following select database script syntax. bool mysql_select_db( db_name, connection ); The description of the parameters is given below − S.No Parameter & Description 1 db_name This required parameter specifies the name of the database to use. 2 connection When not specified, this optional parameter uses the most recent connection used. Try the following example code for selecting a database − <html> <head> <title>Select a MariaDB Database</title> </head> <body> <?php $dbhost = ”localhost:3036”; $dbuser = ”guest1”; $dbpass = ”guest1a”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } echo ”Connected successfully”; mysql_select_db( ”PRODUCTS” ); mysql_close($conn); ?> </body> </html> On successful selection, you will see the following output − mysql> Connected successfully Print Page Previous Next Advertisements ”;

MariaDB – Drop Database

MariaDB – Drop Database ”; Previous Next 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 − Sr.No Parameter & Description 1 sql This required parameter consists of the SQL query needed to perform the operation. 2 connection When not specified, this optional parameter uses the most recent connection used. 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 Print Page Previous Next Advertisements ”;

MariaDB – Create Tables

MariaDB – Create Tables ”; Previous Next In this chapter, we will learn how to create tables. Before creating a table, first determine its name, field names, and field definitions. Following is the general syntax for table creation − CREATE TABLE table_name (column_name column_type); Review the command applied to creating a table in the PRODUCTS database − databaseproducts_ tbl( product_id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, product_manufacturer VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( product_id ) ); The above example uses “NOT NULL” as a field attribute to avoid errors caused by a null value. The attribute “AUTO_INCREMENT” instructs MariaDB to add the next available value to the ID field. The keyword primary key defines a column as the primary key. Multiple columns separated by commas can define a primary key. The two main methods for creating tables are using the command prompt and a PHP script. The Command Prompt Utilize the CREATE TABLE command to perform the task as shown below − root@host# mysql -u root -p Enter password:******* mysql> use PRODUCTS; Database changed mysql> CREATE TABLE products_tbl( -> product_id INT NOT NULL AUTO_INCREMENT, -> product_name VARCHAR(100) NOT NULL, -> product_manufacturer VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( product_id ) -> ); mysql> SHOW TABLES; +————————+ | PRODUCTS | +————————+ | products_tbl | +————————+ Ensure all commands are terminated with a semicolon. PHP Create Table Script PHP provides mysql_query() for table creation. Its second argument contains the necessary SQL command − <html> <head> <title>Create a MariaDB Table</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 TABLE products_tbl( “. “product_id INT NOT NULL AUTO_INCREMENT, “. “product_name VARCHAR(100) NOT NULL, “. “product_manufacturer VARCHAR(40) NOT NULL, “. “submission_date DATE, “. “PRIMARY KEY ( product_id )); “; mysql_select_db( ”PRODUCTS” ); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not create table: ” . mysql_error()); } echo “Table created successfullyn”; mysql_close($conn); ?> </body> </html> On successful table creation, you will see the following output − mysql> Table created successfully Print Page Previous Next Advertisements ”;

MariaDB – Home

MariaDB Tutorial PDF Version Quick Guide Resources Job Search Discussion MariaDB is a fork of the MySQL relational database management system. The original developers of MySQL created MariaDB after concerns raised by Oracle”s acquisition of MySQL. This tutorial will provide a quick introduction to MariaDB, and aid you in achieving a high level of comfort with MariaDB programming and administration. Audience This tutorial targets novice developers and those new to MariaDB. It guides them in understanding basic through more advanced concepts in MariaDB. After completing this tutorial, your firm foundation in MariaDB and level of expertise will allow you to begin developing and easily build on your knowledge. Prerequisites The tutorial assumes your familiarity with relational database management systems, querying languages, MySQL, and general programming. It also assumes familiarity with typical database operations in an application. Print Page Previous Next Advertisements ”;

MariaDB – PHP Syntax

MariaDB – PHP Syntax ”; Previous Next MariaDB partners well with a wide variety of programming languages and frameworks such as PHP, C#, JavaScript, Ruby on Rails, Django, and more. PHP remains the most popular of all available languages due to its simplicity and historical footprint. This guide will focus on PHP partnered with MariaDB. PHP provides a selection of functions for working with the MySQL database. These functions perform tasks like accessing it or performing operations, and they are fully compatible with MariaDB. Simply call these functions as you would call any other PHP function. The PHP functions you will use for MariaDB conform to the following format − mysql_function(value,value,…); The second part of the function specifies its action. Two of the functions used in this guide are as follows − mysqli_connect($connect); mysqli_query($connect,”SQL statement”); The following example demonstrates the general syntax of a PHP call to a MariaDB function − <html> <head> <title>PHP and MariaDB</title> </head> <body> <?php $retval = mysql_function(value, [value,…]); if( !$retval ) { die ( “Error: Error message here” ); } // MariaDB or PHP Statements ?> </body> </html> In the next section, we will examine essential MariaDB tasks, using PHP functions. Print Page Previous Next Advertisements ”;

MariaDB – Installation

MariaDB – Installation ”; Previous Next All downloads for MariaDB are located in the Download section of the official MariaDB foundation website. Click the link to the version you would like, and a list of downloads for multiple operating systems, architectures, and installation file types is displayed. Installing on LINUX/UNIX If you have intimate knowledge of Linux/Unix systems, simply download source to build your install. Our recommended way of installing is to utilize distribution packages. MariaDB offers packages for the following Linux/Unix distributions − RedHat/CentOS/Fedora Debian/Ubuntu The following distributions include a MariaDB package in their repositories − openSUSE Arch Linux Mageia Mint Slackware Follow these steps to install in an Ubuntu environment − Step 1 − Login as a root user. Step 2 − Navigate to the directory containing the MariaDB package. Step 3 − Import the GnuPG signing key with the following code − sudo apt-key adv –recv-keys –keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db Step 4 − Add MariaDB to the sources.list file. Open the file, and add the following code − sudo add-apt-repository ”deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntuprecise main” Step 5 − Refresh the system with the following − sudo apt-get update Step 6 − Install MariaDB with the following − sudo apt-get install mariadb-server Installing on Windows After locating and downloading an automated install file (MSI), simply double click the file to start the installation. The installation wizard will walk you through every step of installation and any necessary settings. Test the installation by starting it from the command prompt. Navigate to the location of the installation, typically in the directory, and type the following at the prompt − mysqld.exe –console If the installation is successful, you will see messages related to startup. If this does not appear, you may have permission issues. Ensure that your user account can access the application. Graphical clients are available for MariaDB administration in the Windows environment. If you find the command line uncomfortable or cumbersome, be sure to experiment with them. Testing the Installation Perform a few simple tasks to confirm the functioning and installation of MariaDB. Use the Admin Utility to Get Server Status View the server version with the mysqladmin binary. [root@host]# mysqladmin –version It should display the version, distribution, operating system, and architecture. If you do not see the output of that type, examine your installation for issues. Execute Simple Commands with a Client Bring up the command prompt for MariaDB. This should connect you to MariaDB and allow execution of commands. Enter a simple command as follows − mysql> SHOW DATABASES; Post- Installation After successful installation of MariaDB, set a root password. A fresh install will have a blank password. Enter the following to set the new password − mysqladmin -u root password “[enter your password here]”; Enter the following to connect to the server with your new credentials − mysql -u root -p Enter password:******* Upgrading on Windows If you already have MySQL installed on your Windows system, and want to upgrade to MariaDB; do not uninstall MySQL and install MariaDB. This will cause a conflict with the existing database. You must instead install MariaDB, and then use the upgrade wizard in the Windows installation file. The options of your MySQL my.cnf file should work with MariaDB. However, MariaDB has many features, which are not found in MySQL. Consider the following conflicts in your my.cnf file − MariaDB uses Aria storage engine by default for temporary files. If you have a lot of temporary files, modify key buffer size if you do not use MyISAM tables. If your applications connect/disconnect frequently, alter the thread cache size. If you use over 100 connections, use the thread pool. Compatibility MySQL and MariaDB are essentially identical. However, there are enough differences to create issues in upgradation. Review more of these key differences in the MariaDB Knowledge Base. Print Page Previous Next Advertisements ”;