HTML table data into a Access Databse table

H

hewstone999

Geek Trainee
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);
 
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?
 
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.
 
what language are you using? I can do PHP but will try my best to help if its ASP.
 
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
  }

?>
 
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)
 
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
  }

?>
 
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 < 1 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?
 
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
  }	
?>
 
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(); 

?>
 
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)
 
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?
 
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); 
	   
  }
 
SQL Query

why do you do $line = explode(',', $row); , if you're not using the variable $line later?
 
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.
 
You must log in or register to reply here.
Back
Top