When trying to create a View, Routine or Procedure for your database from within PHPMyAdmin, you may encounter an error similar to the following:
SELECT SUM(amount)
INTO total_sales
FROM sales
WHERE product_id = product_id
AND sale_date BETWEEN start_date AND end_date;
END
Click apply and then finish. If you encounter any errors, check your SQL query.
You can test the procedure to ensure it has been added correctly simply by opening a new query tab within the UI and then action the following code by hitting the lightning icon.
CALL CalculateTotalSales(101, '2025-01-01', '2025-01-09', @total_sales);
SELECT @total_sales;
CLI
To begin with, a Procedure will need to be created within a .SQL file. In this example, an essential procedure to calculate the total number of sales has been made. PhpMyAdmin may help when populating your SQL file, but you won't be able to execute this via phpMyAdmin.
DELIMITER
CREATE PROCEDURE CalculateTotalSales(
IN product_id INT,
IN start_date DATE,
IN end_date DATE,
OUT total_sales DECIMAL(10, 2)
)
BEGIN
SELECT SUM(s.amount)
INTO total_sales
FROM sales s
WHERE s.product_id = product_id
AND s.sale_date BETWEEN start_date AND end_date;
END $$
DELIMITER ;
Next, load the .SQL file into your database via MySQL CLI. First, upload the newly created .SQL file to your hosting package's root using FTP or the File Manager. You will then need to connect to your hosting package via SSH and load the .SQL file.
mysql -u [username] -p [database] -h [hostname] < procedure.sql
You will be prompted for a password once the procedure has been created. To confirm the procedure has been created correctly,
MariaDB [wordpress-35303439ccfa]> SHOW PROCEDURE STATUS WHERE db = 'wordpress-35303439ccfa';
+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| wordpress-35303439ccfa | CalculateTotalSales | PROCEDURE | wordpress-35303439ccfa@% | 2025-01-23 17:36:53 | 2025-01-23 17:36:53 | DEFINER | | utf8mb3 | utf8mb3_general_ci | latin1_swedish_ci |
+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.004 sec)
View
Workbench
In the Navigator, select the database in the top left, right-click Views, and click 'Create View'.
Once the View has been created, populate the query box with the code for the query. Once done, click 'apply'.
A popup window will ask you to review the script. Click 'apply' and then 'finish' to complete the configuration of the View.
To verify that the View has been created and configured correctly, go to the Navigator, select the database in the top left, right-click Views and the View should be presented to you. In addition, you use the query box to run a SQL command to select the View.
CLI
To create a View, generate a new SQL file and populate it with the relevant queries. Using phpMyAdmin may help when populating your SQL file, but you won't be able to execute this via phpMyAdmin. This is the example that will be loaded via MySQL CLI.
CREATE OR REPLACE VIEW product_sales_summary AS
SELECT
product_id,
sale_date,
SUM(amount) AS total_sales
FROM
sales
GROUP BY
product_id, sale_date
ORDER BY
product_id, sale_date;
SELECT * FROM product_sales_summary;
You will need to load the SQL file to the database. In order to do this, upload the SQL file to the web space via the File Manager or by using FTP. Once uploaded to the root of the web space, connect to the hosting package using SSH and then run the following MySQL import command.
mysql -u [username] -p [database] -h [hostname] < view.sql
You can query your View by using the following query within MySQL CLI:
SELECT * FROM total_sales_by_product;
+------------+-------------+
| product_id | total_sales |
+------------+-------------+
| 101 | 420.50 |
| 102 | 250.00 |
| 103 | 450.00 |
+------------+-------------+
Routine
Workbench
In the Navigator, go to your database > Stored Procedures
Right-click and select 'Create Stored Procedure'.
Define the procedure; in this example, the procedure is called 'GetProductSales'.
Enter the code, in this example we will be using the following:
BEGIN
DECLARE total_sales DECIMAL(10, 2);
SELECT SUM(amount)
INTO total_sales
FROM sales
WHERE product_id = product_id
AND sale_date BETWEEN start_date AND end_date;
SELECT
product_id AS ProductID,
start_date AS StartDate,
end_date AS EndDate,
total_sales AS TotalSales;
END
One done, apply and then save the changes made.
To verify this has been done successfully, you can click the drop-down arrow in the top left corner for the Stored Procedures. The newly created routine should be present. Alternatively, action the following SQL query and change the DB name where appropriate.
SHOW PROCEDURE STATUS WHERE Db = 'salesandsold-35303837922f';
CLI
Similarly to the procedures, an SQL file must be generated to contain the routine. This will be uploaded to the database using MySQL CLI. The routine example is as follows:
DELIMITER $$
CREATE PROCEDURE GetProductSales(
IN product_id INT,
IN start_date DATE,
IN end_date DATE
)
BEGIN
DECLARE total_sales DECIMAL(10, 2);
-- Calculate the total sales for the product in the given date range
CALL CalculateTotalSales(product_id, start_date, end_date, total_sales);
-- Output the result
SELECT
product_id AS ProductID,
start_date AS StartDate,
end_date AS EndDate,
total_sales AS TotalSales
FROM dual;
END $$
DELIMITER ;
Once the SQL file has been uploaded to the root of your hosting package's web space, please connect to the hosting package via SSH and import the SQL file to the MySQL database.
mysql -u [username] -p [database] -h [hostname] < routine.sql
You can then call the routine via the following query:
CALL GetProductSales(101, '2025-01-01', '2025-01-09');
+-----------+------------+------------+------------+
| ProductID | StartDate | EndDate | TotalSales |
+-----------+------------+------------+------------+
| 101 | 2025-01-01 | 2025-01-09 | 420.50 |
+-----------+------------+------------+------------+
1 row in set (0.001 sec)
Query OK, 1 row affected (0.001 sec)
