TalkPHP - View Single Post

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.

11-06-2008, 03:18 PM	#5 (permalink)
code_junkie The Contributor Join Date: Sep 2008 Posts: 39 Thanks: 9	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. __________________ Trying to learn all I can about PHP. Teach me what you know...