How to use PHP prepared statements in OOP

Question

I am saving my data using this code (pasting my code)

Connection.php:

<?php
    namespace Database;
    use Mysqli;

    class Connection {
        public $con;

        function __construct() {
            $this->con = new mysqli(connection strings here);
        }

        function save($sql) {
            $this->con->query($sql);
        }
    }
?>

then my Save.php is like this:

<?php
    require 'config.php';

    class Save {
        function __construct($username, $password) {
            $connect = new Database\Connection;
            $sql = "INSERT INTO sample(string1, string2) VALUES ('$test1', '$test2')";
            $connect->save($sql);
        }
    }

    $save = new Save("last", "last");
?>

my question is how do I implement bind params here and prepared statement for PHP?

and also I would like to ask what are the best way to do this and best practices that I should implement for my code

thanks guys

your usage of `__construct()` function is wrong. Why don't you move that logic to a new function in the class. — Rotimi, Feb 03 '18 at 12:00

score 1 · Answer 1 · answered Feb 03 '18 at 13:04

Your classes are structured in a weird way, I am guessing you want some sort of ORM like class?

If so, you may want to rename your Save class to User (that's a guess since you are trying to save a username and password) and move your constructor logic, e.g.

class User {

    function save($username, $password) {

        $sql = "INSERT INTO users (username, password) VALUES (?,?)";
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param("ss", $username, $password);
        $stmt->execute();        

    }

}

E-housma Mardini · Answer 2 · 2018-02-03T12:13:16.783

This example explain how you can do it .

<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);

/* close connection */
$mysqli->close();
?>

And you can find more info in this link : http://php.net/manual/tr/mysqli-stmt.bind-param.php

And i suggest you to use PDO its better way to connect with the database .

Your example does not help as OP wants to somehow use prepared statements in a class using OOP. — Rotimi, Feb 03 '18 at 12:04
of course the best way is to use PDO but that the answer for what did he ask — E-housma Mardini, Feb 03 '18 at 12:05

score 0 · Answer 3 · edited Mar 26 '19 at 07:22

Use like this.

public function insert_new_user($username, $email, $password){

    $mysqli = $this->link;

    $sql = "INSERT INTO users"
        . " (user_name, user_email, user_pass)"
        . " VALUES (?, ?, ?)";

    $stmt = $mysqli->prepare($sql);

    $stmt->bind_param("sss", $username, $email, $password);

    if ($stmt->execute()) {
        return "success";
    } else {
        return "failed: " . $mysqli->error;
    }
}

How to use PHP prepared statements in OOP

3 Answers3