Adding insert and Update queries problems

[davidjennings]

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;

    }

}

[pritaeas]

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 ?

[davidjennings]

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

[pritaeas]

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

[davidjennings]

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

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

[davidjennings]

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

[pritaeas]

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

[davidjennings]

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

[pritaeas]

You'll need to add a return.

[davidjennings]

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

[pritaeas]

What's the error?

I meant:

return $this->_mysqli->affected_rows;

[davidjennings]

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;

    }



}

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

[davidjennings]

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

[diafol]

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

[davidjennings]

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

Thanks

David

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