4

Below is part of a PHP database class someone else wrote, I have removed about 80% of it's code, all the un-related code to my question has been removed and just the amount remains that allows me to test this class without actually hitting a real database.

This class has a couple methods that let you set a key and value it then turns it into a mysql UPDATE and INSERT sql query using an array. I am trying to figure out how to use this code 100% so I can use this feature of it for UPDATE and INSERTS in my own application.

Basicly from what I gather you do something like this... 

// assign some key/values to insert into DB
$db->assign('name', 'dfgd');
$db->assign('age', 87);
$db->assign('sex', 'female');
$db->assign('user_id', 4556);

// Do  the insert
$db->insert('testing2');

Now where I am confused is I can keep on running code like this over and over on the page and it always will use the correct set of key/value array sets. Above you can see I used the assign() method 4 times and then call the insert() method which creates this

INSERT INTO test (name, age, sex, user_id) VALUES (jason davis, 26, male, 5345)

Now if I run another set like this on the same page...

// assign some key/values to insert into DB
$db->assign('name', 'dfgd');
$db->assign('age', 87);
$db->assign('sex', 'female');
$db->assign('user_id', 4556);

// Do  the insert
$db->insert('testing2');

It then creates this... 

INSERT INTO testing2 (name, age, sex, user_id) VALUES (dfgd, 87, female, 4556)

So how does it not combine the 2 sets of 4, so instead of inserting 8 record on the second insert, it completey replaces the first set of 4 values with the new set. This is great and what I want but I do not understand how it is happening? Also can this be improved anyway?

Below is a full class and my demo code, it can be ran without needing to connect to mysql for this demo, it will print to screen the SQL that it builds.

Also where would the public function reset() in the code below need to be used at, or would it not be needed?

<?php 
class DB{
    public $fields;

    public function assign($field, $value){
        $this->fields[$field] = ($value)==""?("'".$value."'"):$value;
    }

    public function assign_str($field, $value){
        $this->fields[$field] = "'".addslashes($value)."'";
    }

    public function reset(){
        $this->fields = array();
    }

    public function insert($table){
        $f = "";
        $v = "";
        reset($this->fields);
        foreach($this->fields as $field=>$value){
            $f.= ($f!=""?", ":"").$field;
            $v.= ($v!=""?", ":"").$value;
        }
        $sql = "INSERT INTO ".$table." (".$f.") VALUES (".$v.")";
        //print SQL to screen for testing
        echo $sql;
        //$this->query($sql);
        return $this->insert_id();
    }

    public function update($table, $where){
        $f = "";
        reset($this->fields);
        foreach($this->fields as $field=>$value){
            $f.= ($f!=""?", ":"").$field." = ".$value;
        }
        $sql = "UPDATE ".$table." SET ".$f." ".$where;
        echo $sql;
        //$this->query($sql);
    }

    public function query($_query){
        $this->query = $_query;
        $this->result = @mysql_query($_query, $this->link_id) or die( $_query."<p>".mysql_error($this->link_id) );
        return $this->result;
    }

    public function insert_id(){
        return @mysql_insert_id($this->link_id);
    }
}


// start new DB object
$db = new DB;

// assign some key/values to insert into DB
$db->assign('name', 'jason davis');
$db->assign('age', 26);
$db->assign('sex', 'male');
$db->assign('user_id', 5345);

// Do  the insert
$db->insert('test');


echo '<hr />';

// assign some key/values to insert into DB
$db->assign('name', 'dfgd');
$db->assign('age', 87);
$db->assign('sex', 'female');
$db->assign('user_id', 4556);

// Do  the insert
$db->insert('testing2');


echo '<hr />';

// assign some key/values to UPDATE the DB
$db->assign('name', 'jason davis');
$db->assign('age', 26);
$db->assign('sex', 'male');
$db->assign('user_id', 5345);

// DO the DB UPDATE
$db->update('blogs', 'WHERE user_id = 23');

?>
Improve this question
3
  • You might want to accept an answer now before too many people keep arguing about the 'meaning' of this question lol. Commented Jan 18, 2010 at 3:09
  • My rep is exactly 7,777. At least it is 1,111 lower. Commented Jan 18, 2010 at 3:13
  • Since function reset() modify the $this->fields, you can call it without parameter, just do $this->reset(). However your database abstraction above lack value sanitation and vulnerable to sql injection. See my suggestion below for using AdoDB library for a better security and peace of mind :) Commented Jan 18, 2010 at 5:01

