Posted on Sep 24

How to Build a MySQL Web CLI

Creating a MySQL web CLI can be a powerful way to manage your database from any web browser. Using a combination of PHP, HTML, CSS, and JavaScript, you can build a secure, lightweight, and user-friendly interface that mimics a traditional terminal. This article will guide you through the process, using the provided code to explain each component and how they work together.

The Core Concept

The fundamental principle behind this web CLI is a client-server architecture. The browser acts as the client, handling the user interface and input. It sends commands to the server, which is a PHP script. The PHP script, in turn, connects to the MySQL database, executes the command, and sends the result back to the browser for display.

Step 1: Setting up the Project

You'll need a web server with PHP and a MySQL database. A local development environment like XAMPP, WAMP, or MAMP is ideal.

Start by creating a project folder (e.g., mysqlwebcli) in your web server's root directory. Inside this folder, you will place all the necessary files.

The project relies on the phpdotenv library to handle secure database credentials. Navigate to your project directory in the terminal and install it using Composer:

composer require vlucas/phpdotenv

This command creates a vendor directory and an autoload.php file, which your PHP scripts will use to access the library.

Next, create a .env file in the root directory and add your database credentials. This file keeps your sensitive information separate from your code.

.env

DB_SERVERNAME="localhost" DB_USERNAME="your_username" DB_PASSWORD="your_password" DB_NAME="your_database"

Step 2: Building the Interface (HTML & CSS)

The HTML file, index.html, provides the structure for the CLI. It includes a command output area and an input section, styled to look like a terminal.

index.html

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Shell-style MySQL CLI</title> <link rel="stylesheet" href="style.css"> </head> <body> <div class="dos-container"> <div class="cli-output" id="output"> <pre>$ Welcome to the MySQL CLI.</pre> <pre>$ Type 'help' to see all available commands.</pre> </div> <div class="cli-input-container"> <pre id="cli-prompt">$</pre> <input type="text" id="command-input" autofocus> </div> </div> <input type="file" id="sql-file-input" accept=".sql" style="display: none;"> <div id="modal" class="modal"> <div class="modal-content"> <span class="close-btn" id="close-modal">&times;</span> <pre id="modal-content"></pre> <p style="color:red; text-align:center; font-size:12px;">For security, this file is read-only. Edit on the server directly.</p> </div> </div> <script src="script.js"></script> </body> </html>

The style.css file transforms this structure into a sleek, dark-themed command-line interface. It uses monospaced fonts, vibrant green text, and a flexbox layout to create a full-screen, responsive design.

style.css

