Converting mysql_result to mysqli

Beginners - Learning PHP
#1

Hello,

I have a number of functions on a legacy php database website that have versions of mysql_result and I, of course, need to convert them to mysqli.


$result = mysqli_query($sql) or die(mysqli_connect_error());

		[b]if (mysql_result($result, 0, "test" == 1)) {[/b]

			return true;

		} else {

			return false;

		}

	}

and


	function get_access_level() {

		$sql = sprintf("SELECT access_level FROM %s WHERE login = '%s' AND active = 'y'", $this->table_name, $this->user);

		if (!$result = mysqli_query($sql)) {

		   $this->the_msg = $this->messages(14);

		} else {

			[b]$this->access_level = mysql_result($result, 0, "access_level");[/b]

		}

	}

and


	function send_confirmation($id) {

		$sql = sprintf("SELECT email FROM %s WHERE id = %d", $this->table_name, $id);

		[b]$user_email = mysql_result(mysqli_query($sql), 0, "email");[/b]

		if ($this->send_mail($user_email, 37)) {

			return true;

		} else {

			return false;

		}

	}

			$forgot_sql = sprintf("SELECT id, pw FROM %s WHERE email = '%s'", $this->table_name, $this->user_email);

			if ($forgot_result = mysqli_query($forgot_sql)) {

				$this->id = mysql_result($forgot_result, 0, "id");

				$this->user_pw = mysql_result($forgot_result, 0, "pw");

				if ($this->send_mail($this->user_email, 35)) {

					$this->the_msg = $this->messages(23);

[/code]

And there are a few more like those above. If I can figure out the how and why of the conversion I should be able to fix the rest of this code. I don’t get to do enough of this stuff to get a hard grasp of the syntax, so any help you can give is greatly appreciated.

Thanks!

#2

Actually, you have a more fundamental problem. The instance of the mysqli connection is required for mysqli_query calls (you would be getting php errors about the parameters being used.) You will need to make the variable holding the connection available in the scope of each piece code you have shown. In the case of the class methods/functions, you should use dependency injection to get the connection into the class, stored in a property named $db or similar, then you can use $this->db to reference it in the methods.

Next, you have inconstant and likely incorrect error handling for the database statements -

  1. In the first piece of code, you are using or die((mysqli_connect_error()). There are two problems with this - 1) you should NOT output the actual error information to the visitors on your site (hackers deliberately trigger errors so they can get information that’s in the error messages), and 2) I’m pretty sure that mysqli_connect_error() doesn’t work for query errors, but even if you were using mysqli_error(), you should not output the error information to visitors on your site.

  2. In the 2nd piece of code, you are setting up an application error massage when the query has failed with an error and based on the logic being used, whoever wrote this code apparently thinks that when a query doesn’t match a row of data, that this is an error and will result in a false value being returned by the mysqli_query() statement. This is incorrect. Not matching a row of data does not cause the query to fail. The query runs, but the result set is empty, and any sort of fetch statement to access the result will return a false value and unfortunately the old mysql_result() statement actually produces a php error for this case.

If the application error is intended for the case where the user wasn’t found, the logic needs to test if the result set is empty or not empty.

  1. The 3rd piece of code has no error handling.

  2. The 4th piece of code is checking if the query ran before trying to reference the data, but since the end of the code is missing, I/we cannot tell if it is doing anything, good or bad, when the query has failed due to an error.

To SIMPLIFY all of this error handling logic (i.e. remove it) and provide constant handling of database statement errors, just use exceptions and let php handle the error for you. Php will catch the exception and use it’s error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code, you would set those settings to cause the errors to be displayed. When on a live/public server, you would set those settings to cause the errors to be logged.

You should also be using prepared queries, with place-holders in the sql query statement where the data goes, then supply the data values when you execute the query. This will prevent sql special characters in the data from breaking the sql syntax, which is how sql injection is accomplished. Unfortunately, the php mysqli extension is not very well designed, especially with respect to prepared queries. If you can, switch to use the php PDO extension. It is much simpler and more consistent to use.

Lastly, for mysql_result (btw - the logic in the 1st code is incorrect), what is the code doing and how would you accomplish the same thing by fetching the data from the result set into a php variable named $row? Wouldn’t you just reference the column names in the variable $row?

#3

[size=10pt]Actually, you have a more fundamental problem. [/size]

(sigh…) of course I do.

[size=10pt]you should use dependency injection to get the connection into the class, stored in a property named $db or similar, then you can use $this->db to reference it in the methods.[/size]

Thanks for your help. I do have a configuration page that defines the connection variables. The code posted in my original post are parts for various functions and it looks as if I can do away with most of it.

Sponsor our Newsletter | Privacy Policy | Terms of Service