How to Debug Any MySQL Query?

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 Created

Suppose 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 page

STEP 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 query

As 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.