Flat File DB 3

[dagger2002]

Ok everyone I have a problem and have no idea how to solve it. I normally just use mySQL to do my db's but for this project we are useing mySQL for intitial storage of data then we are doing a little data manipulateing and storeing part of the data into a flat file DB. I have the code for what I am doing to get the data to the flat file but am not sure how to be able to use the data after I get it into the file.

So here is the code.

<?php

	$id 	= $row2[stat_id];
	$date 	= $row2[stat_date];
	$time 	= $row2[stat_time];
	$ip 	= $row2[stat_ip];
	$page 	= $row2[stat_page];
	$title 	= $row2[stat_title];

	$file = 'stats.db';

?>

<?php
//	Opens File for write which erases file if it already exists
	$fp = fopen($file, "w");
//	$fp = fopen("data.txt","a");

//	Writes to file	
	fwrite($fp, $id . " &|& " . $date . " &|& " . $time . " &|& " . $ip . " &|& " . $page . " &|& " . $title . "\n");

//	Closes file
	fclose($fp);

?>

It gets the information from the db array and stores it here.

This is the db set of instructions:

	$query = "SELECT * FROM stats_it WHERE  `stat_ip` NOT LIKE '1ST IP GOES HERE' ORDER BY stat_id ASC";
	
	$result = mysql_query($query) or die/*(mysql_error());*/("Error with the submission.");

	$numRows = mysql_num_rows($result);
	$row = mysql_fetch_array($result);

$dropTmp = "DROP TABLE temp_Stats";
$dropRes = mysql_query($dropTmp) or die(mysql_error());//("Error with the submission.");

$queryTmp = "CREATE TABLE temp_Stats ( `stat_id` int(4) NOT NULL , `stat_date` varchar(10) NOT NULL default '', `stat_time` varchar(8) NOT NULL default '', `stat_ip` varchar(15) NOT NULL default '', `stat_page` varchar(255) NOT NULL , PRIMARY KEY (`stat_id`))";
$resultTmp =  mysql_query($queryTmp) or die(mysql_error());//("Error with the submission.");

//	Inserts data into Temp Table
	$queryInsTmp = "REPLACE INTO temp_Stats SET stat_id=\"$row[stat_id]\", stat_date=\"$row[stat_date]\", stat_time=\"$row[stat_time]\", stat_ip=\"$row[stat_ip]\", stat_page=\"$row[stat_page]\"";
	$resultInsTmp =  mysql_query($queryInsTmp) or die(mysql_error());//("Error with the submission.");

while($row = mysql_fetch_array($result)){
	$queryInsTmp = "REPLACE INTO temp_Stats SET stat_id=\"$row[stat_id]\", stat_date=\"$row[stat_date]\", stat_time=\"$row[stat_time]\", stat_ip=\"$row[stat_ip]\", stat_page=\"$row[stat_page]\"";

$resultInsTmp =  mysql_query($queryInsTmp) or die(mysql_error());//("Error with the submission.");

}

//SELECT * FROM TempTable";

//echo '<h1>' . $numRows . '</h1>';

	$query2 = "SELECT * FROM temp_stats WHERE  `stat_ip` NOT LIKE '2ND IP GOES HERE' ORDER BY stat_id ASC";
	
	$result2 = mysql_query($query2) or die/*(mysql_error());*/("Error with the submission.");

	$numRows2 = mysql_num_rows($result2);
	$row2 = mysql_fetch_array($result2);

//	Opens File for write which erases file if it already exists
	$file = 'stats.db';
	
	$fp = fopen($file, "w");

echo "<h3>" . $numRows . "<h3>";
echo "<h4>" . $numRows2 . "<h4>";


	$id 	= $row2[stat_id];
	$date 	= $row2[stat_date];
	$time 	= $row2[stat_time];
	$ip 	= $row2[stat_ip];
	$page 	= $row2[stat_page];
	$title 	= $row2[stat_title];

//	Writes to file	
	fwrite($fp, $id . " &|& " . $date . " &|& " . $time . " &|& " . $ip . " &|& " . $page . " &|& " . $i . "\n");

$i++;


while($row2 = mysql_fetch_array($result2)){
//	echo $row2[stat_ip] . '<br />';

	$id 	= $row2[stat_id];
	$date 	= $row2[stat_date];
	$time 	= $row2[stat_time];
	$ip 	= $row2[stat_ip];
	$page 	= $row2[stat_page];
	$title 	= $row2[stat_title];

	$file = 'stats.db';

//	Writes to file	
	fwrite($fp, $id . " &|& " . $date . " &|& " . $time . " &|& " . $ip . " &|& " . $page . " &|& " . $title . "\n");
echo $i . '<br />';
$i++;

}
//	Closes file
	fclose($fp);

