SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting 31102 records from an existing table

    I want to insert 31102 records into a db table from an html table.

    I wanted to make it dependent on the number of the page. When page=1 then it would insert the first set of records. WHen it's 2 the 2nd set. This way the browser won't freeze.
    PHP Code:
    $num=round(count($trInnerHTML[1])/32);
    echo 
    "<span style=\"color: orange;\">".$num."</span><br />\n";

    $limit=($page+1)*$num;
    $tds = array();
    for(
    $getTds=($page-1)*$num$getTds<$limit$getTds++){//counting the trs
        
    if($getTds!==0){
            
    preg_match_all("#<td.*>(.+)</td#Ui"$trInnerHTML[1][$getTds], $tdInnerHTML);
            
    //print_r($tdInnerHTML[1]);
            
    echo "<span style=\"color: red;\">".count($tdInnerHTML[1])."</span>";
            
    //echo "<br />\n";
            
    for($splitTds=0$splitTds<count($tdInnerHTML[1]); $splitTds++){
                if(
    $splitTds!==0){
                    
    $tds[] = addslashes($tdInnerHTML[1][$splitTds]);
                    echo 
    "<span style=\"color: blue;\">".$tdInnerHTML[1][$splitTds]."</span><br /\n>";
                }
                if(
    $splitTds==count($tdInnerHTML[1])-1){
                    
    $tdarr implode("', '"$tds);
                    
    $sql "INSERT INTO ".$dbTable3." (".$fieldarr.") VALUES ('".$tdarr."')";
                    echo 
    "<span style=\"color: green;\">".$sql."</span><br /\n>";
                    
    mysql_query($sql) or die(mysql_error());
                    
    $tds = array();
                }
            }
        }
    }
    mysql_close($con);

    //then redirect them to the index
    $add=$page+1;
    echo 
    "Location: create_bible.php?type=create&table=bible&page=".$add;
    header("Location: create_bible.php?type=create&table=bible&page=".$add); 
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    And the question is?

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    And the question is?
    How do you associate the $page with the for loop? So that the 1st page will insert the 1st set of records then the next page will insert the next set...
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's this area that I'm looking for clarification:
    PHP Code:
        $start=$page*$num;
        
    $limit=$page*$num;
        
    $tds = array();
        echo 
    "page = ".$page;
        for(
    $getTds=$start$getTds<$limit$getTds++){//counting the trs 
    You might see some changes from the code above since I'm working on this.
    Compare bible texts (and other tools):
    TheWheelofGod

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Let me clarify further. Since
    31102/32=971.9375
    Rounded up to 972
    as mentioned in this formula:
    PHP Code:
    $num=round(count($trInnerHTML[1])/32); 
    Each page should have 972 records to insert in the database table.
    But page 1 will insert records 1-972;
    page 2 will insert records 973-1945 (which is 973+972);
    page 3 will insert records 1946-2918 (which is 1946+972);
    and so on up to 31102.
    Compare bible texts (and other tools):
    TheWheelofGod

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how often will this occur?
    how are the 31102 records being created?

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by litebearer View Post
    how often will this occur?
    how are the 31102 records being created?
    The records are already in a table and are taken to be inserted:
    PHP Code:
    $file="saveddbtable/bible.htm";
    $contents_of_page file_get_contents($file);
    preg_match_all("#<th.*>(.+)</th#Ui"$contents_of_page$thInnerHTML);

    //looking for the tds
    preg_match_all("#<tr.*>(.+)</tr#Ui"$contents_of_page$trInnerHTML); 
    Compare bible texts (and other tools):
    TheWheelofGod

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gilgalbiblewheel View Post
    The records are already in a table
    the records are already in a table, and you want to insert them into another table?

    forget the paging

    just issue a single INSERT/SELECT statement and they're all copied over easily (and efficiently) in one step
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the records are already in a table, and you want to insert them into another table?

    forget the paging

    just issue a single INSERT/SELECT statement and they're all copied over easily (and efficiently) in one step
    I'm testing it on my HeidiSQL but I can't do the same once I upload on the server.
    Compare bible texts (and other tools):
    TheWheelofGod

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gilgalbiblewheel View Post
    ...but I can't do the same once I upload on the server.
    you cannot issue an SQL statement?

    isn't that an INSERT statement in post #1 ?

    why do you think you can't run an INSERT statement with the SELECT option?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    $file="saveddbtable/bible.htm"; 
    $contents_of_page = file_get_contents($file);
    This is not a table. It's a text file.

  12. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    730
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    This is not a table. It's a text file.
    Yes I know. But within the text file is a huge table but I figure before getting into that I should've looked into the reg exp first:
    http://www.sitepoint.com/forums/php-...em-745391.html
    Compare bible texts (and other tools):
    TheWheelofGod


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •