Hi All, I am trying to add insert/update and delete to an OOP singleton database connection using public functions like this, not sure if it is correct?

example code

public function insert($sql){
        if($this->_execute= $this->_mysqli->_execute($sql)){
            while ($row = $this->_execute->fetch_object()){
                $this->_results[] = $row;
            }
            $this->_count = $this->_execute->num_rows;
        }
                return $this;
    }

//page index.php

<?php

require_once 'classes/db.php';

function Users(){
    $users = DB::getInstance()->query('SELECT UserID, UserFullName, UserEmail, UserLastPassword, UserName, UserActive FROM Users');
    if ($users->count()){
        foreach ($users->results() as $user){
        echo $user->UserName, $user->UserActive,$user->UserLastPassword,'<br />';

        }
    }
}
Users();

//page db.php

<?php

class DB 
{
    private $_mysqli,
    $_query,
    $_results = array(),
    $_count=0,
    $_last_id = -1;

    public static $instance;

    public static function getInstance(){
        if (!isset(self::$instance)){
            self::$instance = new DB();
        }
        return self::$instance;
    }

    public function __construct() {
        $this->_mysqli = new mysqli('localhost','root','','db301847341');
        if($this->_mysqli->connect_error) {
            die($this->_mysqli->connect_error);
        }
    }


    public function query($sql){
        if($this->_query= $this->_mysqli->query($sql)){
            while ($row = $this->_query->fetch_object()){
                $this->_results[] = $row;
            }
            $this->_count = $this->_query->num_rows;
        }
        return $this;
    }


    public function execute($sql){
    if($this->_execute= $this->_mysqli->_execute($sql)){
        while ($row = $this->_execute->fetch_object()){
            $this->_results[] = $row;
        }
        $this->_count = $this->_execute->num_rows;
    }
            return $this;
}
    public function results(){
        return $this->_results;

    }
    public function count(){
        return $this->_count;

    }

}

For what reason are you using return $this; in your methods? Are you going to use method chaining?

What is supposed to be the difference between query and execute ?

hi Pritaeas, No not sure what methon chaining is? Need to look that up.

all I am trying to do insert, delete and update and not sure if I can continue with the query command to achieve this, hence the execute command.

Thanks

David

Note that queries like UPDATE and DELETE (and others) do not return a record set, so you can't fetch results. You should use affected_rows for such queries too (instead of num_rows).

Hi all, I am trying to create insert of data using the following script.

Insert not working, not sure if the code is correct.

Any help would be appreciated thanks in advance

David

Page: index.php

<?php
//dummy data
$UserFullName='Sam Smith';
$UserEmail ='email@sams';
$UserLastPassword ='15 Nov 2013';
$UserName= 'Sam Smith';
$UserActive='1';

require_once 'classes/db.php';
function Users(){
    $users = DB::getInstance()->query('SELECT UserID, UserFullName, UserEmail, UserLastPassword, UserName, UserActive FROM Users');
        if ($users->count()){
            foreach ($users->results() as $user){
            echo $user->UserName, $user->UserActive,$user->UserLastPassword,'<br />';

            }
        }
}
Users();
function UserInsert(){
 $usersinserts = DB::getInstance()->affected_rows("INSERT INTO Users (UserFullName, UserEmail, UserLastPassword, UserName, UserActive) VALUES ('$UserFullName','$UserEmail','$UserLastPassword','$UserName','$UserActive'");
}

UserInsert();

Page db.php

<?php

class DB 
{
    private $_mysqli,
    $_query,
    $_results = array(),
    $_count=0,
    $_last_id = -1;

    public static $instance;

    public static function getInstance(){
        if (!isset(self::$instance)){
            self::$instance = new DB();
        }
        return self::$instance;
    }

    public function __construct() {
        $this->_mysqli = new mysqli('localhost','root','','db301847341');
        if($this->_mysqli->connect_error) {
            die($this->_mysqli->connect_error);
        }
    }


    public function query($sql){
        if($this->_query= $this->_mysqli->query($sql)){
            while ($row = $this->_query->fetch_object()){
                $this->_results[] = $row;
            }
            $this->_count = $this->_query->num_rows;
        }
        return $this;
    }

    public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
        if (!$mysqli->query($sql)) {
                trigger_error($mysqli->error);
     }
   }
Member Avatar

diafol

function UserInsert(){
 $usersinserts = DB::getInstance()->affected_rows("INSERT INTO Users (UserFullName, UserEmail, UserLastPassword, UserName, UserActive) VALUES ('$UserFullName','$UserEmail','$UserLastPassword','$UserName','$UserActive'");
}

$UserFullName, etc do not exist inside the function - they're out of scope. The only exist outside the function, unless you define them as global inside the function - not advisable.
Why not pass the values as parameters?

$UserFullName='Sam Smith';
$UserEmail ='email@sams';
$UserLastPassword ='15 Nov 2013';
$UserName= 'Sam Smith';
$UserActive='1';
require_once 'classes/db.php';
function Users(){
    $users = DB::getInstance()->query('SELECT UserID, UserFullName, UserEmail, UserLastPassword, UserName, UserActive FROM Users');
        if ($users->count()){
            foreach ($users->results() as $user){
            echo $user->UserName, $user->UserActive,$user->UserLastPassword,'<br />';
            }
        }
}
Users();
function UserInsert($UserFullName,$UserEmail,$UserLastPassword,$UserName,$UserActive){
 $usersinserts = DB::getInstance()->affected_rows("INSERT INTO Users (UserFullName, UserEmail, UserLastPassword, UserName, UserActive) VALUES ('$UserFullName','$UserEmail','$UserLastPassword','$UserName','$UserActive'");
}
UserInsert($UserFullName,$UserEmail,$UserLastPassword,$UserName,$UserActive);

