表之间的php pdo复制列

I got the two tables(Table1 and Table2):

Table1:
id      hits    url    
1       11      a    
2       5       b    
3       6       c        
4       99      d    
5       14      e


Table2:
id      url    2014.04.13    2014.04.14
1       a      0             5
2       b      0             1
3       c      0             3
4       d      0             60
5       e      0             10

hi all,

Table1 one contains the actual hits(which are always up-to-date) and Table2 to statistics(which are done every day at midnight). The columns id(unique number) and url are in both tables the same. So they got the same amount of rows.

So i create every day a new column(with the date of today) and copy the column hits from the table 'Table1' into the new created column into the table 'Table2'

First i alter Table2:

$st = $pdo->prepare("ALTER TABLE Table2 ADD `$today_date` INT(4) NOT NULL");
$st->execute();

Then i cache all entries i need from Table1:

$c = 0;
$id = array();
$hits = array();

$sql = "SELECT id, hits FROM Table1 ORDER BY id ASC";

$stmt = $pdo->query($sql);
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $id[$c] = $row['id'];
    $hits[$c] = $row['hits'];
    $c++;
}

At last i update Table2:

for ($d = 0 ; $d < $c ; $d++)
{
    $id_insert = $id[$d];
    $sql = "UPDATE DOWNLOADS_TEST SET `$datum_det_dwnloads`=? WHERE id=?";  
    $q = $pdo->prepare($sql);  
    $q->execute(array($hits[$d], $id[$d]));

    if($q->rowCount() == 1 or $hits[$d] == 0) // success
        $hits[$d] = 0;
    else // error inserting (e.g. index not found)
        $d_error = 1; // error :( //
}

So what i need is to copy(insert) a column from one table to another.

The two tables are having ~2000 elements and the copying as described above takes around 40 sec. The bottleneck is the last part (inserting into the Table2) as i found out.

One thing i found is to do multiple updates in one query. Is there anything i can do besides that?

I hope you realise that at some point your table will have irrational number of columns and will be highly inefficent. I strongly advise you to use other solution, for example another table that holds data for each row for each day.

Let's say you have a table with 2000 rows and two columns: ID and URL. Now you want to know the count of hits for each URL so you add column HITS. But then you realise you will need to know the count of hits for each URL for every date, so your best bet is to split the tables. At this moment you have one table:

Table A (A_ID, URL, HITS)

Now remove HITS from Table A and create Table B with ID and HITS attributes). Now you have:

Table A (A_ID, URL)
Table B (B_ID, HITS)

Next move is to connect those two tables:

Table A (A_ID, URL)
Table B (B_ID, A_ID, HITS)

Where A_ID is foreign key to attribute "A_ID" of Table A. In the end it's the same as first step. But now it's easy to add date attribute to Table B:

Table A (A_ID, URL)
Table B (B_ID, A_ID, HITS, DATE)

And you have your solution for database structure. You will have a lot of entries in table B, but it's still better than a lot of columns. Example of how it would look like:

Table A | A_ID |    URL
           0      index
           1      contact

Table B | B_ID | A_ID | HITS | DATE
           0      0      23    12.04.2013
           1      1      12    12.04.2013
           2      0      219   13.04.2013
           3      1      99    13.04.2013

You can also make unique index of A_ID and DATE in Table B, but I prefer to work on IDs even on linking tables.