echo $i;
	mysql_close($link);


?>

Thanks All in advance

 

[dagger2002]

If possiable I would like to read this data into an array.

 

[vacunita]

Use the [blue]file()[/blue] function to open the file into an array.

It seems your column separator would be "[red]&|&[/red]"
explode each line of the file into the fields using the [blue]explode()[/blue] function.

file()

http://www.php.net/manual/en/function.file.php

explode()

http://www.php.net/manual/en/function.explode.php

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

if you're going to store data in a flatfile why don't you use a format like csv for which php has built in handler functions? or better still, use xml.

 

[dkdude]

This is a quick way to dump the contents of your flatfile database into a html table:

<?php

class flat_db {

  var $_separator;
  var $_db = false;

  function set_separator($str) {
    $this->_separator = $str;
  }

  function open_db($filename) {
    $this->_db = file($filename);
  }

  function close_db() {
    unset($this->_db);
  }

  function data_table() {
    echo "<table border=1>\n";
    foreach($this->_db as $record) {
      $record = rtrim($record);
      echo "<tr>\n";
      $fieldset = explode($this->_separator, $record);
      foreach($fieldset as $field)
        echo "<td>$field</td>\n";
      echo "</tr>\n";
    }
    echo "</table>\n";
  }
}

$myDB = new flat_db;
$myDB->set_separator(" &|& ");
$myDB->open_db("stats.db");
$myDB->data_table();
$myDB->close_db();

?>

And, like jpadie says, use an open format like csv (Comma Separated Volume). Just make the separator a ; and then you will be able to open the file with many programs - f.ex. Excel.

 

[dagger2002]

Ok Guys ty very much. But I am having a problem.

The problem is the db. When it is getting it from the mysql it is printing every page out into a table. But the problem lies in that if a page has 29 hits it is printed out 29 times.

I was talking to someone and they were telling me about a tally function, and a way to pull from the database only unique items.

Any thoughts on how I can do this.

 

[dagger2002]

Here is the code

<?php

//	Creates table
	$table = "
		<table border='1' width='100%' cellspacing='5' cellpadding='5'>\n
			<tr>\n
				<th align='left' valign='top'>Page</th>\n
				<th align='right' valign='top'>Hit Count</th>\n
			</tr>\n
	";
	
	$close = "</table>";

?>
<?php

// Database Details
	global $dbhost, $dbuser, $dbpass, $dbname;

// Attempt to connect to database server

	$link = @mysql_connect($dbhost, $dbuser, $dbpass);

// Attempt to connect to select our database

	@mysql_select_db($dbname, $link);
			

?>

<?php

//	Queries DB for records that don't have 10.1.
	$query = "SELECT * FROM stats_boe WHERE  `stat_ip` NOT LIKE '10.1.%' ORDER BY stat_id ASC";
	$result = mysql_query($query) or die/*(mysql_error());//*/("Error with the submission 10.");
	$numRows = mysql_num_rows($result);
	$row = mysql_fetch_array($result);

?>

<?php

//	works the Temparary table
	$dropTmp = "DROP TABLE temp_Stats";
	$dropRes = mysql_query($dropTmp) or die/*(mysql_error());//*/("Error with Temparary submission.");

//	$dropTmp = "DROP TABLE temp_Stats2";
//	$dropRes = mysql_query($dropTmp) or die/*(mysql_error());//*/("Error with Temparary submission.");
	
	$queryTmp = "CREATE TABLE `webStats`.`temp_Stats` (`stat_id` int( 4 ) NOT NULL AUTO_INCREMENT , `stat_date` varchar( 10 ) NOT NULL default '', `stat_time` varchar( 8 ) NOT NULL default '', `stat_ip` varchar( 15 ) NOT NULL default '', `stat_page` text NOT NULL , `stat_title` varchar( 255 ) NOT NULL default '', PRIMARY KEY ( `stat_id` )) TYPE = MYISAM";
	$resultTmp =  mysql_query($queryTmp) or die/*(mysql_error());//*/("Error with the submission.");
	
	while($row = mysql_fetch_array($result)){
		$queryInsTmp = "REPLACE INTO temp_Stats SET stat_id=\"$row[stat_id]\", stat_date=\"$row[stat_date]\", stat_time=\"$row[stat_time]\", stat_ip=\"$row[stat_ip]\", stat_page=\"$row[stat_page]\", stat_title=\"$row[stat_title]\"";
		$resultInsTmp =  mysql_query($queryInsTmp) or die/*(mysql_error());//*/("Error with the submission.");

	}

