CODEWITHSUNDEEP
phpmysqliprepared-statement
Kingfox
Kingfox

Reputation: 129

php add prepared statements to database

I want to implement the prepared statement to my script but really cant get it to work. I already have alot of functions so i want as little change as possible.

I think it would be best to have a prepared statement function? So when i get user inputs I could call that functions instead of query.

The database.php class

class MySQLDB {
    var $connection; // The MySQL database connection

    /* Class constructor */
    function MySQLDB() {
        global $dbsystem;
        $this->connection = mysqli_connect ( DB_SERVER, DB_USER, DB_PASS,   DB_NAME ) or die ( 'Connection Failed (' . mysqli_connect_errno () . ') ' . mysqli_connect_error () );
    }


    /**
     * query - Performs the given query on the database and
     * returns the result, which may be false, true or a
     * resource identifier.
     */
    function query($query) {
        return mysqli_query ( $this->connection, $query );
    }
};

/* Create database connection */
$database = new MySQLDB ();

this is how I call the database from another class.

    $q = "UPDATE users SET name = '$name', started = '$time' WHERE id = '$id';";
    $result = mysqli_query ( $database->connection, $q );

Upvotes: 1

Views: 64

Answers (1)

nanocv
nanocv

Reputation: 2229

In your case I would do something a little cleaner, like this:

<?php

class MySQLDB{

  private function openConnection(){

    // If you don't always use same credentials, pass them by params
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $database = "database";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }

    // Assign conection object
    return $conn;
  }

  private function closeConnection($conn){
    $conn->close();
  }

  function updateUserById($id, $name, $startedTime){

    $conn = $this->openConnection();

    $sqlQuery = "UPDATE users SET name = ?, started = ? WHERE id = ?";

    if ($stmt = $conn->prepare($sqlQuery)) {

      // Bind parameters
      $stmt->bind_param("ssi", $name, $startedTime, $id);

      // Execute query
      $stmt->execute();

      if ($stmt->errno) {
        die ( "Update failed: " . $stmt->error);
      }

      $stmt->close();
      }

    $this->closeConnection($conn);
  }

} // Class end

Now, to use it, you just have to do this:

<?php

$myDBHandler = new MySQLDB;

$myDBHandler->updateUserById(3, "Mark", 1234);

Upvotes: 2

Related Questions