HTML table data into a Access Databse table

Discussion in 'Web Design & Programming' started by hewstone999, Jan 26, 2009.

  1. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    I have a html table that contains data and i want to insert the data into an Access data. Any ideas how to do this?

    Table1:

    01, Rich, Mike, 23, fred lane,
    02, James, milner, 45, School Lane,
    03, Rob, Matthews, 89, College Road,


    I connect to the database using this code:
    $db_conn = new COM("ADODB.Connection");
    $connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./Database1.mdb").";";
    $db_conn->open($connstr);
     
  2. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    Hi hewstone999

    do you mean a html table like this

    HTML:
    <table>
    <tr><td>01</td><td>Mike</td><td>fred lane</td></tr>
    <tr><td>02</td><td>james</td><td>school lane</td></tr>
    </table>
    you want to put that into a database?
     
  3. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    Yes thats correct! Please note the values in the HTML table do change, so i want something like:

    Code:
    "INSERT INTO temp_table1 ("Col1","Col2",".....") VALUES ("Cell 1","Cell 2","....")
    Where "Cell 1 & 2" is the values in the HTML table.
     
  4. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    what language are you using? I can do PHP but will try my best to help if its ASP.
     
  5. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    PHP is fine thank you :)
     
  6. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    you use the script below or use my example

    JS_Extractor! And the death of Table Extractor - Jack Sleight

    mytable.html will contain the actual table contents.

    PHP:
    <?php

      $dom 
    = new DOMDocument();
      
    $dom->loadHTML(file_get_contents('mytable.html'));
      
    $tables $dom->getElementsByTagName('table');
      
    $rows $tables->item(0)->getElementsByTagName('tr');
        
      foreach (
    $rows as $row) {
        
    $cols $row->getElementsByTagName('td');
        echo 
    $cols->item(0)->nodeValue $cols->item(1)->nodeValue $cols->item(2)->nodeValue "\n";
        
    //here you can place the code to insert into database
      
    }

    ?>
     
  7. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    Thanks for that :) the code works fine.

    Is there a way that i can use the code but instead of having the table in another html file, have the table in the same php file (the html table is made by using php tags)
     
  8. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    sorry confused now, if the table is being create by php code, surely when you echo out the table contents, you can insert into the database at the same time.

    i'm not sure what you mean but you can also do this

    PHP:
    <?php
      $html 
    "<table>
    <tr><td>01</td><td>Mike</td><td>fred lane</td></tr>
    <tr><td>02</td><td>james</td><td>school lane</td></tr>
    </table>"
    ;

      
    $dom = new DOMDocument();
      
    $dom->loadHTML($html);
      
    $tables $dom->getElementsByTagName('table');
      
    $rows $tables->item(0)->getElementsByTagName('tr');
        
      foreach (
    $rows as $row) {
        
    $cols $row->getElementsByTagName('td');
        echo 
    $cols->item(0)->nodeValue $cols->item(1)->nodeValue $cols->item(2)->nodeValue "\n";
        
    //here you can place the code to insert into database
      
    }

    ?>
     
  9. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    ok thanks i try that out in min...

    What i have is a application that uploads a ".txt" file to my server. The application will then read the information and put the data in a HTML table. The .txt file contains data e.g.

    01, Richard, fred, 22,
    02, Matt, mike, 55
    ........

    The cells are separted by the comma. The code below show how this is done:
    PHP:
    $a=1;
    $b=1;
    $rows=1;
    $column=1;
    $size1=1;
    $col 1;

    while (!
    feof($fp)){
    $char fgetc($fp);
    $array_of_line_values =explode(", "$char);
    foreach(
    $array_of_line_values as $key => $value){

            if (
    $a==1){                                
                if (
    $b==1){
                    echo 
    "<tr>";
                }
                
                echo 
    "<td><colid" .$col">";
                
    $a=0;
                
    $b=0;            
            }
        if(
    $value == $DimOther) {
        
    $column $column 1;
        ;
        }
        else{
        echo 
    $value;
        }

        if(
    $value == $DimOther) {
            
    $a=1;
            
    $b=0;        
            echo 
    "</colid" .$col"></td>";
            
    $col $col 1;
                
        }
        if(
    $value == "\n"){
            
    $rows $rows 1;
            
    $b=1;
            
    $a=1;
            
    $col 1;
            
            echo 
    "</tr>";
            }
        }

    }

    $column $column/$rows;
    $column1 round($column);
    if (
    $column1 and $rows >= 1){
        
    $column1 1;
    }
    if (
    $rows >= 100){ $size1 5;}

    echo 
    "</table>";

    The $Dimother = ",".
    Is there a way that i can insert the data from here instead of creating the table?
     
  10. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    looks like a CSV file, you may want to look into this PHP: fgetcsv - Manual

    my simple example
    PHP:
    <?php
      $data 
    file_get_contents('data.txt');
      
    $rows explode("\n"$data);    
      foreach(
    $rows as $row){
        
    $line explode(','$row);            
        echo 
    $line[0] . ' ' $line[1] . ' ' $line[2];
        
    //put code here to insert into database
      
    }    
    ?>
     
  11. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    Hey i got the code to work, however when i insert the data into the Access database only the 01 and 02 value get inserted. any ideas?

    Also when i do get all the data in, the data in the Accesss table looks like this:

    01
    02
    James
    Mike
    fred lane
    school lane

    Is there someting wrong with my insert statement?

    Code:
    <?php
    $db_conn = new COM("ADODB.Connection"); 
    $connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./Database1.mdb").";"; 
    $db_conn->open($connstr); 
    
    $html = "<table>
    <tr><td>01</td><td>Mike</td><td>fred lane</td></tr>
    <tr><td>02</td><td>james</td><td>school lane</td></tr>
    </table>";
    
      $dom = new DOMDocument();
      $dom->loadHTML($html);
      $tables = $dom->getElementsByTagName('table');
      $rows = $tables->item(0)->getElementsByTagName('tr');
      $n = 1;  
      $h = 0;
      foreach ($rows as $row) {
        $cols = $row->getElementsByTagName('td');
        echo $cols->item(0)->nodeValue . $cols->item(1)->nodeValue . $cols->item(2)->nodeValue . "\n";
    $rs3 = $db_conn->execute("INSERT INTO TempData(Col".$n.") VALUES('".$cols->item($h)->nodeValue."')");
    	  
      		
      }
    $n = $n + 1;
    $h = $h + 1;
    
    $db_conn->Close(); 
    
    ?>
     
  12. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    I'm not sure what you have done there but the following variables contains the data you need, why are you using MS database?

    PHP:
    $cols->item(0)->nodeValue $cols->item(1)->nodeValue $cols->item(2)->nodeValue
    so it would be something like

    PHP:
    values($cols->item(0)->nodeValue$cols->item(1)->nodeValue$cols->item(2)->nodeValue)
     
  13. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    thanks for ur help, looking back on it makes sence.

    A quick question for you, could i do the same but instead of putting the values into a MS access DB, could i put the values into a Multi-dimentional Array?
     
  14. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
  15. hewstone999

    hewstone999 Geek Trainee

    Likes Received:
    0
    Trophy Points:
    0
    SQL Query

    Hey guys,

    I have the code below which inputs data in a database, however instead of inputted the values i want, it inputs the string: Is there someting wrong with $RowName?

    PHP:
    $ColName "Col1,Col2,Col3";
    $RowName "'$line[0]','$line[1]','$line[2]'";

    $data file_get_contents($table);
    $rows explode("\n"$data);    
    foreach(
    $rows as $row){
       
    $line explode(','$row);
        
    $SQLCODE "INSERT INTO TempData($ColName) VALUES($RowName)";
        
    $rS $db_conn->execute($SQLCODE); 
           
      }
     
  16. edijs

    edijs Programmer

    Likes Received:
    9
    Trophy Points:
    38
    SQL Query

    why do you do $line = explode(',', $row); , if you're not using the variable $line later?
     
  17. Sniper

    Sniper Administrator Staff Member

    Likes Received:
    59
    Trophy Points:
    63
    SQL Query

    use this

    PHP:
    $SQLCODE "INSERT INTO TempData(Col1,Col2,Col3) VALUES($line[0]$line[1]$line[2])

    edit: that was wrong, you managed to mess up my example :) I've also merged the two threads.
     

Share This Page