Import data from a mysql table

Asked

Viewed 822 times

1

I have a database Mysql and I am trying to import the data from a table that the customer passed to my default table. Using the script below, no error appears, however import is not done, table immovable is still empty:

$sql_antigo = "SELECT * FROM mytable ORDER BY id_imovel ASC";
$exe_antigo = mysqli_query($conection, $sql_antigo);

while($row=mysqli_fetch_assoc($exe_antigo)){
$id = $row['id_imovel'];
$codigo  = $row['codigo'];
$controle = round(microtime(true) * 1000);

$sql_novo = "INSERT INTO imoveis (id,cod_int,controle) VALUES ('$id','$codigo','$controle')";
$exe_novo = mysqli_query($conection, $sql_novo);

echo 'Dados do imóvel ' .$id. ' importados com sucesso!<br>';
}

In the echo, appears the phrase stating that all data has been imported.

php mysql mysqli

  • The echo, as it is, it will be displayed regardless of the table having, or not, recorded the data. Use the mysqli_error to check for errors. Also check the value of $exe_antigo is different from false

  • There may be some errors in the Insert procedure. How is your table created? Does the ID field happen to be auto_increment? If yes, you do not need to specify. .

2 answers

1

You can do this only with an SQL query, I’ll leave an example:

Table structure

CREATE TABLE `tabela` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `campo1` varchar(255),
  `campo2` varchar(255),
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

CREATE TABLE `novaTabela` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `campo2` varchar(255),
  `campo1` varchar(255),
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

Note that the campo1 and campo2 are inverted in one table against another

Fictitious data

INSERT INTO `tabela` (`campo1`,`campo2`) VALUES 
("101","201"),
("102","202"),
("103","203"),
("104","204"),
("105","205"),
("106","206"),
("107","207"),
("108","208"),
("109","209"),
("110","210");

The query that solves the problem

INSERT INTO novaTabela SELECT id, campo2, campo1 FROM tabela;

Instead of going through VALUES (...) you pass a SELECT of the table where the data

Note that instead of making one SELECT *, I selected the columns I wanted and in the order I wanted, so you can adjust the data to the new structure. You can also use the formatting functions, for example, to transform a text into DateTime. How do you pass a SELECT on insertion, you can use the clause WHERE to filter what will be inserted, like, ... WHERE ativo = 1 to add the data that is active

  • The name of the table fields To are different from the table B and probably the structure is also.

  • That’s irrelevant, you could put campo1, foo, would make no difference. As said can be shaped the new structure, probably everything necessary to change can be done via SQL, without anything PHP or other language

  • In the question it creates a random number, Mysql has a function for this: RAND(), which can be multiplied, squared, rounded, etc. It has enough available functions

-1

Thanks for the help, guys. I solved it, but in the most complicated way. I generated a file for import via phpMyadmin, as follows:

echo "INSERT INTO immobles (code,cod_int,control) VALUES ('0','$id','$control');
"; }

I pasted the result in the notepad, removed the last semicolon and made the import. Everything worked fine.

Browser other questions tagged php mysql mysqli

You are not signed in. Login or sign up in order to post.