How to Connect PHP to A phpMyAdmin MySQL Database Easily

Introduction to phpMyAdmin

A free PHP application named phpMyAdmin manages MySQL administration tasks in a web browser. Its GUI simplifies MySQL database management compared to the command line. Although it has a simple UI, you may run SQL statements directly. PhpMyAdmin controls databases, tables, fields, relations, indexes, users, and permissions.

PHP and MySQL are needed for phpMyAdmin. It is often included with Apache, MySQL/MariaDB, and PHP development packages like XAMPP or EasyPHP. PHP and MySQL can be used locally after setting up an environment and starting the database server.

In most circumstances, you can use your browser to access phpMyAdmin. It usually requires a login to access. Initial installs, such as those found in certain packages, may utilise the root account by default, which poses a security concern. It is highly advised that phpMyAdmin be secured using a root user password, which may be accomplished via the phpMyAdmin interface.

As a utility, phpMyAdmin creates and runs SQL commands for you in the background. It is nevertheless helpful to understand the underlying SQL. The administration tool offers a straightforward method for visualising the objects, such tables, and the organisation of your database.

Using phpMyAdmin to Create a Database

The online interface of phpMyAdmin provides a simple way to create databases. Numerous databases can be supported by a MySQL database system. Typically, each application will have its own database. The usual steps involved are as follows:

1. Launch your browser and visit phpMyAdmin. First, you may need to log in.
2. From the top menu, select the Databases tab.
3. Find the “Create new database” part (usually a box) and enter the database name you want. Cars, simpledb, logindb, admintable, postaldb, finalpostal, estatedb, msgboarddb, and customdb are a few examples of database names .
4. It is advised that the database’s collation be changed. One potential of confusion is the default latin1_swedish_ci. utf8-general-ci is a better choice. Among other data properties, collations establish character kinds and sorting capabilities.
5. Press the button that says “Create.”

You ought to get a confirmation message if the creation was successful. The precise SQL command was actually run in the background for you by phpMyAdmin when you clicked the button while creating a database using this GUI technique. The SQL command CREATE DATABASE dbname; is the same as this action.

Another way to establish a database is to type the establish DATABASE command straight into the phpMyAdmin SQL box. When creating a database and a user with rights at the same time, this way may be a little faster.

The corresponding SQL code to create a database called Cars is as follows:

CREATE DATABASE Cars;

Using phpMyAdmin to Create Tables

Tables can be added to a database after it has been constructed. Several tables with entries make up a database. A database entry is represented by each table row, and specific information is represented by each column. A graphical interface for specifying the table structure is offered by phpMyAdmin. Following are the steps:

1. Click the database name to choose it. The SQL instruction USE dbname; is equal to this action.
2. A page for the chosen database will be shown by phpMyAdmin. Find the area where a new table can be created.
3. Give your table a name. Users, INVENTORY, Book_Reviews, Books, Customers, Order_Items, and Orders are a few examples.
4. Indicate how many columns your table will contain.
5. Depending on the version of phpMyAdmin, click the Go button (or Create or Submit).
6. After that, an interface for defining each table column will be shown to you. You usually supply the following for each column:
   User_id, first_name, last_name, email, password, or registration_date are columns.

Columns: Columns can be INT, MEDIUMINT, VARCHAR, CHAR, or DATETIME. You can also define new database tables using table commands, naming each segment a column and data type.

Length/Value: The size or maximum length, particularly for string types like CHAR or VARCHAR, is known as the length/value.
◦ Null Property: Indicate if NULL values are allowed in the column. The NOT NULL keyword specifies values that must be entered when adding information.
◦ Index: Explain what an index is. Choose PRIMARY for a primary key. MySQL is instructed which segment to use as a key field using the PRIMARY KEY keyword.

