How to create insert() function inside a class for rapid development

Question

Here is the functions I've created so far

class MySQLiDatabase{

/**
 * Database credentials
 */
private $db_host = ''; // localhost
private $db_name = ''; // database name
private $db_user = ''; // database uer
private $db_pass = ''; // database password

/**
 * create conneaction
 * @string
 */
private $connection;

/**
 * start connection as soon as this file gets load
 */
public function __construct(){
    $this->connect();
}

/**
 * connect to the database
 */
public function connect(){
    $this->connection = mysqli_connect($this->db_host,$this->db_user,$this->db_pass);
    if(!$this->connection){
        die ("Database connection failed: " . mysql_error());
    }else{
        $select_db = mysqli_select_db($this->connection,$this->db_name);
        if(!$select_db){
            die("Database selection failed: " . mysql_error());
        }
    }
}

/**
 * close mysql connection
 */
public function close_connection(){
    if(isset($this->connection)){
        mysqli_close($this->connection);
        unset($this->connection);
    }
}

/**
 * run database query
 */
public function query($sql){
    $result = mysqli_query($this->connection,$sql);
    $this->confirm_query($result);
    return $result;
} // end query($sql)

/**
 * confirm mysql query 
 * @param string
 */
public function confirm_query($result){
    if(!$result){
        die ("Database query failed: " . mysqli_errno());
    }
}

/**
 * Insert into database
 * @param string
 * return TRUE
 */

public function insert($table, $keys, $values){     
    $condition = "INSERT INTO $table ($keys) VALUES('".$values."')";
    $query = $this->query($condition);
    return $query;
}

}

$database = new MySQLiDatabase();
$db =& $database;

Now I would like to create insert() function which is working fine but when I am inserting more data in it its giving me error which I have defined in confirm_query() functions

$database->insert('table', 'username', 'test');

I want to acomplish something like below

$database->insert('table', 'username,password,email', 'test,test,test@email.com');

This is because your values should be in quotes individually. Like "test", "test", "test@gmail.com" — VIDesignz, Nov 28 '15 at 16:06
Learn how to use prepared statements/paramitized queries to prevent sql injection - [How can I prevent SQL-injection in php](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Sean, Nov 28 '15 at 16:08
Also, your idea may seem intuitive but as you move forward you may find this 'simple solution' is hard to maintain. You are better off building methods specifically for each database transaction, such as `$database->insertNewUser($username, $password, $email);` — VIDesignz, Nov 28 '15 at 16:12
And like @Sean said, learn prepared statements...you'll be happier you did, — VIDesignz, Nov 28 '15 at 16:18
I believe it would have to be like this `$database->insert('table', 'username,password,email', '"test","test","test@email.com"');` — VIDesignz, Nov 28 '15 at 18:29
@VIDesignz NO! I have tested it out its not working as well. — shaz3e, Nov 28 '15 at 22:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96445/discussion-between-videsignz-and-shaz3e). — VIDesignz, Nov 28 '15 at 22:27
Also, why not just use prepared statements since you are building this from the start. You will be much happier if you do it now instead of down the road after you extend your new class. — VIDesignz, Nov 28 '15 at 22:50

score 1 · Answer 1 · answered Dec 05 '15 at 10:54

Finally the problem got solved Thanks @VIDesignz for helping me so far with "quots"

Here is the function

public function insert($table, $column, $value){
    $condition = "INSERT INTO $table ($column) VALUES ($value)";
    $query = $this->query($condition);
    return $query;
}

and this is how I am running my query

$database->insert( "table" , 'username,password,email' , " 'shaz3e' , 'securepassword', 'email@email.com' ");

next is to create Update/Delete functions

score 0 · Answer 2 · answered Nov 28 '15 at 16:30

Ok, I can't say your approach is the best but if you are stuck on doing it this way. At least use prepared statements....

(Not tested but should work fine)

Set up the function like this

 public function insert($table, $columns, $values, $execute){
    $sql = "INSERT INTO
            $table ($columns)
            VALUES ($values)"; 
    $query = $database->prepare($sql);
    $query->execute($execute);      
 }

Then with each call you have to set the values like this

    $table = 'table_name';
    $columns = 'column_1, column_2, column_3';
    $values = ':value_1, :value_2, :value_3';
    $execute = array(':value_1' => 'First Value',
                     ':value_2' => 'Second Value',
                     ':value_3' => 'Third Value');

then run the function like this

 $database->insert($table, $columns, $values, $execute);

I quite now using codeigniter hope I'll get success what I am trying to do — shaz3e, Nov 30 '15 at 15:57

How to create insert() function inside a class for rapid development

2 Answers2