//	Queries DB for records that don't have 163.150.
	$query2 = "SELECT * FROM temp_stats WHERE  `stat_ip` NOT LIKE '163.150.%' ORDER BY stat_id ASC";
	$result2 = mysql_query($query2) or die/*(mysql_error());*/("Error with the submission 163.");

	$numRows2 = mysql_num_rows($result2);
	$row2 = mysql_fetch_array($result2);

//Creates Temp2 and Inserts Data
	$queryTmp2 = "CREATE TABLE `webStats`.`temp_Stats2` (`stat_id` int( 4 ) NOT NULL AUTO_INCREMENT , `stat_date` varchar( 10 ) NOT NULL default '', `stat_time` varchar( 8 ) NOT NULL default '', `stat_ip` varchar( 15 ) NOT NULL default '', `stat_page` text NOT NULL , `stat_title` varchar( 255 ) NOT NULL default '', PRIMARY KEY ( `stat_id` )) TYPE = MYISAM";
	$resultTmp2 =  mysql_query($queryTmp2) or die/*(mysql_error());//*/("Error Creating  submission2.");
	
	
	while($row2 = mysql_fetch_array($result2)){
		$queryInsTmp2 = "REPLACE INTO temp_Stats2 SET stat_id=\"$row2[stat_id]\", stat_date=\"$row2[stat_date]\", stat_time=\"$row2[stat_time]\", stat_ip=\"$row2[stat_ip]\", stat_page=\"$row2[stat_page]\", stat_title=\"$row2[stat_title]\"";
		$resultInsTmp2 =  mysql_query($queryInsTmp2) or die/*(mysql_error());//*/("Error with the submission.");

	}

//	works the Temparary table
	$dropTmp2 = "DROP TABLE temp_Stats";
	$dropRes2 = mysql_query($dropTmp2) or die(mysql_error());//*/("Error Dropping  Temparary submission2.");

	$renTemp2 = "ALTER TABLE `temp_Stats2` RENAME `temp_Stats`";
	$renRes = mysql_query($renTemp2) or die/*(mysql_error());//*/("Error Ren Temparary submission2.");


//	Inserts Final Data

?>

<?php
//	Starts File Writing Proccess
?>

<?php

//	Selects Index Page Hits
	$queryIndex = "SELECT * FROM `temp_stats` WHERE `stat_page` LIKE '/BoE/index_BoE.php?' ORDER BY stat_title DESC";
	$resIndex = mysql_query($queryIndex) or die/*(mysql_error());//*/("Error with the Index.");
	
	$rowIndex = mysql_fetch_array($resIndex);
	$numIndex = mysql_num_rows($resIndex);

//	Sets $ index for Print
	$index = "<tr><td align='left' nowrap>" . $rowIndex[stat_title] . "</td><td align='right'>" . $numIndex . "</td></tr>\n"; 
	
?>

<?php

//	Selects a Page
	$queryBody = "SELECT * FROM `temp_stats`_uniq WHERE `stat_page` NOT LIKE '/BoE/index_BoE.php?' ORDER BY stat_page ASC";
	$resBody = mysql_query($queryBody) or die/*(mysql_error());//*/("Error with the Body.");
	
	$rowBody = mysql_fetch_array($resBody);
	$numBody = mysql_num_rows($resBody);

//	Count Individual Pages
	$queryBody2 = "SELECT * FROM `temp_stats`_uniq WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);
	
	$body = "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n"; 

while($rowBody = mysql_fetch_array($resBody)){
//	Count Individual Pages
	$queryBody2 = "SELECT * FROM `temp_stats` WHERE `stat_page` LIKE '$rowBody[stat_page]' ORDER BY stat_title DESC";
	$resBody2 = mysql_query($queryBody2) or die(mysql_error());//*/("Error with the Body2.");
	
	$rowBody2 = mysql_fetch_array($resBody2);
	$numBody2 = mysql_num_rows($resBody2);
	
	$body .= "<tr><td align='left' nowrap>" . $rowBody2[stat_page] . "</td><td align='right'>" . $numBody2 . "</td></tr>\n"; 
}

?>

<?php
//	echo $queryBody2;
//	echo $numIndex;

// Writes to Screen
	echo $table;
	echo $index;
	echo $body;
	echo $close;

?>

I am printing to the screen until I can get it working right. But after that then it will do both printing to the screen and to the file.

 

[vacunita]

It's up to your SQL query, to pick out the UNIQUES, for your select statement.

Try the MYSQL forum for assistance on modifying your query: forum436

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.