◦ A_I (Auto Increment): Select this option to allow MySQL to automatically assign a distinct, incremental value for every new record for numerical primary keys like MEDIUMINT. Ignoring this step can result in an error when attempting to input a second record, stating that you are attempting to create a duplicate value of 0 for user_id. The system will ask you to choose PRIMARY when you click A_I.
Other qualities that are frequently used with integer primary keys include UNSIGNED, which is used for positive-only numeric values.

7. Click the Save button once every column and its properties have been defined.

In the background, phpMyAdmin runs a CREATE TABLE SQL statement in accordance with your instructions. Additionally, you can use the SQL window to directly construct tables. According to some publications, you should test both the SQL and GUI approaches to see which is simpler.

To create a users table, phpMyAdmin would run the following SQL code

CREATE TABLE users (
    user_id MEDIUMINT (6) UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    email VARCHAR(50) NOT NULL,
    password CHAR(60) NOT NULL,
    registration_date DATETIME,
    PRIMARY KEY (user_id)
);

By selecting the SQL tab in phpMyAdmin, you can directly enter and run these SQL instructions. Additional helpful SQL statements that you may use in phpMyAdmin include SHOW CREATE TABLE and SHOW TABLES, which list the tables in the current database. tablename; to view the SQL query used to generate a particular table.

PHP Code Examples (Programmatic Creation)

PHP itself can be used to connect to MySQL and run SQL statements programmatically, although phpMyAdmin is a graphical user interface (GUI) application for database administration.

This is how phpMyAdmin, a PHP tool, is constructed. Examples of using the mysqli extension to connect to a MySQL database:

<?php
// Database access details
DEFINE ('DB_USER', 'your_username'); // e.g., 'root' or 'william'  or 'webmaster'  or 'turing' 
DEFINE ('DB_PASSWORD', 'your_password'); // e.g., 'mypassword'  or 'Cat0nlap'  or 'C0ffeep0t' [31] or 'En1gm3' 
DEFINE ('DB_HOST', 'localhost'); // or 'www.yourwebsite.com' for a remote host 
DEFINE ('DB_NAME', 'your_database'); // Specify DB if connecting to existing one, e.g., 'Cars' or 'logindb' 
// Make the connection:
$dbcon = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); // Using mysqli, the latest construct 
// Set the encoding (optional but recommended)
mysqli_set_charset($dbcon, 'utf8'); // Using utf8 
// Basic error checking example (based on similar error handling concepts)
if ($dbcon->connect_error) { // Checks if the connection failed 
    die('Connect Error (' . $dbcon->connect_errno . ') ' . $dbcon->connect_error); // Stops script execution and prints an error 
}
echo 'Connected successfully to mySQL. <BR>'; // Similar confirmation shown
?>

Once a connection has been made, you can use PHP to build a database by running the build DATABASE SQL command through the connection object:

<?php
// Assuming $dbcon is your valid mysqli connection object as shown above
$sql_create_db = "CREATE DATABASE NewDatabaseName"; // The SQL command to create a database
if ($dbcon->query($sql_create_db) === TRUE) { // Execute the query using the connection object
    echo "<p>Database NewDatabaseName created successfully</p>"; // Based on output shown for successful creation 
} else {
    echo "Error creating database: " . $dbcon->error; // Based on error handling concepts and mysqli methods
}
?>

Likewise, you use the connection to run the construct TABLE SQL query in order to programmatically construct a table:

<?php
// Assuming $dbcon is your valid mysqli connection object, and the database is selected or specified in the connection
$sql_create_table = "CREATE TABLE NewTable (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
)"; // The SQL command to create a table
if ($dbcon->query($sql_create_table) === TRUE) { // Execute the query
    echo "Table NewTable created successfully"; // Based on similar successful output concepts
} else {
    echo "Error creating table: " . $dbcon->error; // Based on error handling concepts and mysqli methods
}
?>

PHP script (createdb.php) that shows how to programmatically automate the construction of a database and tables. This demonstrates that although phpMyAdmin provides a user-friendly graphical user interface, PHP code may also be used to carry out the underlying functions.