How to copy multiple row from a table and insert those to another table in MySql DB with PHP?

Question

<?php
    $conn = mysqli_connect('hostname','username','passwd','dbname');
    $query = 'SELECT name,mobileno FROM test';
    $result = mysqli_query($conn,$query);

    while($row = $result->fetch_array()) {
        $name = $row['name'];
        $monileno = $row['mobileno'];

        $query2 = "INSERT INTO test2(name2,mobileno2) VALUES('$name','$monileno')";
        mysqli_query($conn,$query2);
       }
?>


CREATE TABLE `test` (
  `id` INT(9) AUTO_INCREMENT PRIMARY KEY, 
  `name` varchar(128) NOT NULL,
  `mobileno` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `name`, `mobileno`) VALUES
(1, 'Omar Faruq', '01911775477'),
(2, 'omar Al Faruq', '01911775477'),
(3, 'Helal Uddin', '01914351075');

CREATE TABLE `test2` (
  `id` INT(9) AUTO_INCREMENT PRIMARY KEY, 
  `name` varchar(128) NOT NULL,
  `mobileno` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This code works on Xampp/wamp but when I upload this script to RHEL7.2 web server it does not works.

Possible duplicate of [INSERT with SELECT](https://stackoverflow.com/questions/5391344/insert-with-select) — Your Common Sense, Feb 28 '18 at 12:08

score 0 · Answer 1 · answered Mar 06 '18 at 02:52

<?php
$conn = mysqli_connect('hostname','username','passwd','dbname');
$query = 'SELECT name,mobileno FROM test';
$result = mysqli_query($conn,$query);

while ($row = mysqli_fetch_assoc($result)) { 
  $name = mysqli_escape_string($conn, $row['name']);
  $monileno = mysqli_escape_string($conn, $row['mobileno']); 

$query2 = "INSERT INTO test2(name2,mobileno2) VALUES('$name','$monileno')";
mysqli_query($conn,$query2);
?>

and change on Database :

CREATE TABLE test2 ( id INT(9) AUTO_INCREMENT PRIMARY KEY, name2 varchar(128) DEFAULT NULL, mobileno2 varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Finally it works on RHEL7. I am really happy...... Thanks All

yosher lutski · Answer 2 · 2018-02-28T12:54:58.810

-2

try replacing

    while($row = $result->fetch_array()) {

with

    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    // OR
    while ($row = mysqli_fetch_assoc($result)) {

It seem's like you we're mixing between the object-oriented usage style and the procedural style.

I'm surprised this worked for you. probably might be due to different php versions.

same thing can apply to

VALUES('$name','$monileno')";
//replace with
VALUES('" . mysqli_escape_string($conn, $name) . "', '" . mysqli_escape_string($conn, $monileno) . "')";

edited Feb 28 '18 at 12:54

answered Feb 28 '18 at 11:51

yosher lutski

306
2
8

That code is still highly vulnerable. What if the name is given as `Little ' Bobby Tables`? – Nico Haase Feb 28 '18 at 11:53
I noted it above the code. Though I was trying to find why his code is not working and not pentest it. – yosher lutski Feb 28 '18 at 11:54
I also treated special strings like @Nico noted. Let's see what op says – yosher lutski Feb 28 '18 at 12:15
`myqsli_escape_string` has a typo in it, and the method `mysqli_escape_string` needs two arguments..... – Nico Haase Feb 28 '18 at 12:53
Edited, sorry I typed from my phone :) – yosher lutski Feb 28 '18 at 12:58

How to copy multiple row from a table and insert those to another table in MySql DB with PHP?

2 Answers2