I'm trying to write a Database class. I've done this okay and can create an instance of the class and connect no problem.

I'm trying to write a query method which will allow me to pass en an array of variables for query or insert.

This is what I have, how can achieve this?

[PHP]
<?php

class Database{

public $connection;
private $result;

public function __construct(){

    $this->connection = new PDO('mysql:host=localhost;dbname=as', 'root','');

}

public function query($sql) {

    $result = $this->connection->prepare("$sql");
    return $result;

} 

public function query_par($sql, $a) {

    $result = $this->connection->prepare("$sql");
    $result->execute(array($a));
    return $result;

}

}
$db = new Database();

?>
[/PHP]

    What do you hope to achieve with this class that you cannot do by directly using PDO (possibly with a few helper functions of your own)?

    laserlight

    I want to use a single database object for my entire site all I need to do is pass in my query, insert, update or delete string to each method of my object.

        You would need to initialise a single object of your database class somewhere, and whatever method you choose could be done for a PDO object instead.

        As for simplifying CRUD functionality: if you're trying to avoid having to repeatedly create a PDOStatement object, you could write wrapper functions, but you'll need to figure out how you want to handle parameters and their data types. (Note that if you don't have any parameters, PDO's exec method is already such a "wrapper" function.)

          You could just extend PDO and add any additional methods you want, something like this (untested):

          <?php

class MyPDO extends PDO
{
    public function __construct($dsn, $username, $passwd, Array $options=null)
    {
        parent::__construct($dsn, $username, $passwd, array $options=null);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

public function doQuery($sql, Array $params=null)
{
    $stmt = $this->prepare($sql);
    $stmt->exec($params);
    return $stmt;
}
}

// sample usage
$pdo = new MyPDO($dsnString, $dbUserName, $dbPassword);
$sql = "
SELECT *
FROM my_table
WHERE id = :id
";
$id = 12345;
$stmt = $pdo->doQuery($sql, array(':id' => $id));
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // do something with $row
}

          PS: Don't know why my indenting got screwed up here. 🤷

            I have this code, my error is in line 26 which is this "$stmt->exec($params);"

            Any help please

            Fatal error: Call to undefined method PDOStatement::exec() in C:\wamp64\www\a2019\database.php on line 26

            <?php

            class Database{

            private $connection;

public function __construct(){

    try {
         return $this->connection = new PDO('mysql:host=localhost;dbname=as', 'root','');
    }   catch (PDOException $e){
        echo "Connection Failed: " . $e->getMessage();
    }      

}

public function getDb() {
    if ($this->connection instanceof PDO) {
         return $this->connection;
    }
}

public function doQuery($sql, Array $params=null){
            
    $stmt = $this->connection->prepare($sql);
    $stmt->exec($params);
    return $stmt;

}

            }

            $db = new Database();

            ?>

            Here is my page,

            if($db->getDb()){
            echo 'Connected Okay <br>';
            }


                    $sql = "SELECT * FROM aircraft WHERE Manufacturer = :manu";
        $manu = 'Airbus';
        $stmt = $db->doQuery($sql, array(':manu' => $manu));
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            print_r($row);
        }
              NogDog wrote:

              You could just extend PDO and add any additional methods you want

              I suggest that you don't do that unless:

              • You want to override some method in the PDO class (i.e., inheritance for polymorphism), or

              • You want a new class because you need to store one or more data members (hence merely extending the interface of the PDO class with a library of non-member functions will not suffice), or

              • You want to access a protected member of the PDO class (but there are none)

              Otherwise, extending the interface of the PDO class with a library of non-member functions is superior as it allows different such libraries to be freely combined, whereas if you inherited merely to extend the interface when you didn't need to, and another person did the same, a third person wanting to use functionality from both would have to deal with multiple inheritance (but in this particular form, that's not available in PHP).

              In NogDog's example, you could easily have a constructor function that is a wrapper for the PDO class constructor that does the same thing, and likewise doQuery merely needed a PDO parameter.

              NZ_Kiwis wrote:

              I have this code, my error is in line 26 which is this "$stmt->exec($params);"

              exec is a method belonging to the PDO class, not the PDOStatement class. You're looking for the execute method of the PDOStatement class instead.

                Write a Reply...