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?
[COLOR=“Blue”]Table1:
01, Rich, Mike, 23, fred lane,
02, James, milner, 45, School Lane,
03, Rob, Matthews, 89, College Road,
[COLOR=“Red”]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]
| 01 | Mike | fred lane |
| 02 | james | school lane |
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:
"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.
PHP is fine thank you 
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
}
?>[/php]
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 = "| 01 | Mike | fred lane |
| 02 | james | school lane |
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 “”;[/php]
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]
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 = "
| 01 | Mike | fred lane |
| 02 | james | school lane |
$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();
?>[/code]
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[/php]
so it would be something like
[php]values($cols->item(0)->nodeValue, $cols->item(1)->nodeValue, $cols->item(2)->nodeValue)[/php]
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?
sure you can, rather me doing it for you, see if you can do it your self PHP: Arrays - Manual
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);
}
[/php]
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])[/php]
edit: that was wrong, you managed to mess up my example I’ve also merged the two threads.