A question about text file + arrays + sorting arrays before I dive in! 1

[southbeach]

OK - Working on a dashboard where text content is read and displayed in full or based on sorting/search criteria. Not a problem! Thus far I have written the code to do all that. I have the code to
(a) loop through a directory and identify .txt files
(b) read each .txt files and append it to an array - Thank goodness for array_merge()!
(c) push loaded content

Now that I am able to do all that, requests start to pour in. I now need to sort ASC or DES on any of the text columns.

Problem:
.txt files are more like flat csv documents or text files where columns are separated by commas so, a typical row may look like this: AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ ...

The array if dumped may look like:
Array(
[0]=>AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ
[1]=>AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ
[2]=>AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ
...
)

Obviously, each row is different from each other as you normally would expect. Given that the entire row goes into an individual array element, it seems that I cannot use PHP built in array sorting methods which brings me to

QUESTIONS:
(a) Am I mistaken that there is no way I could sort data content due to the way I am loading my data onto arrays?
(b) If (a), how then could I sort?
(c) Should I fix my code to load data into a multidimensional arrays? Can these arrays be sorted?

As usual, thank you all in advance for your assistance!



--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[jpadie]

a) Am I mistaken that there is no way I could sort data content due to the way I am loading my data onto arrays?

yes - you are mistaken.

(b) If (a), how then could I sort?

code provided below.

(c) Should I fix my code to load data into a multidimensional arrays? Can these arrays be sorted?

yes. perhaps - and I can provide code to do this if you want.... but it is the least good method as php is slow at sorting arrays. The best way is to use either a temporary mysql table or, better still, an in-memory sqlite table. below is code for the latter which you should find out-performs other solutions.

AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ
AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ
AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM,NNN,OOO,PPP,QQQ

<?php
/*
note - can pass an array of textfiles as well as a single textfile
returns: a numeric array of associative arrays of columns
*/
function sortMultiArrays( $textfile, $column=0, $method="ASC"){
	$errors = array();
	if(!is_array($textfile)):
		$_t = array();
		$_t[] = $textfile;
		$textfile = $_t;
		unset($_t);
	endif;
	foreach ($textfile as $file):
	$fh = fopen($file, 'r');
	while (FALSE !== ($row = fgetcsv($fh))):
		if(!isset($pdo)):
			/* create temp table */
			$SQL = "create table t (%s)";
			for ($i = 0; $i<count($row); $i++): 
				$cols[] = "col{$i} TEXT";
				$placeholders[] = '?';
			endfor;
			$sql = sprintf($SQL, implode(', ', $cols));
			$pdo = new PDO( 'sqlite::memory' );
			$pdo->exec($sql);  			
			
			$s =$pdo->prepare(
								sprintf(	"insert into t values (%s)", 
											implode(',',$placeholders)
										));
			if($s === false):
				die (
						print_r($pdo->errorinfo(), true)
					);
			endif;
  		endif;

  		/* check row length and pad if necessary */
  		if(count($row) == 0) continue; // deal with blank lines
  		while(count($row) < count($cols)):
  			$row[] = '';
  		endwhile;
  		if(count($cols) < count($row)):
  			die('malformed csv');
  		endif;

		$result = $s->execute($row);
		if($result == false):
			$errors[] = $s->errorInfo();
		endif;	  		
	endwhile;
	
	fclose($fh);
	endforeach;
	
	if($debug && count($errors) > 0):
		echo '<pre>'. print_r($errors, true) . '</pre>';
	endif;
	
	$sql = "Select * from t order by col{$column} $method";
	
	$s = $pdo->prepare($sql);
	$s->execute();
	$data = $s->fetchAll( PDO::FETCH_ASSOC );
	return $data;
}

 

[southbeach]

WOW!

New trick to add into the tricks bag!!!!! No clue one could do something like this, the very reason I ask questions even when I appear to have a workable answer.

Correct me if I am wrong but all I need to make sure is that I have PHP Extension php_pod_sqlite loaded. Correct?

Thanks,


Jose

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[jpadie]

in windows I think you need pdo and pdo_sqlite.
but you could use native mysql too. just a bit more annoying to cleanse variables.


in Linux you'd just do sudo apt-get install php5-sqlite.

 

[jpadie]

rthe alternative is to use array_multisort. but to do that you would need to transform from an array if rows to an array of columns. array_multisort is an expensive operation however.

you could also get performance enhancements from the dB method above by forcing an index onto the relevant column in the creation.

 

[jpadie]

and for future readers, the function above should also have a debug argument

function sortMultiArrays( $textfile, $column=0, $method="ASC", $debug = FALSE){

passing true as an argument will immediately output any query errors.