CODEWITHSUNDEEP
phpmysql
nicolas
nicolas

Reputation: 75

How copy data from one table into another? PHP

Following problem there are 2 tables, "Daten" and "Server" in my Mysql DB called "csv" they both got "ServerID" inside them as a column. How can I copy the data in column "ServerID" from the table "Server" to "Daten".

I probably have to say the numbers in "Server" "ServerID" are AUTO-Increment.

The result I want is: in ServerID is a number and I want that number in the other table too.

I tried this but its only giving me: "111111111111111111111111111111111111"

$con=mysqli_connect("localhost","THUNI","root","csv");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="SELECT ServerID FROM Server";
$result=mysqli_query($con,$sql);


 //Associative array
$row=mysqli_fetch_array($result, MYSQLI_ASSOC);
printf ($row["ServerID"]);

// Free result set
mysqli_free_result($result);

Can someone help me?

Upvotes: 3

Views: 14430

Answers (4)

nicolas
nicolas

Reputation: 75

Thanks to everyone I did it, it was not that hard. The solution are functions, with funcitons you can insert everything at the same time and thats what I wanted. A function could look like this:

Function getserverID($Servername)
{
    //Check if the entery is already in the DB
    //to provide double enteries
    $result = mysql_query("select * from Server where Servername = lower('$Servername')");
    $num_rows = mysql_num_rows($result);
    if ($Servername =="avlrbrcl03"){
    //echo ($num_rows);
    }
        //If it is not existing, create the entery  
        if($num_rows == 0){
            $import="INSERT into Server(Servername) values(lower('$Servername'))";
                mysql_query($import) or die(mysql_error());
                $ID=mysql_insert_id();

                //and if existing save it as a variable
                }else{
                    $row = mysql_fetch_assoc($result);
                    $ID= $row["ServerID"];
                    }

        return $ID;
}

If you have already done this you have to create a While Loop to import the datas. This could look like this:

while(($data = fgetcsv($handle, 1000, ";")) !== FALSE)
    {
    $Storagetyp = getstoragetyp($_FILES['filename']['name']);
    $ServerID = getserverID($data[0]);
    $Datum = getdatumID($data[2]);

    //Check if there is already such a entry
    $result = mysql_query ("select * from Daten where ServerID = '$ServerID' and Speicherplatz = '$data[1]' and DatumID = '$Datum'");
    $num_rows = mysql_num_rows($result);

        //If it doesn't exist, import it
        if($num_rows == 0){
            $import="INSERT into Daten(Speicherplatz, DatumID, ServerID, Storagetyp) values('$data[1]','$Datum','$ServerID','$Storagetyp')";
                mysql_query($import) or die(mysql_error());
            }   
}

Upvotes: 1

Hearner
Hearner

Reputation: 2729

INSERT INTO Server (ServerID)
SELECT ServerID FROM Daten;

That will insert into Server the ServerID from Daten

The correct way to copy datas from a table to another is :

INSERT INTO table2
SELECT * FROM table1;

Or 

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

In your script, it should be something like that

$con=mysqli_connect("localhost","THUNI","root","csv");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="Insert into Server 
        Select * from Daten";
mysqli_query($con,$sql); // That inserts the data from Daten to Server

$query = "SELECT ServerID FROM Server";
$result = mysqli_query($con,$query); // You're selecting ServerID in Server

echo "The result are ". mysqli_fetch_array($result)['ServerID']; // It gives all ServerID from Server

Upvotes: 2

shankar kumar
shankar kumar

Reputation: 648

Use following code to do it.

<?php 

$con=mysql_connect("localhost","root","THUNI");
mysql_select_db("csv",$con);

$sql="INSERT INTO  Daten (ServerID)
 SELECT ServerID FROM Server";
 mysql_query($sql); 
?>

Upvotes: 0

Amit Shah
Amit Shah

Reputation: 1380

Make sure you have same schema for both the table.

table 1:

CREATE TABLE IF NOT EXISTS `email` (
  `email` varchar(255) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

table 2:

CREATE TABLE IF NOT EXISTS `email_table` (
  `email` varchar(255) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

your query in php code would be:

insert into email_table (select * from email);

Let me know in case any query :)

Thanks Amit

Upvotes: 0

Related Questions