Can I display a query error number, or how can I find out what's wrong with my MySQL query?

Question

I was writing register.php which creates the user and add him to MySQL table "workers". It has a basic validation and uses session to check if the user is already logged in. However, I have encountered a problem with my INSERT query, It doesn't work. I have made an error message which display text: "Something went wrong, try again later... " and should display mysqli error. However it just displays the message and doesn't work. How can I display mysql query error number and what's wrong with my query? register.php:

   <?php
session_start();
include_once ('db.php');
$error = false;
$nameError = '';
$passwordError = '';
$usernameError = '';
$surnameError = '';
$emailError = '';
$MSG = '';
if (!isset($_SESSION['login_user'])) {
    if (isset($_POST['Register'])) {


        $name = $_POST['name'];
        $surname = $_POST['surname'];
        $email = $_POST['email'];
        $password = $_POST['password'];
        $username = $_POST['username'];


        // Username validation
        if (empty($username)) {
            $error = true;
            $usernameError = 'Please enter your username';
        } else {
            if (!preg_match('/^[a-zA-Z0-9]{5,}$/', $username)) {
                $error = true;
                $usernameError = 'Invalid Username';
            }
        }
        //Name validation
        if (empty($name)) {
            $error = true;
            $nameError = "Please enter your full name.";
        } else if (strlen($name) < 3) {
            $error = true;
            $nameError = "Name must have at least 3 characters.";
        } else if (!preg_match("/^[a-zA-Z ]+$/", $name)) {
            $error = true;
            $nameError = "Name must contain alphabets and space.";
        }
        //surname validation
        if (empty($surname)) {
            $error = true;
            $surnameError = "Please enter your surname";
        } else if (strlen($surname) < 3) {
            $error = true;
            $surnameError = "Surname must have at least 3 characters";
        } else if (!preg_match("/^[a-zA-Z ]+$/", $surname)) {
            $error = true;
            $surnameError = "Surname must contain alphabets";
        }


        //basic email validation
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $error = true;
            $emailError = "Please enter valid email";
        } else {
            // check email exist or not
            $query = "SELECT email FROM workers WHERE email='$email'";
            $result = mysqli_query($conn, $query);
            $count = mysqli_num_rows($result);
            if ($count != 0) {
                $error = true;
                $emailError = "Provided Email is already in use.";
            }
        }
        // password validation
        if (empty($password)) {
            $error = true;
            $passwordError = "Please enter password.";
        } else if (strlen($password) < 6) {
            $error = true;
            $passwordError = "Password has to be at least 6 charachters long";
        }

        $name = mysqli_real_escape_string($conn, $name);
        $surname = mysqli_real_escape_string($conn, $name);
        $email = mysqli_real_escape_string($conn, $name);
        $password = mysqli_real_escape_string($conn, md5($password));
        $user = mysqli_real_escape_string($conn, $username);

        if (!$error) {
            $query = "INSERT INTO 'workers' (id,user_name,password,email,surname,name) VALUES(NULL ,$user,$password,$email,$surname,$name)";
            $result = mysqli_query($conn, $query);
            if ($result) {
                $MSG = "Successfully registered, you may login now";
                unset($name, $password, $username, $surname, $name);


            } else {
                $MSG = "Something went wrong, try again later...";
                echo mysqli_error($conn);
            }

        }

    }
}else{
    echo "<script type='text/javascript'> document.location = 'welcome.php'; </script>";
    exit();
}

?>

You do not want to show internal errors to outside people, that looks extremely amateurish. Instead you want to start monitoring your http servers error log file where you can read in details what is wrong. — arkascha, Aug 01 '17 at 05:36
`mysqli_*` functions doesn't return errors. It return only `false`, when query fails.. If you want errors in return, you should use `PDO` http://php.net/manual/en/book.pdo.php — Zedex7, Aug 01 '17 at 05:36
When the query returns `false` to indicate an error, call `mysqli_error()` to get the error message. — Barmar, Aug 01 '17 at 05:37
See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks for the reason for the error in your `INSERT` query. — Barmar, Aug 01 '17 at 05:38
You never do `echo $MSG`, it shouldn't be showing that message. It should show the SQL error because you do `echo mysqli_error($conn);`. — Barmar, Aug 01 '17 at 05:39
Stop using `mysqli_real_escape_string` and learn to use prepared statements. — Barmar, Aug 01 '17 at 05:40
This must not be the actual code you're running, post the real code. — Barmar, Aug 01 '17 at 05:41
Don't see a `mysqli_connect` anywhere. Is `$conn` being declared somewhere else? — vsharper, Aug 01 '17 at 05:56

pedram shabani · Answer 1 · 2017-08-01T06:08:00.767

0

You have one Mistake in your query you can not use ' when you want to Definition table in your query.

it is true

INSERT INTO `workers` (id,user_name,password,email,surname,name) VALUES(NULL ,$user,$password,$email,$surname,$name)";

edited Aug 01 '17 at 06:08

answered Aug 01 '17 at 05:56

pedram shabani

1,654
2
20
30

i am wondering about his question? why mysqli_error($conn) is not showing message as he asked? it should show the error message – Farsay Aug 01 '17 at 06:31

gview · Accepted Answer · 2017-08-01T07:48:31.840

As Barmar already advised, you should change this query to use prepared statements.

I would go one step further and advise you to use PDO and scrap using mysqli altogether.

With that said, as pedram pointed out you have an issue with your table name specification, but I find that is sometimes an issue with people pasting in code.

All tablename specifiers in mysql need to use backtics. So:

INSERT INTO 'workers'

Isn't going to work.

INSERT INTO `workers`

might.

In my opinion there is no strong reason to use backtics unless your tablename is a reserved word. I never use them unless I absolutely have to.

But that is only part of the problem with your query, because you need quotes around any CHAR/VARCHAR/STRING data.

So you need:

$query = "INSERT INTO `workers` (id,user_name,password,email,surname,name) VALUES (NULL, '$user','$password','$email','$surname','$name')";

Last but not least you have this code:

 if ($result) {
                $MSG = "Successfully registered, you may login now";
                unset($name, $password, $username, $surname, $name);
            } else {
                $MSG = "Something went wrong, try again later...";
                echo mysqli_error($conn);
            }

It's not clear what you are actually seeing, but this code should be echoing the mysqli_error(). You set a $MSG variable but you never echo it out. In production code you shouldn't be echoing out the mysqli_error() to end users as that will be leaking your database internals. Just for debugging it's ok, but easy to forget.

There is actually an HTML code within register.php which I decided not to post here as I don't think the problem is there. — ProPall, Aug 01 '17 at 08:12

Can I display a query error number, or how can I find out what's wrong with my MySQL query?

2 Answers2