使用PHP连接类到MySQL数据库，插入，更新，删除

Question

I am about to start a new project, building a website in PHP using MySQL, javascript etc... My question is can i create a "connection class to MySQL database using PHP" once, and call that class every time I want to insert, update, or delete something. I'm new to php so go easy, can you send me example code, or link, to a tutorial. At the moment when I want to do a connection I put the connection at the top of the php which works but a lot of duplicated code on many pages.

category.php

<?php

    // connect to mysql db
    $con = mysqli_connect("localhost", "root", "");

    // use a mysql database
    mysqli_select_db($con, "gaa2013");

    // run a sqql query
    $result = mysqli_query($con, "select * from categories");

    print("<form method='POST' action=\"home.php?page=category\">");

        print("<select name = 'cat'>");
        print("<option selected=\"selected\" style=\"background-color: blue\">All</Option>");
            //print fields from each row
        while($row = mysqli_fetch_array($result)){
            $curr = $row['description'];
                print("<option value='$curr'>$curr</option>");

        }
        print("</select>");
            print("<input type=\"submit\" name=\"submit\" value=\"Select a Category\">");
        print("<hr>");

more code....

login.php

//If there are input validations, redirect back to the login form
if($errflag) {
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location: index.php");
    exit();
}

// connect to mysql db
$con = mysqli_connect("localhost", "root", "");
// use a mysql database
mysqli_select_db($con, "gaa2013");
// run a sqql query
$result = mysqli_query($con,"select * from users WHERE username='$username'");
//$num_rows = mysql_num_rows($result);
    //Check whether the query was successful or not
if($result) {
    while($row = mysqli_fetch_array($result)){
        //echo "$num_rows Rows
";
        $id = $row['id'];
        $userp = $row['username'];
        $pass = $row['password'];
        $_SESSION['SESS_MEMBER_ID'] = $id;
        $_SESSION['SESS_USERNAME'] = $userp;
        $_SESSION['SESS_PASSWORD'] = $pass;
        session_write_close();
        header("location: home.php");
        exit();
    }
more code.....

thanks in advance

Gman

Answer 1

Disclaimer: this is just an introduction. I do not advice this for regular use.

Your target is to avoid this:

// connect to mysql db
$con = mysqli_connect("localhost", "root", "");

// use a mysql database
mysqli_select_db($con, "gaa2013");

... every time you want to make a connection.

To do this, an approach it can be the Design Pattern: Singleton.

Check its explanation out here.

Now:

class Database {
    private static $connection = null;

    public static function getInstance()
    {
        static $connection = null;
        if (null === $connection) {
            $connection = new Database();
        }
        return $connection;
    }

    /**
     * Protected constructor to prevent creating a new instance of the
     * *Singleton* via the `new` operator from outside of this class.
     */
    protected function __construct()
    {
        // connect to mysql db
        $con = mysqli_connect("localhost", "root", "");

        // use a mysql database
        mysqli_select_db($con, "gaa2013");

        return $con;
    }
}

Then, you would get and run database connections like this:

// Get DB connection
$connection = Database::getInstance();
$result = mysqli_query($connection, "select * from categories");

How that works?

1. You request a new Database connection with Database::getInstance();
2. Database::getInstance checks if it is null and, therefore, no connection has been requested yet. If so, constructs one. Otherwise, uses the already open connection.
3. The database constructor is not available outside the Database object and therefore you can not request new Database(); from outside.

I would like you to know, however, that nowadays it is discussed if the Singleton pattern is appropiate or not. I do not think that you should concern much about it, as long as you are learning. I recommmend you to play with these techniques yourself: test, try, see what happens, if you feel it is right or not.