Wednesday, December 26, 2018

phpMyPassion

Get A Insert Query Statement For All Rows in MySql

This is a common thing that usually required for back end programming.  Sometimes we need all rows of a table as an query statement just like -

INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (100,'some vlaue','some value','2018-10-20');

So the question is "How do we get the insert query statement for this easily ?"

Here I am gonna write a simple PHP function by that you can get the insert query statement for all rows in a table :-

<?php
function makeInsertQuery($mysqli,$table, $where=null) {
    $sql="SELECT * FROM `{$table}`".(is_null($where) ? "" : " WHERE ".$where).";";
    $result=$mysqli->query($sql);

    $fields=array();
    foreach ($result->fetch_fields() as $key=>$value) {
        $fields[$key]="`{$value->name}`";
    }

    $values=array();
    while ($row=$result->fetch_row()) {
        $temp=array();
        foreach ($row as $key=>$value) {
            $temp[$key]=($value===null ? 'NULL' : "'".$mysqli->real_escape_string($value)."'");
        }
        $values[]="(".implode(",",$temp).")";
    }
    $num=$result->num_rows;
    return "INSERT `{$table}` (".implode(",",$fields).") VALUES \n".implode(",\n",$values).";";
}
?>


When you pass tableName to @makeInsertQuery($tableName) function. You will get below output..

INSERT INTO `tableName` (`id`, `parent_id`, `name`, `type`) VALUES
(1103, 1019, 'ios11.2.2', 'os'),
(1104, 1019, 'ios11.2.5', 'os'),
(1105, 1017, 'iosos_version', 'os'),
(1106, 1019, 'ios11.0.1', 'os'),
(1107, 1018, 'ios10.2.1', 'os'),
(1108, 1019, 'ios11.1.1', 'os'),
(1109, 1025, 'ios9.1', 'os'),
(1110, 1019, 'ios11.0.3', 'os'),
(1111, 1019, 'ios11.2.1', 'os'),
(1112, 1019, 'ios11.1.2', 'os'),
(1113, 1018, 'ios10.3.1', 'os'),
(1114, 1018, 'ios10.3.3', 'os'),
(1115, 1019, 'ios11.2.6', 'os'),
(1116, 1019, 'ios11.2', 'os'),
(1117, 1018, 'ios10.1.1', 'os');

So using above function you can get insert query statement for a table in MySql.

You can also get insert query statement by dumping or exporting the table into .sql file. So you can dump your table data with executing below command on terminal.

sudo mysqldump -u USERNAME -p DATABASE tableName > tableName.sql;

Windows users can export from MySql workbench:-


If you open the .sql file into editor, you will find query statement same as above. So just copy the all insert query statement and execute where you want.


About Author -

Hi, I am Anil.

Welcome to my eponymous blog! I am passionate about web programming. Here you will find a huge information on web development, web design, PHP, Python, Digital Marketing and Latest technology.

Subscribe to this Blog via Email :

Note: Only a member of this blog may post a comment.