MYSQLi Count Query: Prepared Statements

[PythonHelp]

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

[cyberRobot]

The while loop in the above code is missing a closing curly brace.

[Barand]

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);

 

[PythonHelp]

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.

 

[mac_gyver]

there are two main points for using a prepared query -

1. to prevent sql special character in external, unknown, dynamic data values from breaking the sql query syntax.
2. 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);