BTW - if all you're doing is running this function automatically (no conditionals) only once from near the function itself, one has to wonder why bother have a function at all. If it's not reusable or you don't have plans to split the code into includes...

Hi diafol, thanks for your valid comments, once I get the functions working I intend to split the code into includes.

I have run the code and I get an error ": Call to undefined method DB::affected_rows() in C:\wamp\www\db\index.php on line 17"

Any help would be appreciated

Thanks in advance

David

Call to undefined method DB::affected_rows()

affected_rows() method has not been created. If you are referring to the MySQLi property it should be something like:

$this->_mysqli->affected_rows

Hi Pritaes, so this $this->_mysqli->affected_rows should be added to public function line 38?

public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
        if (!$mysqli->query($sql)) {
                trigger_error($mysqli->error);
     }
     $this->_mysqli->affected_rows;
   }

Thanks in advance

David

You'll need to add a return.

Hi Pritaeas

I have added a return as follows and still reports the same error.

Thanks

David

public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
        if (!$mysqli->query($sql)) {
                trigger_error($mysqli->error);
     }
     $this->_mysqli->affected_rows;
     return $this;
   }

What's the error?

I meant:

return $this->_mysqli->affected_rows;

Hi Pritaeas, thanks for your reply

I am still getting this error msg " Call to undefined method DB::affected_rows() in C:\wamp\www\db\index.php on line 29"

page: index.php

<?php


/*if(isset($_POST['title'], $_POST['post'])){
$UserFullName = htmlentities($_POST['UserFullName']);
$UserEmail = htmlentities($_POST['UserEmail']);
$UserLastPassword = htmlentities($_POST['UserLastPassword']);
$UserActive = htmlentities($_POST['UserActive']);

}*/

$UserFullName='Sam Smith';
$UserEmail ='email@sams';
$UserLastPassword ='15 Nov 2013';
$UserName= 'Sam Smith';
$UserActive='1';
require_once 'classes/db.php';
function Users(){
    $users = DB::getInstance()->query('SELECT UserID, UserFullName, UserEmail, UserLastPassword, UserName, UserActive FROM Users');
        if ($users->count()){
            foreach ($users->results() as $user){
            echo $user->UserLastPassword." ".$user->UserName." ". $user->UserActive,'<br />';
            }
        }
}
Users();

function UserInsert($UserFullName,$UserEmail,$UserLastPassword,$UserName,$UserActive){
 $usersinserts = DB::getInstance()->affected_rows("INSERT INTO Users (UserFullName, UserEmail, UserLastPassword, UserName, UserActive) VALUES ('$UserFullName','$UserEmail','$UserLastPassword','$UserName','$UserActive'");
}
UserInsert($UserFullName,$UserEmail,$UserLastPassword,$UserName,$UserActive);

Page:db.php

<?php

class DB 
{
    private $_mysqli,
    $_query,
    $_results = array(),
    $_count=0,
    $_last_id = -1;

    public static $instance;

    public static function getInstance(){
        if (!isset(self::$instance)){
            self::$instance = new DB();
        }
        return self::$instance;
    }

    public function __construct() {
        $this->_mysqli = new mysqli('localhost','root','','db301847341');
        if($this->_mysqli->connect_error) {
            die($this->_mysqli->connect_error);
        }
    }


    public function query($sql){
        if($this->_query= $this->_mysqli->query($sql)){
            while ($row = $this->_query->fetch_object()){
                $this->_results[] = $row;
            }
            $this->_count = $this->_query->num_rows;
        }
        return $this;
    }

    public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
        if (!$mysqli->query($sql)) {
                trigger_error($mysqli->error);
     }
     return $this->_mysqli->affected_rows;

   }

    public function results(){
        return $this->_results;

    }
    public function count(){
        return $this->_count;

    }



}
Member Avatar

diafol

public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
        if (!$mysqli->query($sql)) {
                trigger_error($mysqli->error);
     }
     return $this->_mysqli->affected_rows;
   }

A few things here. $sql is not initialised, so it doesn't exist in this scope. Any ref to $_mysqli->xxx within methods should be stated thus...

$this->_mysqli->xxx

As the insert query has not been run, then the affected_rows has no meaning.

Hi Diafol, I am struggling understanding this can you provide any more detail on how it should be done correctly.

If you have the time it would be appriciated.

Thanks

David

Member Avatar

diafol

Sorry David, must have missed your reply. I'm hitting the dirt shortly, but will be back tomorrow if you're still interested.

Hi Diafol - yes that would be great, I know how busy things get

Thanks

David

Member Avatar

diafol

OK from the issues I raised in the previous post:

$sql is not initialised, so it doesn't exist in this scope.

if (!$mysqli->query($sql)) {

You place $sql as a parameter for this function. Well, it's not declared anywhere in the containing function [ usersinserts ]. Therefore, it does not exist, so essentially you're running an empty query - or more likely you're encountering an error.

There are a couple of ways to get around this:

  • Pass $sql as a parameter: private function userinserts($sql, ...)
  • Set $sql as a property at the start of the class: private $sql;, which you then reference throughout your class as $this->sql

Any ref to $_mysqli->xxx within methods should be stated thus...
$this->_mysqli->xxx

You can't use the $_mysqli object like that inside methods, you have to reference it as $this->_mysqli.

So...

public function usersinserts( $UserFullName, $UserEmail, $UserLastPassword, $UserName, $UserActive) {
    $sql = "INSERT ..."; //whatever it is
    if (!$this->_mysqli->query($sql)) {
        trigger_error($this->_mysqli->error);
    }
    return $this->_mysqli->affected_rows;
}

Hope that helps.