0

I have a database contains ID and name of my Staff (DATABASE 1):

--------------
| ID | name  |
--------------
| 1  | Mr.AA |
| 2  | Mr.AB |
|... | ...   |
| 78 | Mr.CZ |
--------------

Then my colleague has the staff absence database per day for 2 years (DATABASE 2):

Tablename: Table_for_Mr.AA
--------------------------
| ID | date       | work |
--------------------------
| 1  | 2016-01-01 | Yes  |
| 2  | 2016-01-02 | Yes  |
| 3  | 2016-01-03 | No   |
|... | ...        | ...  |
|730 | 2017-12-31 | Yes  |
--------------------------

Due to our agreement, we hold each database ourselves (2 parties), so each database is stored in different server. Lately I need to get the data from DATABASE 2 to be shown in my website and I can ask my colleague to make PHP file that return the array for each name (www.colleaguewebsite/staff/absence.php?name=Mr.AA).

I already made the new 'workstat' database (DATABASE 3) in my server with this detail:

---------------------------------
| ID | date       | Name | work |
---------------------------------
| 1  | 2016-01-01 |     
| 2  | 2016-01-02 |
| 3  | 2016-01-03 |
|... | ...        |
---------------------------------

this is the best I can do:

$sourceURL = 'www.colleaguewebsite/staff/absence.php'

$sql1= $conn->query("select * FROM staff ");
while($row_1 = $sql1->fetch_array()){
    $name= $row_1 ['name'];

    //getting the absence detail from each staff
    $json_1 = file_get_contents($sourceURL.'?name='.$name);
    $data_1 = json_decode($json_1,true);
    foreach($data_1 as $value_1){
        $date = $value_1['date'];
        $work = $value_1['work'];

        //if the correspondence date is exist then update, otherwise add
        $sql_2 = $conn->query("select * FROM workstat WHERE date='$date' AND name='$name");
        if ($sql_2->num_rows > 0){
            $update=$conn->query("UPDATE workstat set name='$name', work='$work' WHERE date='$date' ");
        }else{
            $addnew=$ob->query("INSERT INTO availability (date, name, work) VALUES ('$date', '$name', '$work'
        }
    }   
}

However, I have some things that bothers:

  1. The required time to execute this script is very long, mostly exceeding the 90 seconds time.
  2. Dirty database. I will have 730 row of data (per day) for each name, so my database 3 will have 730 * 78 person = 56.940 rows with duplicate date (2017-01-01 ... 2017-12-31 for Mr.AA, 2017-01-01 ...2017-12-31 for Mr.AB, etc...).

How can I optimize my code in table design and loading time? Another method than file_get_contents is okay, I hope it's still PHP.

bakanoob
  • 21
  • 2
  • Do you really have separate absence tables for each employee? – Tim Biegeleisen Dec 05 '17 at 01:59
  • Which absence table, the source or the target? Currently the source absence table (DATABASE 2 in other server) is unknown because it's my colleague who made it in his own server, but the target absence table (which get data from database 2: DATABASE 3) is a single database for all employee, not separated. – bakanoob Dec 05 '17 at 02:02
  • Step one, Drug colleague. Step two, steal his data. Step three, enjoy all the speed and freedom of access you need. ( Just kidding ) How, up to date does the data have to be. Can you clone database 1 with a nightly process for example. – ArtisticPhoenix Dec 05 '17 at 02:25
  • @ArtisticPhoenix how I wish I could do that LOL. Unfortunately he insisting that the absence database is his to keep, so well, I only can get it through loading external content and update my own database for the sake of further usage if needed. But with my current knowledge... sigh. – bakanoob Dec 05 '17 at 02:40
  • ask your friend to allow you to send a multiple query so you don't query it 1 piece at a time. – Forbs Dec 05 '17 at 03:10
  • @Forbs I guess that's it. Sigh. I thought I can make this out from my side after he gave me that array(s). :( – bakanoob Dec 05 '17 at 04:58

0 Answers0