1

I have functions.php that create connection to MySQL database, and execute query ->

function query($query, $fun, $value)
{   
    require "connect.php";
    mysqli_report(MYSQLI_REPORT_ALL);   

    try 
    {           
        $con = new mysqli($host, $db_user, $db_password, $db_name);         

        if($con->connect_errno) 
        {           
            throw new Exception(mysqli_connect_errno()); 
        }
        else 
        {               
            if(!is_array($value)) 
            {   
                
                if($result = $polaczenie->query(sprintf($query, mysqli_real_escape_string($con, $value))))      
                {                       

                    $num_of_rows = $result->num_rows;                       

                    if($num_of_rows>0)
                    {                   
                        $fun($result);                      
                    }
                    else 
                    {                                   
                        echo 'no results';
                    }                                                           
                }
                else 
                {
                    throw new Exception($con->error); 
                }

                $con->close(); 
            }           
            else 
            {
                // ...
            }           
        }
    }
    catch(Exception $e) 
    {               
        echo 'error ->'.$e;         
    }
}

in my index.php I am trying to execute the query ->

query("SELECT DISTINCT category FROM books ORDER BY category ASC", "get_categories", "");

I am getting that error ->

mysqli_sql_exception: No index used in query/prepared statement SELECT DISTINCT category FROM books ORDER BY category ASC in C:\xampp\htdocs...\functions.php:536;

I know that I could just change error reporting method to MYSQLI_REPORT_STRICT, but hiding error is good solution ?

This is what I get after executing "SHOW INDEX FROM books;" ->

books

I tried to use

SELECT DISTINCT category
FROM books FORCE INDEX (idx_category) 
ORDER BY category ASC;

yet still doesn't work

I dont want to change report mode to MYSQLI_REPORT_STRICT or others

Reading mysql documentation

Jason
  • 11
  • 3
  • How many books do you have in your database, it may be that MySQL doesn't think using an index is worth it using an index if the number of rows is smallish (don't know the limits). Try adding thousands of books and see if that makes a difference. – Nigel Ren Mar 09 '23 at 07:14
  • I don't have many books in my table, but that query works fine in mysql console. I was just wondering why it's not working, with MYSQLI_REPORT_ALL mode in PHP. Or maybe it's not just that important ? So I should just use STRICP mode ... – Jason Mar 09 '23 at 07:51

0 Answers0