mysqli error: No index used in query/prepared statement, what does this mean??

I already got this working in PDO, and for practice, I was trying to do mysqli with the same database operation, and I got this error:

Exception: exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement select count(*) as recordcount from customers where (firstname like ? or lastname like ?) and deleted = 'n' order by lastname, firstname ' in C:\xampp\htdocs\newdimension\_includes\ndAPI.php:776 Stack trace: #0

Open in new window


The SQL statement I'm trying to do is:

$db = DB::getMYSQLIConnection();
        // Turn on Exceptions
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_ALL);

                $sqlCount = " select count(*) as recordcount " .
                            " from customers " .
                            " where (firstname like ? " .
                            " or lastname like ?) " .
                            " and deleted = 'n' " .
                            " order by lastname, firstname ";
                
                $stmtCount = $db->prepare($sqlCount);
                $searchString = '%' . $searchString . '%';
                $stmtCount->bind_param('ss', $modifiedSearchString, $modifiedSearchString);
                $stmtCount->execute(); // ERROR IS OCCURRING HERE

Open in new window


Can someone please tell me what's wrong and how to correct this?? Thanks.
elepilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Is this...
$searchString = '%' . $searchString . '%';

Open in new window

supposed to be this...
$modifiedSearchString = '%' . $searchString . '%';

Open in new window

0
elepilAuthor Commented:
Dave, thanks for responding.

This forum unfortunately does not allow original posters to edit their posts. I just forgot to remove that "$searchString = '%' . $searchString . '%';". In my actual code, I actually have:

$modifiedSearchString = '%dav%';

Open in new window


I've actually step-traced debug and $modifiedSearchString does contain '%dav%', the expected value.

You know what I don't understand is that when I extracted the problem code into its separate test page, it works! I don't know what's causing this, I've even step-debug and examined the variable values, they're identical! I guess it boils down to my having to understand what that error message means.
0
elepilAuthor Commented:
Here's how my code looks like in my test page, and it works fine:

$modifiedSearchString = '%dav%';
    try {
        $db = DB::getMYSQLIConnection();
        // Turn on Exceptions
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_ALL);

        if ($db instanceof mysqli) {
            $sqlCount = " select count(*) as recordcount " .
                        " from customers " .
                        " where (firstname like ? " .
                        " or lastname like ?) " .
                        " and deleted = 'n' " .
                        " order by lastname, firstname ";

            $stmtCount = $db->prepare($sqlCount);
            $stmtCount->bind_param('ss', $modifiedSearchString, $modifiedSearchString);
            $stmtCount->execute();
            $result = $stmtCount->get_result();
            $row = $result->fetch_assoc();
        }
    } catch(Exception $e) {
        echo $e->getMessage();
    }
echo $row['recordcount'];

Open in new window


The above outputs 44, which is the number of records. If you compare this to the code snippet I posted originally, there's not anything different.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Dave BaldwinFixer of ProblemsCommented:
As I am fond of telling people, if it acts different then it is different.  The question is not 'if' but 'where'.  The primary difference I see is the 'try/catch' code.  ??
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elepilAuthor Commented:
Dave, I found the problem. It was the mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_ALL); causing the problem!

I changed the MYSQLI_REPORT_ALL to MYSQLI_REPORT_STRICT, and it worked. It also works if I removed it altogether and just left MYSQLI_REPORT_ERROR.

Do you know the difference between MYSQLI_REPORT_STRICT and MYSQLI_REPORT_ALL?? The PHP manual isn't much help on this.
0
Dave BaldwinFixer of ProblemsCommented:
According to this page http://php.net/manual/en/mysqli-driver.report-mode.php , MYSQLI_REPORT_STRICT throws mysqli_sql_exception and the other flags do not.
0
elepilAuthor Commented:
Dave, actually it is the MYSQLI_REPORT_ERROR that causes mysqli to throw mysqli_sql_exception. I know this because when all I have is mysqli_report(MYSQLI_REPORT_ERROR), it still throws Exceptions. So I don't know what MYSQLI_REPORT_ALL or MYSQLI_REPORT_STRICT does.
0
Dave BaldwinFixer of ProblemsCommented:
Don't know what else to tell you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.