0

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;

    }

}

Edited by davidjennings

3
Contributors
17
Replies
72
Views
3 Years
Discussion Span
Last Post by diafol
Featured Replies
  • 1
    diafol 3,669   3 Years Ago

    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 … Read More

0

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 ?

0

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

Edited by davidjennings

0

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).

0

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);
     }
   }
0
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...

Edited by diafol

0

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

0

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
0

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

0

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;
   }
0

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;

    }



}

Edited by davidjennings: change line no

1
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.

0

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

Edited by davidjennings

0

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

0

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.

Edited by diafol

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.