Table

[prolong199]

i am currently designing a webpage using php and MySQL and i was wondering how do i populate a table in MySQL with data from a table on a webpage eg. from the source code i wish to grab the raw data of one webpage and place it into a table for use on another.

PLEASE ANY HELP WOULD BE MUCH APPRECIATED

[CYBER_Aeon]

Well, the concept is fairly simple, but there isn't one fix-all solution - You have to write a routine to parse the HTML from the website in question.

A basic example though would go something like this -

Step 1) Fetch the HTML from the site in question.
I usually use the file command to do it, and use implode to turn it back into a string from an array:

$Source = implode( '', file( "http://www.google.com" ) );

You could technically use fopen too, but theres more lines to handle that.

Step 2) Parse the Source.
This is the tricky part. I can't even begin to describe how to go about doing it, because the websites are so different.

Step 3) Store the Data.
Now you just have to actually STORE the data. You'll want a simple insert SQL statement inorder to update the Database, something like this:

INSERT INTO MyTable( 'Field1Name', 'Field2Name', 'Field3Name' ) VALUES ( 'DataValue1', 'DataValue2', 'DataValue3' )

That should give you a fairly basic idea of how it would work. I can help you more with the parsing part if you could post the link your parsing and describe what parts of it you need.

[gesf]

Yes prolong199... CYBER_Aeon is just right!
Here is a little example on how you can do it:

<?php

$page = 'http://url/to/file.html'; 

$fp = @fopen($page , 'r'); 
 while(!feof($fp)){ 
   $row .= @fgets($fp, 4096); 
 }
@fclose($fp); 
 
if (eregi('<title>(.*)</title>', $row, $out)) { 
   $title = $out[1]; 
   break; 
}
 
?>

As you can see... $title has the file.html title!

[CYBER_Aeon]

Take a peek at this. More notes follow at the bottom.

<?php
 //
 // Step 1) Retrieve the HTML from the target website.
 //
 $Source = implode( '', file( 'http://www.aria.com.au/pages/aria-charts-display.asp?chart=1S50' ) );

 //
 // Step 2) Parse the HTML in fields
 //
 // In this case, since we have tables, we'll do it that way.
 //
 
 //
 // Isolate the table from the rest of the source. Kinda tricky.
 // Starting: <table class="chartTable">
 // Closing: </table>
 
 $Source = strstr( $Source, '<table class="chartTable">' );
 $TableLength = (strlen($Source) - strlen(strstr( $Source, '</table>' ))) - strlen('<table class="chartTable">');
 
 $Source = substr( $Source, strlen('<table class="chartTable">'), $TableLength );
 
 //
 // Now that we have just the table, break it up and scan through the contents.
 // Keep in mind that the first row is just table headers.
 //

 // Standardize the Row tags
 $Source = str_replace( '<tr class="tblAltRow">', '<tr>', $Source );
 
 // Create an array from the table, by table row.
 $Rows = explode( '<tr>', $Source );

 // Run through each row (Excluding the first two, 0 which is empty, and 1 which is the header)
 for ( $i = 2; $i < count( $Rows ); $i++ )
 {
  // Create another array based on the table colums.
  $Columns = explode( '<td>', $Rows[$i] );

  // Clean up the excess </td> fields
  $Columns = str_replace( "</td>", "", $Columns );
  
  //
  // Now, we need to decide what to do with this row.
  // First, I would select just the fields I need, and then store it where needed.
  // In this example, we'll select the fields we need and just echo them.
  //
  // The Fields, in this example, are as followed:
  // $Columns[1] = B
  // $Columns[2] = TW
  // $Columns[3] = LW
  // $Columns[4] = TI
  // $Columns[5] = HP
  // $Columns[6] = Title
  // $Columns[7] = Artist
  // $Columns[8] = Accred.
  // $Columns[9] = Company
  // $Columns[10] = Catagory #
  //
  
  $TW = $Columns[2];
  $LW = $Columns[3];
  $HP = $Columns[5];
  $Title = $Columns[6];
  $Artist = $Columns[7];
  
  //
  // For example sake, lets just echo them.
  //
  echo "TW: $TW - LW: $LW - HP: $HP - Title: <FONT COLOR=BLUE>$Title</FONT> - Artist: <FONT COLOR=BLUE>$Artist</FONT><BR>";
 }
?>

Parses the chart from this address and outputs it. The demo is located here.

How about that? That should handle all the parsing you need for that website, just replace the echo near the bottom with whatever code you need. If your going to build a display table to just show it with, make sure you put the echo's for the <TABLE> declare outside of the 'Rows' forloop. If your going to store it into a SQL Database, thats where you want to do it, too.

Hope that helps you out.

[phpForMe]

Just wondering what would happen if the page you are drawing data from had two tables with nothing to distinguish the closing table tag.

Would it use the first or second closing table tag as the one to use in this snippet?

$TableLength = (strlen($Source) - strlen(strstr( $Source, '</table>' ))) - strlen('<table class="chartTable">');

Good question. I usually end up desiging these on a per-page basis, because each website is so incredibly different from the last. Dont quote me on this because i'm not 100% sure on this - But i'm pretty confident that it would still pull the first occurance of the </table> tag after the first occurance of <table class="chartTable">, so it should be fine.

Now, if they had nested another table INSIDE that table, i'd probably be SOL.

[CYBER_Aeon]

Whoops, forgot to log in.