Database connections with OOP PHP and mysqli

PHP
1

I am probably at the “low intermediate” level in procedural PHP, but my only experience with OOP PHP was a project done in class about five years ago and I want to re-learn and become more proficient in OOP PHP.

Yesterday I worked through an OOP tutorial that built a simple registration system. The only problem was that it was a bit out of date and used mysql_. I tried to convert to mysqli_ but am running into problems that I can’t figure out how to fix.

Here is the relevant code from the database class (DB.class.php) -

<?php
// DB.class.php

class DB {
	
	protected $db_name = 'databasename';
	protected $db_user = 'databaseuser';
	protected $db_pass = 'databasepassword';
	protected $db_host = 'databasehost';
	
	// Open a connect to the database.
	// Make sure this is called on every page that needs to use the database.
	
	public function connect() {
	
		$connect_db = new mysqli( $this->db_host, $this->db_user, $this->db_pass, $this->db_name );
		
		if ( mysqli_connect_errno() ) {
			printf("Connection failed: %s\
", mysqli_connect_error());
			exit();
		}
		return true;
		
	}

}

And in the userTools.class.php file there is (I just included here an example of a method that is causing problems) :

<?php
// UserTools.class.php

require_once 'User.class.php';
require_once 'DB.class.php';

class UserTools {

        // Log the user in.
	// First check to see if the username and password match a row in the database.
	// If it is successful, set the session varialbes and store the user object within.
	
	public function login( $username, $password ) {
		
		$hashedPassword = md5($password);
		$result = mysqli_query("SELECT * FROM users WHERE username = '$username' AND password = '$hashedPassword' ");
		
		if ( mysqli_num_rows($result) == 1 ) {
			$_SESSION[ "user" ] = serialize( new User( mysqli_fetch_assoc( $result ) ) );
			$_SESSION[ 'login_time' ] = time();
			$_SESSION[ 'logged_in' ] = 1;
			return true;
		} else {
			return false;
		}
	}
}

I am getting the error message (among many): “mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\OOBusiness\classes\UserTools.class.php on line 42”.

I have tried all sorts of things to fix this issue, but nothing seems to work. What else do I need to change/add in my switch from mysql to mysqli?

2

The syntax is

resource mysql_query ( string $query [, resource $link_identifier] )
mixed mysqli_query ( mysqli $link, string $query [, int $resultmode] )

In mysql_query the connection link was optional, but in mysqli_query it’s required and is the first parameter.

3

Except when you are using OOP where you call the mysqli_ methods on the database oblect instead.

You need to pass that $connect_db object through to where the mysqli_ call in order to use it with mysqli_ calls either as procedural or OOP calls.

So either procedural:

$result = mysqli_query($connect_db,"SELECT * FROM users WHERE username = '$username' AND password = '$hashedPassword' ");

or OOP:

$result = $connect_db->mysqli_query("SELECT * FROM users WHERE username = '$username' AND password = '$hashedPassword' ");

As a next step you’d then replace the mysqli_query call with mysqli_prepare and mysqli_bind calls to separate the data from the SQL and so make SQL injection impossible.

4

:d’oh: Thanks Stephen, I don’t know how I missed that :blush:

Off Topic:

Time to take a break and get something to eat. That’s my story and I’m sticking to it.

5

Thanks for clearing that up. I understand the overall basic concepts of OOP, but it seems that I’m not very clear yet in piecing the little bits together.

I changed my database query, and that took care of the error message in question.

Now I get these two errors: "Notice: Undefined variable: connect_db in C:\xampp\htdocs\OOBusiness\classes\UserTools.class.php on line 42

Fatal error: Call to a member function mysqli_query() on a non-object in C:\xampp\htdocs\OOBusiness\classes\UserTools.class.php on line 42"

So I tried creating a new database object inside each of the methods that had queries like this:

$db = new DB();
		$db->connect();

and now I’m getting errors "Notice: Undefined variable: connect_db in C:\xampp\htdocs\OOBusiness\classes\UserTools.class.php on line 45

Fatal error: Call to a member function mysqli_query() on a non-object in C:\xampp\htdocs\OOBusiness\classes\UserTools.class.php on line 45"

6

Is it still returning “true” instead of a handle?

7

I’m not sure, because I can’t get past that fatal error.

8 
$connect_db = new mysqli( $this->db_host, $this->db_user, $this->db_pass, $this->db_name );

Should be:

$this->connect_db = new mysqli( $this->db_host, $this->db_user, $this->db_pass, $this->db_name );

Since it is a class instance variable

9

I made that change to the DB class connect() method (it makes sense to me, thank you), but I’m still getting the same error messages - both with and without creating a new database object within the login() method in the userTools class.

