How do I display a MySQL error in PHP for a long query that depends on the user input?

Question

In PHP, I am trying to execute a long MySQL query that depends on the user input. However, my query fails with the following message,

"Query Failed".

Actually I have printed this message whenever the query fails, but I am having hard time looking for the reason behind this failure. Unfortunately, I couldn't find it because the error is not specified on the web page. Is there a way to display the error message that caused the failure on the web page?

Here's my code,

$from = "Findings";
$where = "";

if ($service != null)
{
    $from = $from . ", ServiceType_Lookup";
    $where= "Findings.ServiceType_ID= ServiceType_Lookup.ServiceType_ID AND ServiceType_Name= ". $service;

    if ($keyword != null)
        $where= $where . " AND ";
}

if ($keyword != null)
{
    $where= $where . "Finding_ID LIKE '%$keyword%' OR
                     ServiceType_ID LIKE '%$keyword%' OR
                     Title LIKE '%$keyword%' OR
                     RootCause_ID LIKE '%$keyword%' OR
                     RiskRating_ID LIKE '%$keyword%' OR
                     Impact_ID LIKE '%$keyword%' OR
                     Efforts_ID LIKE '%$keyword%' OR
                     Likelihood_ID LIKE '%$keyword%' OR
                     Finding LIKE '%$keyword%' OR
                     Implication LIKE '%$keyword%' OR
                     Recommendation LIKE '%$keyword%' OR
                     Report_ID LIKE '%$keyword%'";
}

$query = "SELECT Finding_ID,
                 ServiceType_ID,
                 Title,
                 RootCause_ID,
                 RiskRating_ID,
                 Impact_ID,
                 Efforts_ID,
                 Likelihood_ID,
                 Finding,
                 Implication,
                 Recommendation,
                 Report_ID  FROM ".$from . " WHERE " . $where;

echo "wala 2eshiq";

$this->result = $this->db_link->query($query);
if (!$this->result) {
    printf("Query failed: %s\n", mysqli_connect_error());
    exit;
}

$r = mysqli_query($this->db_link, $query);
if ($r == false)
    printf("error: %s\n", mysqli_errno($this->db_link));

You can just use: `$this->db_link->error` to get the last error message. For all errors use `$this->db_link->error_list`. — hakre, Sep 01 '12 at 12:31
For a much better solution please take a look at [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 05 '19 at 19:58

Christian · Accepted Answer · 2012-09-01T12:25:36.923

30

Use this:

mysqli_query($this->db_link, $query) or die(mysqli_error($this->db_link)); 
# mysqli_query($link,$query) returns 0 if there's an error.
# mysqli_error($link) returns a string with the last error message

You can also use this to print the error code.

echo mysqli_errno($this->db_link);

Take a look here and here

edited Sep 01 '12 at 12:25

answered Sep 01 '12 at 12:19

Christian

1,492
3
16
19

Arian Faurtosh · Answer 2 · 2015-02-12T16:43:00.237

16

I use the following to turn all error reporting on for MySQLi

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

*NOTE: don't use this in a production environment.

edited Feb 12 '15 at 16:43

answered Jul 16 '14 at 18:30

Arian Faurtosh

17,987
21
77
115

3

IMHO this **should** be used in production environment. You should not display errors to the user, but that is a separate issue related to PHP server set up. – Dharman Oct 05 '19 at 19:56
thank you kind sir – zyrup Jan 18 '21 at 19:26
This should be the accepted answer. It doesn't require error checking for every single SQL query (like $mysqli -> error after each execution) – G M Dec 29 '22 at 03:18

score 7 · Answer 3 · edited Oct 01 '18 at 01:29

7

Use function die():

or die(mysql_error());

edited Oct 01 '18 at 01:29

Peter Mortensen

30,738
21
105
131

answered Sep 01 '12 at 12:21

Julien

1,946
3
33
51

21

He's using `mysqli_*`, try not to direct him to use `mysql_*`. – David Sep 01 '12 at 12:24

score 0 · Answer 4 · edited Oct 01 '18 at 01:30

0

The suggestions don't work because they are for the standard MySQL driver, not for mysqli:

$this->db_link->error contains the error if one did occur

Or

mysqli_error($this->db_link)

will work.

edited Oct 01 '18 at 01:30

Peter Mortensen

30,738
21
105
131

answered Sep 01 '12 at 12:27

mc_fish

493
3
10

score -1 · Answer 5 · edited Oct 01 '18 at 01:31

-1

Try something like this:

$link = @new mysqli($this->host, $this->user, $this->pass)
$statement = $link->prepare($sqlStatement);
                if(!$statement)
                {
                    $this->debug_mode('query', 'error', '#Query Failed<br/>' . $link->error);
                    return false;
                }

edited Oct 01 '18 at 01:31

Peter Mortensen

30,738
21
105
131

answered Dec 11 '14 at 04:48

Abdel Rahman Karim

114
10

score -1 · Answer 6 · edited Oct 05 '19 at 19:57

-1

One useful line of code for you would be:

$sql = "Your SQL statement here";
$result = mysqli_query($this->db_link, $sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error($this->db_link), E_USER_ERROR);

This method is better than die, because you can use it for development AND production. It's the permanent solution.

edited Oct 05 '19 at 19:57

Dharman

30,962
25
85
135

answered Sep 12 '16 at 08:15

Hrvoje Antunović

781
5
15

How do I display a MySQL error in PHP for a long query that depends on the user input?

6 Answers6

Linked

Related