PHP/MySQL: Dynamic prepared statement with insert/update query

Question

I found this http://net.tutsplus.com/tutorials/php/the-problem-with-phps-prepared-statements/

and it works really good to have it in a seperate php file which my other files calls to with a query as argument.

Is it possible to make something similar with other queries like insert and update?

I've seen the tutorial. It sucks (it's based on a wrong assumption, you don't need to bind the results to variables. You can use $row["title"] etc to do whatever you want. — Madara's Ghost
– Madara's Ghost, Commented Jul 14, 2012 at 14:35

Alex Ball · Accepted Answer · 2012-07-14 20:12:18Z

1

This is the updated example:

$params is an array.

 function insertToDB($params, $db) { //Pass array and db

        $fields = array();
        $conn = new mysqli('localhost', 'root', 'root', 'db') or die('XXX');     
        $stmt =  $conn->stmt_init();
        $stmt->prepare("SELECT * FROM ".$db); 
        $stmt->execute();
        $meta =  $stmt->result_metadata();
        while ($field = $meta->fetch_field()) { 
             $fields[] = $field->name;   
        }

        $fields = implode(", ", $fields);


        $placeholders = implode(',', array_fill(0, count($params), '?'));

        $types = '';
        foreach($params as $value) {
            $types.= substr(strtolower(gettype($value)), 0, 1); 
        }

        $ins = "INSERT INTO MYDB (".$fields.") VALUES (".$placeholders.")"; 

        $bind_names[] = $types; 
        for ($i = 0; $i < count($params); $i++) { 
            $bind_name = 'bind' . $i;
            $$bind_name = $params[$i];
            $bind_names[] = &$$bind_name;
        }
        if ($stmt->prepare($ins)) {
                call_user_func_array(array($stmt,'bind_param'),$bind_names); 
                $insresult = $stmt->execute(); 
        }
        return $insresult;
        $stmt->close();
    }

edited Jul 14, 2012 at 20:12

answered Jul 14, 2012 at 13:03

Alex Ball

4,4842 gold badges20 silver badges23 bronze badges

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

3 Comments

Oskar Persson Over a year ago

So I have to execute a select query to then execute a insert query? I would like a method that takes three arguments (2 arrays and one string). The first could be values, then columns and last the table name. Then the method should make prepared statement query from those values. Though I can't figure out how. Tried some of the code you wrote above but I can't get it working.

Alex Ball Over a year ago

@Oskwish, You should execute the first query to retireve the metadata of table (put it in $fields array) and then build a $ins query, with the $field array and the $params (an array of value that you want to insert in). With this method you can pass First argument the values (in this case $params), second arg the columns, you do not need because you retrieve it by first query and put it in $fields array, third arg the table, this you can pass what yo want...

Oskar Persson Over a year ago

Yeah, don't mind by first comment. Your code works great, though I don't use the select query, instead I sends columns, values and table as parameters. Thanks for your help! And to be sure, this is a safe way of inserting data, right?

Collectives™ on Stack Overflow

PHP/MySQL: Dynamic prepared statement with insert/update query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related