10

Perhaps you should try a different approach? Find a nice PDO tutorial to follow. After you understand how to use PDO then you can go about writing your own connection object if you think it is worth it.

11

Apprently what you are doing is not OOP at all, just creating an mysqli connection object does not indicate that your code is object oriented. The usage of mysqli functions is procedural, and for some reason you aint even using the instantiated mysqli object. I wonder what you are trying to achieve.

12

@ahundiak - I have read a lot about the difference between mysqli and PDO and apparently the main difference is “the core advantage of PDO over MySQLi is in its database driver support. At the time of this writing, PDO supports 12 different drivers, opposed to MySQLi, which supports MySQL only.” I have no interest at this time in using other database drivers than mySQL, so for now I have chosen to stick to mysqli. Maybe sometime in the future something might convince me to convert to PDO.

@Hall of Famer - What I am trying to accomplish is convert the results of an out-dated tutorial from mysql to mysqli so that it works for me. I am not trying at this point to have something that is purely OOP. By the way, I was under the impression that it is okay to mix a bit of procedural with OOP, and there is nothing wrong with well-written procedural code.

Back to the issue at hand - how do I fix my code so that it is not giving me errors, not how do I throw the whole thing out and do something different. :slight_smile:

13

Consider checking in your code to a public github repository so all we need to do is clone it. Debugging code spread across multiple files via a forum is way too challenging for me.

14

Then your best shot is to take a chance on a different tutorials. As you may have already known, the concept of procedural and OO programming differ dramatically. Its not easy to actually convert procedural code into OOP unless you are at least professional or even expert at it(which requires high-level refactoring techniques). The best way is to actually redesign your program following a true OOP tutorial and stop thinking in the old procedural way. Also keep in mind that just using object does not make your program OOP, I’ve seen some java and C# code that are pretty much procedural in disguise since the author uses tons of static methods that are nothing more than namespaced functions.

15

The difference between the mysql_ calls and mysqli_ calls is that the database connection parameter was optional with mysql_ calls (if present it was the second parameter).

With mysqli_ calls that is no longer optional. You need to pass the connection object to everywhere that you are accessing the database so that you can then either pass it as the first parameter to a procedural mysqli_ call or use it as the object on which to make an OOP mysqli_ call.

16

There are a few dots here that need to be connected.

First, getting the connection from the DB class. You have a couple options.

  1. Return it from the connect method (Mittineague alluded to this one):
    public function connect() {

        $connect_db = new mysqli( $this->db_host, $this->db_user, $this->db_pass, $this->db_name );

        if ( mysqli_connect_errno() ) {
            printf("Connection failed: %s\
", mysqli_connect_error());
            exit();
        }
        [COLOR="#FF0000"]return $connect_db; [/COLOR]

    }

Or 2) store the connection in an instance variable, then provide a getter (cpradio mentioned this one):

    public function connect() {

        [COLOR="#FF0000"]$this->[/COLOR]connect_db = new mysqli( $this->db_host, $this->db_user, $this->db_pass, $this->db_name );

        if ( mysqli_connect_errno() ) {
            printf("Connection failed: %s\
", mysqli_connect_error());
            exit();
        }
        return true;

    }

    [COLOR="#FF0000"]public function get_connection() {
        return $this->connect_db
    }[/COLOR]

Next, you need to get that connection from within your UserTools class. The way you do that depends on which option you picked above.

If you picked 1, then in UserTools, you would do:

$db = new DB('host', 'user', 'pass', 'name');
$connection = $db->connect();

Or if you picked 2, then:

$db = new DB('host', 'user', 'pass', 'name');
$db->connect();
$connection = $db->get_connection();

Finally, you would use $connection in your query calls, which was the original problem everyone spotted.

mysqli_query([COLOR="#FF0000"]$connection,[/COLOR] "SELECT * FROM users WHERE username = '$username' AND password = '$hashedPassword' ");

Keep in mind that there are still many things that could – and in some cases, absolutely should – be done differently, but for now, this should get you past your errors.

17

Thanks, Jeff Mott. I think you may have supplied the missing pieces to my puzzle. I’ll try it out tomorrow. (Thanks to all others who have added their two cents worth). My plan is to make this work, then gradually revise it until the code is the way it should be. I need to be able to see how the basic parts fit together before I obscure them with all sorts of other details.

Most of my OOP books, and most OOP tutorials try to throw too much theory at a person without leaving them with something useful in the end. This is the first tutorial that kept to the very basics and resulted in something that worked.

My main issue with OOP is not the theory, but how do you take all those concepts and plan and build a whole project from the ground up.