PythonHelp Posted June 24, 2020 Share Posted June 24, 2020 (edited) Hi ! I am trying to translate my mysqli count query that works perfectly into prepared statements. Unfortunately, after playing around and using my knowledge of PS, I have come up with this script which fails to execute and returns a http 500 error. I may have missed something very silly, I require some guidance on fixing the error. <?php $conn = mysqli_connect("xxxx", "xxxx", "xxxx", "xxx"); $sel_query = "SELECT S1, B1 COUNT(IF(S1 = ?, 1, NULL)) 'Accepted', COUNT(IF(S1 = ?, 1, NULL)) 'Rejected', COUNT(IF(S1 = ?, 1, NULL)) 'Under_Review' FROM Enrol"; $stmt = $conn->prepare($sel_query); $Accepted="Accepted"; $Rejected="Rejected"; $Under_Review="Under Review"; $stmt->bind_param("sss",$Accepted, $Rejected, $Under_Review); $stmt->execute(); $result = $stmt->get_result(); // get the mysqli result if($result->num_rows === 0) exit('No records found!'); while($row = $result->fetch_assoc()) { ?> <tr> <td><?php echo $row["Accepted"]; ?></td> <td><?php echo $row["Rejected"]; ?></td> <td><?php echo $row["Under_Review"]; ?></td> </tr> </table> Edited June 24, 2020 by PythonHelp Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted June 24, 2020 Share Posted June 24, 2020 The while loop in the above code is missing a closing curly brace. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 24, 2020 Share Posted June 24, 2020 That query doesn't require preparation - there are no user-provided values. Also your use of single quotes around the column aliases is wrong, and you have a comma missing after B1 $sel_query = "SELECT S1 , B1 , SUM(S1 = 'Accepted') `Accepted` , SUM(S1 = 'Rejected') `Rejected` , SUM(S1 = 'Under Review') `Under_Review` FROM Enrol"; $result = $conn->query($sel_query); Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted June 24, 2020 Author Share Posted June 24, 2020 1 hour ago, cyberRobot said: The while loop in the above code is missing a closing curly brace. I didn't seem to locate the missing curly brace 49 minutes ago, Barand said: That query doesn't require preparation - there are no user-provided values. Also your use of single quotes around the column aliases is wrong, and you have a comma missing after B1 $sel_query = "SELECT S1 , B1 , SUM(S1 = 'Accepted') `Accepted` , SUM(S1 = 'Rejected') `Rejected` , SUM(S1 = 'Under Review') `Under_Review` FROM Enrol"; $result = $conn->query($sel_query); Great, I was thinking the same about not using PS in this query! Thank you for making that clear for me. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 24, 2020 Share Posted June 24, 2020 there are two main points for using a prepared query - to prevent sql special character in external, unknown, dynamic data values from breaking the sql query syntax. to speed up the overall process when executing the same query more than once within an instance of your script, since the communication and execution planning phase for the sql query statement is performed only once. if you are not doing either of these, don't use a papered query. 7 hours ago, PythonHelp said: returns a http 500 when learning, developing, and debugging code/queries, you should display all php errors (when on a live/public server, you should log all php errors.) set php's error_reporting to E_ALL and display_errors to ON, in the php.ini on your system. if you put these settings into your code, parse/syntax errors wont be reported (the case of the missing } ) since your code never runs to change the settings for this type of error. putting these settings into your code also means more work for you later when you must find and change/remove them when moving the code to a live/public server. you also need error handling for all statements that can fail. for database statements, the easiest way of adding error handling, without adding logic at each statement that can fail (connection, query, prepare, and execute) is to use exceptions and in most cases let php catch the exception where it will use its error related settings (see above) to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this rule is when inserting/updating duplicate or out of range user submitted values. in this case your code should catch the exception, detect if the error number is for something that your code is designed to handle, then setup a message for the user telling them what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it. to enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection, then remove any existing error handling logic, since it will no longer be executed if there is an error - mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); 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.