Using PHP OOP concept connecting to MySQL database

Hi

I have been getting to know PHP OOP concepts, and I started trying by writing a class and handful of functions to connect to the database and retrieve the information from the tables. I went through previous posts having similar titles, but most of them have written using mysql functions and I am using mysqli functions. I want somebody to through this simple script and let me know where the mistake is.

This is my class.connect.php:

<?php

    class mySQL{
        var $host;
        var $username;
        var $password;
        var $database;
        public $dbc;

        public function connect($set_host, $set_username, $set_password, $set_database)
        {
            $this->host = $set_host;
            $this->username = $set_username;
            $this->password = $set_password;
            $this->database = $set_database;

            $this->dbc = mysqli_connect($this->host, $this->username, $this->password, $this->database) or die('Error connecting to DB');        
        }

        public function query($sql)
        {
            return mysqli_query($this->dbc, $sql) or or die('Error:'.mysqli_error($this->dbc).', query: '.$sql);
        }

        public function fetch($sql)
        {        
            $array = mysqli_fetch_array($this->query($sql));          
            return $array;
        }

        public function close()
        {
            return mysqli_close($this->dbc);
        }
    }
    ?>

This is my index.php:

<?php
        require_once ("class.connect.php");

        $connection = new mySQL();

        $connection->connect('localhost', 'myDB', 'joker', 'names_list');
        $myquery = "SELECT * FROM list";
        $query = $connection->query($myquery);        

        while($array = $connection->fetch($query))
        {
            echo $array['first_name'] . '<br />';
            echo $array['last_name'] . '<br />';                
        }

        $connection->close();
?>

I am getting a error message "Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1, query: 1"

Any idea why this is happening?

Thanks

        public function fetch($sql)
        {        
            $array = mysqli_fetch_array($this->query($sql));          
            return $array;
        }

You're calling $this->query() in there as if $sql is a string, when in reality $sql is already a result object.

-Dan

Hi Dan,

thanks for replying. Can you be bit more specific, I am quite new into PHP. What would be the alternative.

Regards

You already run the query here: $query = $connection->query($myquery);

So now $query contains the mysql resource.

Then you pass it to fetch here: while($array = $connection->fetch($query))

Then in fetch, you try to run the query with the already-run resource: $array = mysqli_fetch_array($this->query($sql));

Instead, just use the $sql variable to fetch the array. $array = mysqli_fetch_array($sql);

A couple pointers, if I may:

1. Don't use var at the top of your class. This is old syntax. Use Public, Private, or Protected instead.

2. In addition to the above point, make sure you set the visibility responsibly. Does the database connection (dbc) really need to be public? Why would you ever need to access it outside of the class?

3. Using die() in a class is generally not something you want to do. It can cause your site to break in fantastic ways. You should set your query errors to an error class variable. Then make a method to retrieve these errors if you need to show them. You could also log them to a file.

4. Don't use mysqli_fetch_array() unless you specifically need that functionality. The reason is because it returns both a numerical and associate array which is unnecessary and inefficient. Since you are using array keys to access the data, just use mysqli_fetch_assoc() to only return an associative array.

Hope that helps.

For connection with database using OOP concepts, better using 'Singleton' pattern.

For creating only one instance of object.

For connection with database using OOP concepts, better using 'Singleton' pattern.

For creating only one instance of object.

-Dan

For connection with database using OOP concepts, better using 'Singleton' pattern.

For creating only one instance of object.

Better still to use dependency injection, so you don't blow scope all to hell.