My 'find by id' function not working and I don't understand why?

[Jool]

Hello.

I have a function which should find students based on their ID. The function is as follows:

[php] function find_student_by_id($id) { //so this function should return an associated array called teacher which has all teacher propertuies associated to that ID
global $db_connection;

$sql = "SELECT * FROM students_table";
//$sql .= "WHERE id='" . db_escape($db_connection, $id) . "' ";
$sql .= "WHERE id='" . $id . "' ";

$result = mysqli_query($db_connection, $sql);
//echo $result;
confirm_result_set($result);

$student = mysqli_fetch_assoc($result); // find first
mysqli_free_result($result);
return $student; // returns an assoc. array called student

}[/php]

As you can see, the function also calls another function inside it, called - confirm_result_set($result):

[php] function confirm_result_set($result_set) {
if (!$result_set) {
exit(“Database query failed jool!.”);
}
}
[/php]

In my exercise I created a form which takes us to the page (welcome.php) where this function above is run. The form successfully connects to the database and adds a new entry into the database (so I know I am connecting to the database ok). After doing so, it directs to this page passing an id string. This gets passed successfully.

The problem appears to be that function function confirm_result_set($result_set) is not returning a result_set as I keep getting the “Database query failed jool!.” error.

I took these functions from a tutorial and I am trying to adapt them. What is confusing me slightly is that the function confirm_result_set passes a variable called ‘result_set’ but the function function find_student_by_id is passing a variable called ‘result’. I tried making these the same but this didn’t seem to make a difference?

So I’m stuck here. Any advice / help would be great.

Many thanks.

[phdr]

What is confusing me slightly is that the function confirm_result_set passes a variable called 'result_set' but the function function find_student_by_id is passing a variable called 'result'. I tried making these the same but this didn't seem to make a difference?

It is not required to make the two variable names the same for a function definition and a function call to work together. The name used in the function definition just defines what the call-time parameter is named and what you will reference it by in the code inside the function. When you call the function, you are supplying a VALUE for the call-time parameter. The value for the call-time parameters can come from anything - a literal value (string/number), a variable, an expression, the returned value from another function, … For this particular usage, the value is in a variable named $result.

The problem appears to be that function function confirm_result_set($result_set) is not returning a result_set as I keep getting the "Database query failed jool!." error.

The confirm_result_set() function is doing what it was written to do. It is telling you that the Database query has failed. You should be spending your time determining what is wrong with the sql query.

However, the confirm_result_set() function is pointless. If this was real code on a real web site, you would not tell the visitor that something resulted in a database error and when learning, developing, and debugging code/queries, you should display the actual database error information to help you to determine what is causing the query to fail.

The simplest way of accomplishing this is to use exceptions for database statement (connection/query) errors and let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. Error_reporting should always be set to E_ALL. When learning, developing, and debugging code/queries, display_errors should be set to ON. When on a live/public server, display_errors should be OFF and log_errors should be ON.

For the php mysqli extension, to use exceptions for database statement errors, add the following line of code before the point where you are making the connection, then remove the confirm_result_set() function definition and calls -

[php]mysqli_report(MYSQLI_REPORT_ALL);[/php]

Once you do this and set php’s error_reporting and display_errors as suggested, you will be getting some error information that will help you find the problem with your sql query.

Next, you should be using a prepared query when supplying data to an sql query statement. This will make it impossible for any sql special characters in the data to break the sql query syntax, which is how sql injection is accomplished. Using a prepared query also simplifies the sql syntax (which is where your current problem is at), which will make it easier to write error free queries. Any php variable(s), quotes around the variable(s), and any concatenation dots are removed from the sql query and replaced with a simple ? place-holder.

Unfortunately, the php mysqli extension wasn’t designed very well when dealing with prepared queries. If you can, switch to use the php PDO extension. Not only is it overall easier and more consistent to use than the php mysqli extension, once you learn the php PDO statements, you can use those same php PDO statements for other database types (only the sql query syntax may change for other types of databases.)

[Jool]

Hi phdr,

Many thanks for a detailed and comprehensive response. Apologies for not getting back earlier - I’ll need to update my settings properly so I get notified when I get a reply to my post.

Thanks for the explanation of a function definition and function call. Slightly less of a head scratcher after your explanation.

Regarding seeing database errors, I noticed that display_errors was already set to ON on my development server and Error_reporting was set to E_ALL (so that’s good).

Regarding the mysqli_report(MYSQLI_REPORT_ALL); function;
to add it before the point where you are making the connection - would this mean it would be put before I call global $db_connection; in the function? i.e.

[php]function find_student_by_id($id) {
mysqli_report(MYSQLI_REPORT_ALL);
global $db_connection;

  $sql = "SELECT * FROM students_table";
  //$sql .= "WHERE id='" . db_escape($db_connection, $id) . "' ";
  $sql .= "WHERE id='" . $id . "' ";
 
  $result = mysqli_query($db_connection, $sql);
//echo $result;
 confirm_result_set($result);
 
$student = mysqli_fetch_assoc($result); // find first
 mysqli_free_result($result);
 return $student; // returns an assoc. array called student

}
[/php]

I did manage to work out the error - a pesky lack of a space in the php code.

Prepared statements will be next. Many thanks for the help.

Have a good day.

[thinsoldier]

This is good for a learning experiment. After learning prepared statements I suggest not actually continuing to use this code on a real project. There are easy to use libraries out there that simplify all of this. They are adequate for 80+% of what you’d want to do and for anything else you can use raw sql as you’re doing in your code now.