PHP class insert data into table not working

Question

With the script below I'm trying to add a new user to the user table. I'm new with working with classes and so far I'm not able to get it working. My server is running PHP7.

config.php

<?php
/**
 * Database config variables
 */
define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASSWORD", "12345");
define("DB_DATABASE", "databasename");
?>

connect.php

<?php

class DB_Connect {

    // constructor
    function __construct() {

    }

    // destructor
    function __destruct() {
        // $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once '/folder/folder/folder/config.php';
        // connecting to mysql
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

        // return database handler
        return $conn;
    }

    // Closing database connection
    public function close() {
        $conn->close();
    }

} 
?>

If I change the connect function in the connect.php to the function below I get the succes message (Connected successfully) so I think there is no database connection issue:

public function connect() {
    require_once '/folder/folder/folder/config.php';
    // connecting to mysql
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

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

functions.php

<?php

class DB_Functions {

    private $db;

    //put your code here
    // constructor
    function __construct() {
        include_once './connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }

    // destructor
    function __destruct() {

    }

    /**
     * Storing new device
     */
    public function storeUser($name, $userid, $club) {
        echo;
        // insert user into database
        $result = "INSERT INTO users(name, user_id, club_id, created_at) VALUES('$name', $userid, $club, NOW())";
        // check for successful store
        if ($result) {
            // succes
            return "Device is added";
        } else {
            return "Error: Device is not added!" ;
        }
    }

    /**
     * Getting all users
     */
    public function getAllUsers() {
        $result = "select * FROM users";
        return $result;
    }

}

?>

The script below I made as a test script to run in the browser to add a test user:

<?php

$name = "1234567890";
$userid = "1234";
$club = "09876";

include_once './functions.php';

$db = new DB_Functions();

$res = $db->storeUser($name, $userid, $club);

echo $res;

?>

Does the MySQL user have permission to insert into the users table? — Nicholas Summers, Apr 14 '16 at 19:43
You never send anything to mysql you just store a string in a variable. — PeeHaa, Apr 14 '16 at 19:43
As a sidenote your `close()` method is also borked. `$conn` is a variable local to the `connect()` method so you can't access it from the `close()` method. — Chris, Apr 14 '16 at 19:44
`$club` is a string and needs to be surrounded by single quotes in your query. — devlin carnate, Apr 14 '16 at 19:45

Mawia HL · Accepted Answer · 2016-04-14T19:59:48.760

0

Without executing the insert statement and the connection source, it won't work. Try this:

$result = $this->db->connect()->query("INSERT INTO `users`(`name`, `user_id`, `club_id`, `created_at`) 
                 VALUES('$name', '$userid', '$club', NOW())");

And check if data is inserted:

if($this->db->connect()->affected_rows){
   return 'Device is added';
}

Function connect() does not return any connection:

You must put return $conn; instead of echoing the connection success.

edited Apr 14 '16 at 19:59

answered Apr 14 '16 at 19:54

Mawia HL

3,175
1
21
44

It is working now, youre code inserts it into the database. The inserted check is not working do. When I run the script I get a empty page no feedback (Device is added). – user1242574 Apr 14 '16 at 20:31
Please look at this [link](http://php.net/manual/en/mysqli.affected-rows.php). And if my answer helps, you can click the tick icon above. – Mawia HL Apr 14 '16 at 20:39

Monochrome Yeh · Answer 2 · 2016-04-14T21:10:52.047

You didn't send any query from your DB instance.

connect.php

class DB_Connect {
    private static $_instance;

    // destructor
    function __destruct() {
        $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once '/folder/folder/folder/config.php';

        if (self::$_instance === null) {
            // connecting to mysql
            self::$_instance = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
        }
        // return database handler
        return self::$_instance;
    }

    // Closing database connection
    public function close() {
        if (self::$_instance !== null) {
            self::$_instance->close();
        }
    }

}

functions.php.php

public function storeUser($name, $userid, $club) {
    // insert user into database
    $query = "INSERT INTO users(name, user_id, club_id, created_at) VALUES({$name}, {$userid}, {$club}, NOW())";
    $result = this->db->query($query); // <--- you missed out this syntax...
    // check for successful store
    if ($result) {
        // succes
        return "Device is added";
    } else {
        return "Error: Device is not added!" ;
    }
}

[Singleton pattern doesn't really do much in PHP.](http://stackoverflow.com/questions/4595964/is-there-a-use-case-for-singletons-with-database-access-in-php/4596323#4596323) — Mikey, Apr 14 '16 at 21:00
@Mikey Thnkas your suggesstion, however, I couldn't write down connection pool for this case, maybe poster have to solve his problem first. — Monochrome Yeh, Apr 14 '16 at 21:03

PHP class insert data into table not working

2 Answers2