2020 Posted July 17, 2020 Share Posted July 17, 2020 Php programmers, I am getting this error: Fatal error: Uncaught mysqli_sql_exception: No index used in query/prepared statement (null) in C:\xampp\htdocs\test\select_adv.php:70 Stack trace: #0 C:\xampp\htdocs\test\select_adv.php(70): mysqli_stmt_execute(Object(mysqli_stmt)) #1 {main} thrown in C:\xampp\htdocs\test\select_adv.php on line 70 Line 70 is: if(mysqli_stmt_execute($stmt) === FALSE) Context: if(mysqli_stmt_prepare($stmt,$query)) { mysqli_stmt_bind_param($stmt,'s',$_SESSION['search_column']); if(mysqli_stmt_execute($stmt) === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); } This removes the error: if($stmt) === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); } I still want to know why this works: if($stmt) === FALSE) { but this doesn't work: if(mysqli_stmt_execute($stmt) === FALSE) { Let me know. Full Code in case you're wondering just what on earth is going on .... <?php //include('error_reporting.php'); error_reporting(E_ALL); ini_set('error_reporting',E_ALL);//Same as: error_reporting(E_ALL); ini_set('display_errors','1'); ini_set('display_startup_errors','1'); require('conn.php'); echo __LINE__; ?> <form name = "search" method = "POST" action=""> <label for="keywords">Keywords:*</label> <input type="text" name="keywords" id="keywords" placeholder="Input Keywords" required> <br> <label for="search_column">Search in ... ?</label> <select name="search_column" id="search_column"> <option value="page_url">Page Url</option> <option value="link_anchor_text">Link Anchor Text</option> <option value="page_description">Page Description</option> <option value="keyphrases">Keyphrase</option> <option value="keywords">Keywords</option> </select> <br> <label for="tos_agreement">Agree to TOS or not ? *</label> <select name="tos_agreement" id="tos_agreement" required> <option value="Yes">Yes</option> <option value="No">No</option> </select> <br> <button type="submit">Submit</button><br> <button type="submit" value="submit">Submit</button><br> <input type="submit" value="submit"><br> <button name=submit value=" ">Submit</button><br> <br> <input type="reset"> <br> </form> <?php session_start(); echo __LINE__; if($_SERVER['REQUEST_METHOD'] === 'POST') { echo __LINE__; if(ISSET($_POST['submit'])) { echo __LINE__; if(ISSET($_POST['search_column'])) { $_SESSION['search_column'] = $_POST['search_column']; echo $_SESSION['search_column']; echo __LINE__; } //Re-write the following 4 lines ... mysqli_report(MYSQLI_REPORT_ALL|MYSQLI_REPORT_STRICT); $conn = mysqli_connect("localhost","root","","test"); $conn->set_charset("utf8mb4"); //$query = "SELECT page_url,link_anchor_text,page_description,keyphrases,keywords FROM links WHERE $_SESSION['search_column'] = ?"; $query = "SELECT page_url,link_anchor_text,page_description,keyphrases,keywords FROM links WHERE {$_SESSION['search_column']} = ?"; //$query = "SELECT page_url,link_anchor_text,page_description,keyphrases,keywords FROM links WHERE " . $_SESSION['search_column'] . " = ?"; $stmt = mysqli_stmt_init($conn); if(mysqli_stmt_prepare($stmt,$query)) { mysqli_stmt_bind_param($stmt,'s',$_SESSION['search_column']); if(mysqli_stmt_execute($stmt) === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); } $result = mysqli_stmt_bind_result($stmt,$page_url,$link_anchor_text,$page_description,$keyphrase,$keywords); mysqli_stmt_fetch($stmt); while(mysqli_stmt_fetch($stmt)) { echo "url"; echo "<br>"; echo "anchor_text"; echo "<br>"; echo "description"; echo "<br>"; echo "keyphrases"; echo "<br>"; echo "keywords"; echo "<br>"; echo "|"; echo "<br>"; } mysqli_stmt_close($stmt); mysqli_close($conn); } else { echo "1. QUERY failed!"; } if(mysqli_stmt_prepare($stmt,$query)) { mysqli_stmt_bind_param($stmt,'sssss',$_POST['page_url'],$_POST['link_anchor_text'],$_POST['page_description'],$_POST['keyphrases'],$_POST['keywords']); if(mysqli_stmt_execute($stmt) === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); } $result = mysqli_stmt_get_result($stmt); while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) { $page_url = $row['page_url']; echo $page_url; echo "<br>"; $link_anchor_text = $row['link_anchor_text']; echo $link_anchor_text; echo "<br>"; $page_description = $row['page_description']; echo $page_description; echo "<br>"; $keyphrases = $row['keyphrases']; echo $keyphrases; echo "<br>"; $keywords = $row['keywords']; echo $keywords; echo "<br>"; echo "|"; echo "<br>"; } mysqli_stmt_close($stmt); mysqli_close($conn); } else { die("2. QUERY failed!"); } echo '<pre>' . print_r($_POST, 1) . '</pre>'; echo $_SESSION['search_column']; } } echo __LINE__; ?> Quote Link to comment Share on other sites More sharing options...
gw1500se Posted July 17, 2020 Share Posted July 17, 2020 You are not saving then using the statement object returned by prepare. RTFM 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 17, 2020 Share Posted July 17, 2020 the error is because you are not using an index in the sql query, meaning that it will have to scan through all the data in the table to find rows that satisfy the WHERE clause, and you are just copy/pasting things you see without even reading what they are saying. the MYSQLI_REPORT_ALL value you are using is causing this. if you read the section of the mysqli error report documentation that i posted in one of your threads, you will see what the ALL value does. it includes the MYSQLI_REPORT_INDEX - Report if no index or bad index was used in a query value. 31 minutes ago, 2020 said: I still want to know why this works: it doesn't. all you have done is randomly change the code so that it is no longer executing that query. btw - since you are using exceptions for database statement error handling, none of the discrete error handing logic you have in your code will be executed, and is therefore pointless, since execution transfers to the nearest exception handler, or to php if none. its php that's giving you the current error output. 1 Quote Link to comment Share on other sites More sharing options...
2020 Posted July 17, 2020 Author Share Posted July 17, 2020 (edited) 56 minutes ago, mac_gyver said: the error is because you are not using an index in the sql query, meaning that it will have to scan through all the data in the table to find rows that satisfy the WHERE clause, and you are just copy/pasting things you see without even reading what they are saying. the MYSQLI_REPORT_ALL value you are using is causing this. if you read the section of the mysqli error report documentation that i posted in one of your threads, you will see what the ALL value does. it includes the MYSQLI_REPORT_INDEX - Report if no index or bad index was used in a query value. it doesn't. all you have done is randomly change the code so that it is no longer executing that query. btw - since you are using exceptions for database statement error handling, none of the discrete error handing logic you have in your code will be executed, and is therefore pointless, since execution transfers to the nearest exception handler, or to php if none. its php that's giving you the current error output. Did I understand your hint Mac Guyver ? I switched to this now: mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); Error gone. Should I keep it at that since I do not know how to deal with all this exception thingy as it is way over my head. This is producing result: <?php //include('error_reporting.php'); error_reporting(E_ALL); ini_set('error_reporting',E_ALL); ini_set('display_errors','1'); ini_set('display_startup_errors','1'); ?> <form name = "search" method = "POST" action=""> <label for="keywords">Keywords:*</label> <input type="text" name="keywords" id="keywords" placeholder="Input Keywords" required> <br> <button type="submit">Submit</button><br> <button type="submit" value="submit">Submit</button><br> <input type="submit" value="submit"><br> <button name=submit value=" ">Search</button><br> <button type="submit" name="submit" value="submit">Search</button> <br> <input type="reset"> <br> </form> <?php if($_SERVER['REQUEST_METHOD'] === 'POST') { if(ISSET($_POST['submit'])) { if(ISSET($_POST['keywords'])) { $keywords = $_POST['keywords']; } //Re-write the following 4 lines ... mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $conn = mysqli_connect("localhost","root","","test"); $conn->set_charset("utf8mb4"); if(mysqli_connect_error()) { echo "Could not connect!" . mysqli_connect_error(); } $query = "SELECT page_url,link_anchor_text,page_description,keyphrases,keywords FROM links WHERE keywords = ?"; $stmt = mysqli_stmt_init($conn); //FIRST ATTEMPT TO DISPLAY TBL RESULTS USING mysqli_stmt_bind_result() FUNCTION. TEST RESULT: ATTEMPT A FAILURE! if(mysqli_stmt_prepare($stmt,$query)) { mysqli_stmt_bind_param($stmt,'s',$keywords); //if(mysqli_stmt_execute($stmt) === FALSE)//WHY THIS LINE SHOWS ERROR ? Fatal error: Uncaught mysqli_sql_exception: No index used in query/prepared statement (null) in C:\xampp\htdocs\ebrute\select.php:53 Stack trace: #0 C:\xampp\htdocs\ebrute\select.php(53): mysqli_stmt_execute(Object(mysqli_stmt)) #1 {main} thrown in C:\xampp\htdocs\test\select.php on line 53 $stmt_execution = mysqli_stmt_execute($stmt); if($stmt_execution === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); die; } $bind_result = mysqli_stmt_bind_result($stmt,$page_url,$link_anchor_text,$page_description,$keyphrase,$keywords); if($bind_result === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); die; } $stmt_fetch = mysqli_stmt_fetch($stmt); if($stmt_fetch === FALSE) { printf("Error: %s.\n", mysqli_stmt_error($stmt)); printf("Error: %d.\n", mysqli_stmt_errno($stmt)); die; } while(mysqli_stmt_fetch($stmt)) { echo "$page_url"; echo "<br>"; echo "$link_anchor_text"; echo "<br>"; echo "$page_description"; echo "<br>"; echo "$keyphrase"; echo "<br>"; echo "$keywords"; echo "<br>"; echo "|"; echo "<br>"; } mysqli_stmt_close($stmt); mysqli_close($conn); } else { echo "1. QUERY failed!"; } die; Is anything wrong or buggy with my code or what ? Edited July 17, 2020 by 2020 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.