Mount INSERT from SELECT

Question

I’d like to make a select and from it to generate the insert.
I want to start from the prepost that I don’t know the table structure, the number of columns, nothing but the table name.

select * from cliente where nome like 'PEDRO%';

Viraria:

insert into cliente(.......) values(.......);
insert into cliente(.......) values(.......);
insert into cliente(.......) values(.......);
insert into cliente(.......) values(.......);
insert into cliente(.......) values(.......);

Is there any way to do this directly on mysql ?
If not, with the php is possible somehow ?

My intention with this is to save this result in a file .sql and leave it in the machine.
If the mysql manage that output, export via php, otherwise, the php should generate this expected output. But I don’t know the structure of the table, so I can’t tell which fields would be filled

Answer 1

EDITION

After talking to the Marconi, I realized that you need to know the data type of each field of your table. To do this, I used mysqli_fetch_field and with the values I saved in an array. The values of this function are returned the numbers representing the data type of the table field.

Thanks to this citizen of good we can see the values of each type in this table:

numerics 
-------------
BIT: 16
TINYINT: 1
BOOL: 1
SMALLINT: 2
MEDIUMINT: 9
INTEGER: 3
BIGINT: 8
SERIAL: 8
FLOAT: 4
DOUBLE: 5
DECIMAL: 246
NUMERIC: 246
FIXED: 246

dates
------------
DATE: 10
DATETIME: 12
TIMESTAMP: 7
TIME: 11
YEAR: 13

strings & binary
------------
CHAR: 254
VARCHAR: 253
ENUM: 254
SET: 254
BINARY: 254
VARBINARY: 253
TINYBLOB: 252
BLOB: 252
MEDIUMBLOB: 252
TINYTEXT: 252
TEXT: 252
MEDIUMTEXT: 252
LONGTEXT: 252

And then create a function to know if the value we concatenate requires quotation marks or not.

function getTypeCampo($number){
    $numerics = array('16', '1', '2', '9', '3', '8', '4', '5', '246');
    if(in_array($number, $numerics)){
        return "numeric";
    }
    return "string";    
}

Complete code:

$query = mysqli_query($conn, "SELECT * FROM tabela WHERE campo LIKE 'PEDRO%'");
// string inicial
$campos = "INSERT INTO tabela (";
$valores = "VALUES ";
$status = true;
$titposDeDados = array();
while($value = mysqli_fetch_assoc($query)){
    $key = 0;
    $valores .= "(";
    if($status){
        // pega o tipo de dados e monta o array 
        while($types = mysqli_fetch_field($query)){
            $titposDeDados[] = getTypeCampo($types->type);
        }
        // pega o nome dos campos da tabela e concatena em $campos
        foreach($value as $campo => $valor){
            $campos .= "`".$campo."`,";
             if($titposDeDados[$key] == 'string'){
                 $valores .= "'".$valor."',";
             } else {
                 $valores .= $valor.",";
             }
             $key++;
        }
        $status = false;
    } else {
        // concatena os valores que serão inseridos em $valores
        foreach($value as $valor){
            if($titposDeDados[$key] == 'string'){
                 $valores .= "'".$valor."',"; // se for string (varchar, text, ...)
             } else {
                 $valores .= $valor.","; // se for numerico (integer, decimal, ..)
             }
            $key++;
        }
    }
    $valores = substr($valores, 0, -1);
    $valores .= "),";
}

$insert = substr($campos, 0, -1).") ".substr($valores, 0, -1);
echo $insert; // mostra a string

function getTypeCampo($number){
    $numerics = array('16', '1', '2', '9', '3', '8', '4', '5', '246');
    if(in_array($number, $numerics)){
        return "numeric";
    }
    return "string";    
}

Answer 2

In this case I think it would be easier with php. You’ll need to create a loop with the results. Something like this:

select * from cliente where nome like 'PEDRO%';
$conexaoBanco = mysqli_query;

while($valorSelect = mysqli_fetch_array($resultadoDaQuery)){
    insert into cliente(nome, cpf, exemplo) values($valorSelect['nome'], $valorSelect['cpf'], $valorSelect['exemplo'] );
    $conexaoBanco = mysqli_query;
}

Well I don’t know how you make your connection to the bank, I just put an example there. Anything ask the question how you make the connection.