html, body { height: 100%; margin: 0; padding: 0; overflow: hidden; /* Prevents unwanted scrollbars on the main page */ } body { background-color: #000; color: #00ff00; /* Classic green text on black */ font-family: 'Consolas', 'Courier New', Courier, monospace; display: flex; justify-content: center; align-items: center; } .dos-container { width: 95%; /* Fits the browser window */ height: 95%; /* Fits the browser window */ border: none; /* No border for a cleaner look */ padding: 10px; box-shadow: none; /* No box shadow */ background-color: #000; display: flex; flex-direction: column; } .cli-output { flex-grow: 1; /* Makes this section fill all available vertical space */ overflow-y: auto; white-space: pre-wrap; word-wrap: break-word; margin-bottom: 5px; padding-right: 15px; border: none; /* No border for the output area */ scrollbar-color: #00ff00 #000; scrollbar-width: thin; } .cli-output pre { margin: 0; padding-left: 2px; } .cli-input-container { display: flex; align-items: center; gap: 5px; padding-top: 5px; } .cli-input-container pre { margin: 0; color: #00ff00; font-weight: bold; } #command-input { flex-grow: 1; background-color: transparent; border: none; color: #00ff00; font-family: 'Consolas', 'Courier New', Courier, monospace; font-size: 1em; outline: none; padding: 0; caret-color: #00ff00; /* Blinking green cursor */ } /* Custom scrollbar for WebKit browsers */ .cli-output::-webkit-scrollbar { width: 10px; } .cli-output::-webkit-scrollbar-track { background: #000; } .cli-output::-webkit-scrollbar-thumb { background-color: #00ff00; border-radius: 5px; border: 2px solid #000; } pre { font-weight: bold; } /* --- Modal Styles --- */ /* The Modal (background) */ .modal { display: none; /* Hidden by default */ position: fixed; /* Stay in place */ z-index: 1; /* Sit on top of everything */ left: 0; top: 0; width: 100%; /* Full width */ height: 100%; /* Full height */ overflow: auto; /* Enable scroll if needed */ background-color: rgba(0,0,0,0.8); /* Black with opacity */ } /* Modal Content */ .modal-content { background-color: #000; border: 2px solid #00ff00; margin: 15% auto; /* 15% from the top and centered */ padding: 20px; width: 80%; /* Could be more or less, depending on screen size */ max-width: 600px; color: #00ff00; font-family: 'Consolas', 'Courier New', Courier, monospace; position: relative; } .modal-content pre { white-space: pre-wrap; word-wrap: break-word; max-height: 400px; overflow-y: auto; border: none; /* Make the pre tag inside the modal also borderless */ } /* The Close Button */ .close-btn { color: #00ff00; position: absolute; top: 10px; right: 20px; font-size: 28px; font-weight: bold; transition: all 0.2s ease-in-out; } .close-btn:hover, .close-btn:focus { color: red; text-decoration: none; cursor: pointer; } p { margin: 0; }

Step 3: Client-Side Logic (JavaScript)

The script.js file is the brain of the front end. It handles user input, manages the application's state, and communicates with the server.

Prompt Management: The script uses currentPrompt and currentDB variables to dynamically update the prompt ($, $msql>, or mysql>db>) as the user navigates through sessions and databases.
Command Parsing: It listens for user commands and uses regular expressions to match different input formats like start [db_name], q <- [query], or json<-q<- [query].
API Calls: Based on the command, it makes a fetch request to the appropriate PHP endpoint (execute.php, execute_json.php, etc.), sending the command and the current database name in the request body.
Special Commands: Commands like help and sysconfig are handled directly in the browser to reduce server load. The sysconfig command fetches the .env file content and displays it in a read-only modal for security.
File Uploads: When the upload sql command is entered, the script programmatically triggers a click on a hidden file input. Once a file is selected, it uses the FormData API to send the file to upload_sql.php.
Error Handling: The script provides user-friendly error messages if a command is not found or a server request fails.

script.js

document.addEventListener('DOMContentLoaded', () => { const commandInput = document.getElementById('command-input'); const outputDiv = document.getElementById('output'); const promptElement = document.getElementById('cli-prompt'); const modal = document.getElementById('modal'); const modalContent = document.getElementById('modal-content'); const closeModalBtn = document.getElementById('close-modal'); const sqlFileInput = document.getElementById('sql-file-input'); let currentPrompt = '$'; let currentDB = ''; commandInput.focus(); const updatePrompt = () => { if (currentDB) { promptElement.textContent = `mysql>${currentDB}>`; } else if (currentPrompt === '$msql>') { promptElement.textContent = `$msql>`; } else { promptElement.textContent = `$`; } }; const helpGuide = ` Available Commands: - help | h | hlp : Show this help guide. - mysql : Start a MySQL session. - exit : Exit the current MySQL session. - sysconfig : View the contents of the .env file. (Read-only) --- MySQL Session Commands --- - start [db_name] : Select a database to work with. - close | close mydb : Close the current database connection. - drop [db_name] | del [db_name] : Permanently delete a database. - drop [tbl_name] | delete [tbl_name] : Permanently delete a table. - list mydb | ls mydb : List all databases and their sizes. - list table | ls table : List all tables in the current database. - q <- [SQL query] : Execute an SQL query and display results in a table. - json<-q<- [SQL query]: Execute an SQL query and display results in JSON format. - upload sql : Upload and execute a .sql file. - bkp [db_name] | backup [db_name] : Create and download a backup of a database. Note: Commands are case-insensitive. --- For Support and Feedback --- - Visit: https://iquipedigital.com - Email: support@iquipedigital.com - GitHub: https://github.com/iquipe/mysqlwebcli - License: MIT License `; const executeCommand = async () => { const fullCommand = commandInput.value.trim(); if (fullCommand === '') { return; } outputDiv.innerHTML += `\n<pre>${promptElement.textContent} ${fullCommand}</pre>`; const lowerCaseCommand = fullCommand.toLowerCase(); let commandToSend = ''; let endpoint = 'execute.php'; let isBackupCommand = false; const startRegex = /^start\s+(\S+)$/i; const backupRegex = /^(bkp|backup)\s+(\S+)$/i; const jsonQueryRegex = /^json<-q<-\s*(.+)$/i; const sqlQueryRegex = /^q\s*<-\s*(.+)$/i; const dropDbRegex = /^(drop|delete|del)\s+(database|db)?\s*(\S+)$/i; const dropTableRegex = /^(drop|delete)\s+(\S+)$/i; const startMatch = lowerCaseCommand.match(startRegex); const backupMatch = lowerCaseCommand.match(backupRegex); const jsonMatch = fullCommand.match(jsonQueryRegex); const sqlMatch = fullCommand.match(sqlQueryRegex); const dropDbMatch = lowerCaseCommand.match(dropDbRegex); const dropTableMatch = lowerCaseCommand.match(dropTableRegex); if (lowerCaseCommand === 'help' || lowerCaseCommand === 'h' || lowerCaseCommand === 'hlp') { outputDiv.innerHTML += `<pre>${helpGuide}</pre>`; } else if (lowerCaseCommand === 'sysconfig') { try { const response = await fetch('get_env.php'); const envContent = await response.text(); modalContent.textContent = envContent; modal.style.display = 'block'; } catch (error) { outputDiv.innerHTML += `<pre style="color: red;">Error: Could not retrieve .env file content.</pre>`; } } else if (backupMatch) { const dbName = backupMatch[2]; endpoint = 'backup.php'; commandToSend = dbName; isBackupCommand = true; outputDiv.innerHTML += `<pre>Starting backup of database '${dbName}'...</pre>`; } else if (lowerCaseCommand === 'mysql') { currentPrompt = '$msql>'; outputDiv.innerHTML += `<pre>MySQL session started. Type 'start [database name]' to select a database.</pre>`; } else if (startMatch) { const dbName = startMatch[1]; currentDB = dbName; currentPrompt = '$msql>'; outputDiv.innerHTML += `<pre>Database changed to '${dbName}'.</pre>`; } else if (lowerCaseCommand === 'exit') { currentPrompt = '$'; currentDB = ''; outputDiv.innerHTML += `<pre>MySQL session terminated.</pre>`; } else if (lowerCaseCommand === 'close' || lowerCaseCommand === 'close mydb') { if (currentDB) { outputDiv.innerHTML += `<pre>Closed database '${currentDB}'.</pre>`; currentDB = ''; } else { outputDiv.innerHTML += `<pre>No database is currently open.</pre>`; } } else if (lowerCaseCommand === 'list mydb' || lowerCaseCommand === 'ls mydb') { commandToSend = 'SPECIAL_COMMAND_LIST_DB'; } else if (lowerCaseCommand.match(/^(list|ls)\s+(table|tbl)$/)) { if (currentDB) { commandToSend = 'SPECIAL_COMMAND_LIST_TABLES'; } else { outputDiv.innerHTML += `<pre style="color: red;">No database selected. Use 'start [database name]' first.</pre>`; } } else if (lowerCaseCommand === 'upload sql') { sqlFileInput.click(); outputDiv.innerHTML += `<pre>Please select a .sql file to upload...</pre>`; } else if (dropDbMatch) { const dbName = dropDbMatch[3]; if (confirm(`WARNING: This will permanently delete the database '${dbName}'. Are you sure?`)) { endpoint = 'execute.php'; commandToSend = `DROP DATABASE \`${dbName}\``; outputDiv.innerHTML += `<pre>Dropping database '${dbName}'...</pre>`; if (currentDB === dbName) { currentDB = ''; } } else { outputDiv.innerHTML += `<pre>Deletion canceled.</pre>`; } } else if (dropTableMatch && currentPrompt.startsWith('$msql>') && currentDB) { const tableName = dropTableMatch[2]; if (confirm(`WARNING: This will permanently delete the table '${tableName}' from database '${currentDB}'. Are you sure?`)) { endpoint = 'execute.php'; commandToSend = `DROP TABLE \`${tableName}\``; outputDiv.innerHTML += `<pre>Dropping table '${tableName}'...</pre>`; } else { outputDiv.innerHTML += `<pre>Deletion canceled.</pre>`; } } else if (currentPrompt.startsWith('$msql>') && jsonMatch) { commandToSend = jsonMatch[1].trim(); endpoint = 'execute_json.php'; } else if (currentPrompt.startsWith('$msql>') && sqlMatch) { commandToSend = sqlMatch[1].trim(); endpoint = 'execute.php'; } else { outputDiv.innerHTML += `<pre style="color: red;">Command not found. Type 'help' to see available commands.</pre>`; } updatePrompt(); if (commandToSend) { try { const response = await fetch(endpoint, { method: 'POST', headers: { 'Content-Type': 'application/x-www-form-urlencoded', }, body: `command=${encodeURIComponent(commandToSend)}&db=${encodeURIComponent(currentDB)}` }); if (isBackupCommand) { const blob = await response.blob(); const url = window.URL.createObjectURL(blob); const a = document.createElement('a'); a.style.display = 'none'; a.href = url; a.download = `backup_${commandToSend}.sql`; document.body.appendChild(a); a.click(); window.URL.revokeObjectURL(url); outputDiv.innerHTML += `<pre>Backup file downloaded.</pre>`; } else if (endpoint === 'execute_json.php') { const result = await response.json(); outputDiv.innerHTML += `<pre>${JSON.stringify(result, null, 2)}</pre>`; } else { const result = await response.text(); outputDiv.innerHTML += `<pre>${result}</pre>`; } } catch (error) { outputDiv.innerHTML += `<pre style="color: red;">Error: Could not connect to the server or invalid response.</pre>`; } } commandInput.value = ''; outputDiv.scrollTop = outputDiv.scrollHeight; }; commandInput.addEventListener('keydown', (e) => { if (e.key === 'Enter') { e.preventDefault(); executeCommand(); } }); sqlFileInput.addEventListener('change', async (event) => { const file = event.target.files[0]; if (!file) { outputDiv.innerHTML += `<pre style="color: red;">No file selected.</pre>`; return; } const formData = new FormData(); formData.append('sql_file', file); formData.append('db', currentDB); outputDiv.innerHTML += `<pre>Uploading and executing '${file.name}'...</pre>`; outputDiv.scrollTop = outputDiv.scrollHeight; try { const response = await fetch('upload_sql.php', { method: 'POST', body: formData, }); const result = await response.text(); outputDiv.innerHTML += `<pre>${result}</pre>`; } catch (error) { outputDiv.innerHTML += `<pre style="color: red;">Error during file upload and execution.</pre>`; } sqlFileInput.value = ''; commandInput.focus(); outputDiv.scrollTop = outputDiv.scrollHeight; }); if (closeModalBtn) { closeModalBtn.onclick = () => { modal.style.display = 'none'; commandInput.focus(); }; window.onclick = (event) => { if (event.target === modal) { modal.style.display = 'none'; commandInput.focus(); } }; } });