4 Answers 4

Reset to default
2

Key in associative arrays are unique; assigning a new value erases the old.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You're half-wrong. Keys are replaced but that's not how the class should work (inspect the code and examples carefully).
It keeps assigning to $this->fields[$field]. How does what I said not explain it?
His question was "How does this PHP know which array key/values to use". This would be a correct answer to that question.
@Chacha102: This would be a correct answer to the title of the question, (un?)fortunately that's not all we have to pay attention to.
2

If you still open for another database abstaction library, I want to suggest you to use AdoDB. It's can connect to multiple database, so you code will stay the same if you decide to switch database later. It have build in feature to sanitize data before insert/update.

For your code above, when you use AdoDB, you will write it like this:

$adodb =& ADONewConnection($dsn);
$data['name'] = 'dfgd';
$data['age'] = 87;
$data['sex'] = 'female';
$data['user_id'] = 4556;

// Do  the insert
$result = $adodb->AutoExecute($table_name, $data, 'INSERT');

//If update, must have one of the key, such as id column
$result = $adodb->AutoExecute($table_name, $data, 'UPDATE', "id=$id");

You can read the documentation from the site, or inside zip file that you can download. I always use this library in all my project, even I prefer it more that build in CodeIgniter database library.

Improve this answer

Comments

0

insert() and update() should (originally) set the $this->fields property back to an empty array upon execution, but you somehow (wrongly) deleted that code?

Update your code to this:

    public function insert($table){
        $f = "";
        $v = "";
        foreach($this->fields as $field=>$value){
            $f.= ($f!=""?", ":"").$field;
            $v.= ($v!=""?", ":"").$value;
        }
        $sql = "INSERT INTO ".$table." (".$f.") VALUES (".$v.")";
        $this->reset();
        //print SQL to screen for testing
        echo $sql;
        //$this->query($sql);
        return $this->insert_id();
    }

    public function update($table, $where){
        $f = "";
        foreach($this->fields as $field=>$value){
            $f.= ($f!=""?", ":"").$field." = ".$value;
        }
        $sql = "UPDATE ".$table." SET ".$f." ".$where;
        $this->reset();
        echo $sql;
        //$this->query($sql);
    }
Improve this answer

1 Comment

The issue is not that the class doesn't work, but rather that it does work, even with this apparent "bug".
0

Ok, we have come to the conclusion that my previous answer was right:

Because you use the name keys, it replaces the old keys with the new keys.

$db->assign('user_id', "1");

basically does this:

$this->fields['user_id] = (1)==""?("'1'"):1;

And when you got to do it again, it replaces it 

$this->fields['user_id'] = (2)==""?("'2'"):2;

Try doing an assign, and then only assign the user_id again, there rest of the data will stay the same.

To fix this problem, we would call the $this->reset() function after a query.

public function query($_query){
        $this->query = $_query;
        $this->result = @mysql_query($_query, $this->link_id) or die( $_query."<p>".mysql_error($this->link_id) );
        $this->reset();
        return $this->result;
    }

or you could call it in the individual insert or update functions:

public function insert($table){
    // .... stuff
    $this->query($sql);
    $this->reset();
    return $this->insert_id();
}

The other possibility is that the original programmer didn't convey his intent to you well enough. He might expect you to call $db->reset() after every query.

Improve this answer

17 Comments

-1: reset — Set the internal pointer of an array to its first element. Should be $this->reset();
@Alix Correct, it wouldn't work in a real setting. But, I (being a stubborn programmer) must argue that I was correct in explaining the phenomenon that was happening, that made it appear as though it was able to not mix in the old fields with new fields.
And yes all the table names and fields are made up, I was just trying to see how this class build the SQL part, in production I would probably run many different sets of updates and inserts on asome pages and they would all have different key/values most likely
@jasondavis You should be able to simply use the fixes that either I or @alix have created.
@jasondavis that might have been the programmers original intent, for you to call reset after every query. Would make sense why the function was public too.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.