How to Debug Any MySQL Query?
Last Updated :
11 May, 2022
One of the most frequent problems faced by the full-stack developers, or the DBMS managers is the debugging of the MySQL queries. It is very difficult to debug the MySQL commands as there isn't a single IDE that can help in debugging the database queries. So, what can be done? How do tackle the DBMS query errors? Here, is a simple method to debug MySQL queries. This method works most of the time and actually saves a lot of effort and time for the developer. In this article, PhpMyAdmin is used for this process.
Form CreatedSuppose the developer has written the query for the above form. The query adds a new row in the user's table after the submit button is pressed. However, there is a slight error in the query, and thus the query isn't doing what it's supposed to do i.e; inserting the data into the table: $query = mysqli_query($this->con, "INSERT INTO users VALUES('', '$first_name', '$last_name')");
STEP 1: Echo Query, start by echoing the query string. Only the query part has to be echoed, and not the whole string. So, the echo line will be kept above the $query line. echo "INSERT INTO users VALUES('', '$first_name', '$last_name')"; $query = mysqli_query($this->con, "INSERT INTO users VALUES('', '$first_name', '$last_name')");
STEP 2: Refresh, enter some data into the form, press the submit button, and then refresh the page. Check the output.
Output on the pageSTEP 3: Execute the Output in PhpMyAdmin, copy and paste the output from the output page into the SQL tab of PhpMyAdmin. Go to localhost/phpmyadmin, find the database, and then click on the SQL tab. Find the database before following this step. Now, paste the output in the text area and then click on the 'Go' button.
Execute the Output in PhpMyAdmin
STEP 4: Error, the query is debugged, Check the ‘MySQL said’ part to know the error with the query.
Error in the queryAs per the error, our table 'users' doesn't exist in the database 'user'. On further investigation, it was found that the name of the table is 'user' and not 'users'.
Thus, the correct code after debugging will be $query = mysqli_query($this->con, "INSERT INTO user VALUES('', '$first_name', '$last_name')"); Therefore, this way can be used to debug any MySQL query.
Similar Reads
How and When To Use SLEEP() Correctly in MySQL? Pre-requisites: MySQL – Introdution MySQL has many useful but yet unexplored features. SLEEP() is one of these. SLEEP is a query that pauses the MySQL process for a given duration. If we give NULL or negative value, query gives a warning or an error. If there is no issue or error, query resumes proc
2 min read
Identify and Kill Queries with MySQL Command In SQL, some unnecessary processes can degrade your system's performance. Over time, threads pile up and stall your server, preventing users from accessing tables and executing requests. When resource usage is extremely high, you may need to kill MySQL processes. To do this, we first need to identif
3 min read
How to Log SQL Queries? SQL (Structured Query Language) is the standard language for interacting with relational databases, allowing users to retrieve, manipulate, and manage data. Logging SQL queries is a crucial aspect of database management and performance monitoring. SQL query logging involves the systematic recording
6 min read
How to Show Schema of a Table in MySQL Database? A table schema in MySQL database defines the structure of table, including columns, data types, relationships between columns, etc. It is a blueprint for the table, describing how data is organized in the table and how it relates to other tables in the database. To see the schema of a table in MySQL
2 min read
CRUD Operations in MySQL As we know that we can use MySQL to use Structure Query Language to store the data in the form of RDBMS. SQL is the most popular language for adding, accessing, and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use. The applic
3 min read