Loading data form table to table?

code_junkie · 11-04-2008, 01:21 PM

I am having a problem with the last bit of my code. I am trying to make a script that will load data from one table and insert it into another table in the same DB. This is what I got.

PHP Code:


			
$query = "INSERT INTO call_in SELECT time, date, company_id, vehicle_id, stop_no, driver_id, cargo_11, cargo_15, cargo_16, cargo_other FROM online_report VALUE time, date, company_id, vehicle_id, stop_no, driver_id, cargo_11, cargo_15, cargo_16, cargo_other";

  $result = @mysql_query ($query);  //runs the query

  

  if (mysql_affected_rows() == 1) {  //if it ran ok.

    

    echo '<p>The database was updated</p>';

    exit();

        

    } else {  // if did not run ok.

      $message = '<p>The database was not updated</p>';

    }

Can anyone give me some guidance with this issue?

Runar · 11-06-2008, 02:11 AM

Your original query was not working, thou it was only some minor mistakes that caused it to fail. I suggest you take a look at the MySQL Reference on the INSERT syntax.

The following code should work, but note that I have not tested it myself. It will first attempt to copy all records from the table`online_report` to the table `call_in`. Then it will return one message; one for success (if affected rows is higher than 0) or one for failure.

PHP Code:


			
<?php

    // We insert the entire online_report table to call_in
    // Both tables MUST have the exact same structure, or this query will have to be changed
    $query = mysql_query( 'INSERT `call_in` SELECT * FROM `online_report`' );

    // Continue if query succeded
    if( mysql_affected_rows( $query ) > 0 )
    {
        echo '<p>The database was copied!</p>';
    }

    // Something failed
    else
    {
        echo '<p>The database was not copied!</p>';
    }

?>

Please let me know if it works!

Yours,
Runar

code_junkie · 11-06-2008, 02:51 PM

That did not work. Get an error of " Duplicate entry '600-15691869000' for key 1 ". Can I make it ignore that error and insert the data anyway?

Runar · 11-06-2008, 03:00 PM

Since I am not familiar with your database structure, I will have to guess on what exactly causes the problem. Is the table you are copying to empty, or does it contain existing data? Does the entry '600-15691869000' exist in both tables?

If it does contain data, you will either have to delete that data before copying from the other table, or make sure you do not attempt to copy the entries that exist in both tables.

I may be wrong, but give us some more information about your database structure and existing data, and I am sure someone will be able to help you.

code_junkie · 11-06-2008, 03:18 PM

As for the '600-15691869000', I'm not sure what the '600-' part is because thats not in ant of my tables. And for '15691869000' it is a driver number so it will be used many time in both tables.

Let me go over what I am doing. I have a system that records data about truck drivers for when they arrive and depart from their stops. They can either call it in over the phone to an automated system that writes to 1 table OR they call our operator who will enter the data into a different table. Each company that uses our system can check online to monitor the drivers logs.

For the automated system, its data get stored on a different server and sent to the web server (kinda ridiculous if you ask me, but I didn't sent it up), but the catch is it just continues to write to the same file so when it copies to the web server it send the same data plus what ever was added. Then this is what happens:

PHP Code:


			
// Dump table.

  $query = "DROP TABLE call_in";

  $result = @mysql_query ($query);  //runs the query

  

  if (mysql_affected_rows() == 1) {  //if it ran ok.

    

    echo '<p>The database was updated</p>';

    exit();

        

    } else {  // if did not run ok.

      $message = '<p>The database was not updated</p>';

    }  // end table dump.

    

  // Create table call_in.

  $query = "CREATE TABLE call_in (

              time INT UNSIGNED NOT NULL,

            date INT NOT NULL,

            company_id INT NOT NULL,

            vehicle_id INT NOT NULL,

            stop_no INT NOT NULL,

            driver_id BIGINT(11) NOT NULL,

            cargo_11 INT NOT NULL,

            cargo_15 INT NOT NULL,

            cargo_16 INT NOT NULL,

            cargo_other INT NOT NULL,

            PRIMARY KEY (`time`,`driver_id`)

            )";

  $result = @mysql_query ($query);  //runs the query



  // Load data from automated server

  $query = "LOAD DATA LOCAL INFILE 'c:/TRUCKER.csv' INTO TABLE call_in FIELDS TERMINATED BY ','";

  $result = @mysql_query ($query);  //runs the query

  

  $query = "INSERT INTO call_in SELECT time, date, company_id, vehicle_id, stop_no, driver_id, cargo_11, cargo_15, cargo_16, cargo_other FROM online_report";

  $results = mysql_query($query) or die (mysql_error()); 

  $numrows = mysql_num_rows($results);

  

  // Continue if query succeded

    if ($numrows > 1) {

        echo '<p>The database was copied!</p>';

    }



    // Something failed

    else

    {

        echo '<p>The database was not copied!</p>';

    }

   

 

    

    mysql_close();  // close the database connection.

Sorry for the long post. Let me know if you have an other questions or if you need me to explain something more clearly.

Runar · 11-06-2008, 03:34 PM

I apologize. SQL in general is far from my favourite field, so I am not sure if this will help you or not. You could use the following method to only copy certain fields between the two tables, like for cases when one or both of the tables contains unique fields. Maybe that is what you are looking for:

Code:

INSERT `call_in` (`time`, `date`, `company_id`, <more fields>) SELECT `time`, `date`, `company_id`, <more fields> FROM `online_report`