Jump to content

Archived

This topic is now archived and is closed to further replies.

obsidian

Sorting by column from a flat file

Recommended Posts

wow... seems to be a while since i've had something worthy to post a new thread in this board about, but i need to get some input from our resident gurus ;) . i have recently undertaken a project, and i have a solution in mind, but i'm sure there is a more efficient way to handle things, but rather than fogging the creativity by posting my solution, i want to get some input from you all first. here's the situation: i am working with a company who had their database server crash last weekend due to overload. first problem: code was outdated and severely under-optimized. i've taken care of many of those pages, but there is one other thing we're going to try to do. the question was posed to me: [i]"We have one table that is queried several thousand times a day, but the data doesn't change but once a week. Can we not have a flat file that is created each Monday when the data is updated and have the users query that flat file to shift some of the server load over to our web server as opposed to our database server?[/i]

my answer, of course, as any good freelancer's would be was: [i]sure, we can do whatever you want[/i]. i still stand behind that decision, but i wanted to see if some of you have more experience and have a better idea of how to handle this sort of problem. this gets us to my question: once i have my flat file created, what is the best way to sort my records by a column? i've got two methods in mind, but they both seem to run fairly slowly. does anyone have any suggestions right offhand for how to optimally do a sort like this?

thanks in advance for any input.

Share this post


Link to post
Share on other sites
jesus, a chance to help the master
First, I agree with agreeing, You want this, Sure, its only a months extra work, but sure

Anyway
I would run a script monday to put the results in a secure XML file
Then simply read and run the results from the XML file

Share this post


Link to post
Share on other sites
[quote author=onlyican link=topic=109328.msg440582#msg440582 date=1159144846]
I would run a script monday to put the results in a secure XML file
Then simply read and run the results from the XML file
[/quote]

i've already got a CRON job set up to run every monday night to genereate a CSV file that contains all the data, so that part i don't need help with. where i'm looking for help is when i reload that CSV for the user query, and i have it all in an array (multi-dimensional array :P ), i need to know if there is a [i]good[/i] way to sort by a specified column.

thanks

Share this post


Link to post
Share on other sites
Will the way the data is sorted change? if not.. sort it once when you run the query. Otherwise... you might want to look into storing it in [url=http://php.net/sqlite]sqlite[/url].

Share this post


Link to post
Share on other sites
[quote author=thorpe link=topic=109328.msg440627#msg440627 date=1159150828]
Will the way the data is sorted change? if not.. sort it once when you run the query. Otherwise... you might want to look into storing it in [url=http://php.net/sqlite]sqlite[/url].
[/quote]

well, that's what i'm getting at. i need to give the user the option of sorting by column on the page. thus, i'm actually considering simply storing one flat file sorted by each column and pulling the file necessary based on the user's query. that way, i'd only have to filter out unwanted data rather than having to let PHP run the sort for me.

thoughts?

Share this post


Link to post
Share on other sites
[quote]thoughts?[/quote]

Have you access to sqlite?

Share this post


Link to post
Share on other sites
If the text file is really not super big you can use array_multisort(), I have few flat files (72,000) records 18.4MB,that I search and sort and it takes less than a second to return the array!

very small example data 'one.txt'

[code]829', 'Brockton', 'MA', 'Brockton', '02302', '120 Commercial Street', '120 Commercial ST', '8002758777', '5085800411', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:63000;}i:1;a:2:{i:0;i:28800;i:1;i:63000;}i:2;a:2:{i:0;i:28800;i:1;i:63000;}i:3;a:2:{i:0;i:28800;i:1;i:63000;}i:4;a:2:{i:0;i:28800;i:1;i:63000;}i:5;a:2:{i:0;i:27000;i:1;i:48600;}}', '9998', '+42.081894', '-71.015918
830', 'Holbrook', 'MA', 'Holbrook', '02343', '333 Plymouth Street', '333 Plymouth ST', '8002758777', '7817674114', 'a:6:{i:0;a:2:{i:0;i:29700;i:1;i:61200;}i:1;a:2:{i:0;i:29700;i:1;i:61200;}i:2;a:2:{i:0;i:29700;i:1;i:61200;}i:3;a:2:{i:0;i:29700;i:1;i:61200;}i:4;a:2:{i:0;i:29700;i:1;i:61200;}i:5;a:2:{i:0;i:29700;i:1;i:45000;}}', '9998', '+42.150594', '-70.997963
831', 'South Boston', 'MA', 'Boston', '02127', '444 East 3rd Street', '444 E 3rd ST', '8002758777', '6172681098', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:27000;i:1;i:46800;}}', '9998', '+42.336397', '-71.042261
832', 'Lafayette Station', 'MA', 'Boston', '02111', '7 Avenue DE Lafayette', '7 Avenue DE Lafayette', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:64800;}i:1;a:2:{i:0;i:27000;i:1;i:64800;}i:2;a:2:{i:0;i:27000;i:1;i:64800;}i:3;a:2:{i:0;i:27000;i:1;i:64800;}i:4;a:2:{i:0;i:27000;i:1;i:64800;}i:5;a:2:{i:0;i:27000;i:1;i:46800;}}', '9998', '+42.353297', '-71.061595
833', 'Hanover Street', 'MA', 'Boston', '02113', '217 Hanover Street', '217 Hanover ST', '8002758777', '6177204521', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:64800;}i:1;a:2:{i:0;i:28800;i:1;i:64800;}i:2;a:2:{i:0;i:28800;i:1;i:64800;}i:3;a:2:{i:0;i:28800;i:1;i:64800;}i:4;a:2:{i:0;i:28800;i:1;i:64800;}i:5;a:2:{i:0;i:28800;i:1;i:50400;}}', '9998', '+42.362944', '-71.055780
834', 'State House', 'MA', 'Boston', '02133', '24 Beacon Street,Room 2', '24 Beacon ST RM 2', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;i:0;}', '9998', '+42.353719', '-71.138675
3511', 'Logan Airport Retail Unit', 'MA', 'East Boston', '02228', '235 Prescott Street', '235 Prescott ST', '8002758777', '6175697803', 'a:6:{i:0;a:2:{i:0;i:30600;i:1;i:64800;}i:1;a:2:{i:0;i:30600;i:1;i:64800;}i:2;a:2:{i:0;i:30600;i:1;i:64800;}i:3;a:2:{i:0;i:30600;i:1;i:64800;}i:4;a:2:{i:0;i:30600;i:1;i:64800;}i:5;a:2:{i:0;i:30600;i:1;i:52200;}}', '9742', '+42.375033', '-71.024360
836', 'East Boston', 'MA', 'Boston', '02128', '50 Meridian Street', '50 Meridian ST', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:61200;}i:1;a:2:{i:0;i:28800;i:1;i:61200;}i:2;a:2:{i:0;i:28800;i:1;i:61200;}i:3;a:2:{i:0;i:28800;i:1;i:61200;}i:4;a:2:{i:0;i:28800;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:46800;}}', '9998', '+42.371297', '-71.039195
840', 'Cathedral', 'MA', 'Boston', '02118', '59 West Dedham Street', '59 W Dedham ST', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:43200;}}', '9998', '+42.341943', '-71.073122
838', 'Back Bay Annex', 'MA', 'Boston', '02116', '390 Stuart Street', '390 Stuart ST', '8002758777', '6172675581', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:68400;}i:1;a:2:{i:0;i:27000;i:1;i:68400;}i:2;a:2:{i:0;i:27000;i:1;i:68400;}i:3;a:2:{i:0;i:27000;i:1;i:68400;}i:4;a:2:{i:0;i:27000;i:1;i:68400;}i:5;a:2:{i:0;i:27000;i:1;i:57600;}}', '5011', '+42.348978', '-71.074289
839', 'Kendall Square', 'MA', 'Cambridge', '02142', '250 Main Street', '250 Main ST', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;i:0;}', '9998', '+42.362297', '-71.085069
3514', 'Charlestown', 'MA', 'Charlestown', '02129', '23 Austin Street', '23 Austin ST', '8002758777', '6172413885', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:61200;}i:1;a:2:{i:0;i:28800;i:1;i:61200;}i:2;a:2:{i:0;i:28800;i:1;i:61200;}i:3;a:2:{i:0;i:28800;i:1;i:61200;}i:4;a:2:{i:0;i:28800;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:46800;}}', '9998', '+42.375295', '-71.065015
841', 'Astor', 'MA', 'Boston', '02115', '207 Massachusetts Avenue', '207 Massachusetts AVE', '8002758777', '6173750203', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:68400;}i:1;a:2:{i:0;i:28800;i:1;i:68400;}i:2;a:2:{i:0;i:28800;i:1;i:68400;}i:3;a:2:{i:0;i:28800;i:1;i:68400;}i:4;a:2:{i:0;i:28800;i:1;i:68400;}i:5;a:2:{i:0;i:28800;i:1;i:50400;}}', '3043', '+42.344888', '-71.086777
843', 'East Cambridge', 'MA', 'Cambridge', '02141', '303 Cambridge Street', '303 Cambridge ST', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:27000;i:1;i:50400;}}', '9998', '+42.371172', '-71.081304
3516', 'Roxbury', 'MA', 'Roxbury', '02119', '55 Roxbury Street', '55 Roxbury ST', '8002758777', '6174271683', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:46800;}}', '9998', '+42.329597', '-71.085995
3517', 'Grove Hall', 'MA', 'Dorchester', '02121', '647 Warren Street', '647 Warren ST', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:43200;}}', '9998', '+42.310461', '-71.083273
3518', 'Uphams Corner', 'MA', 'Dorchester', '02125', '551 Columbia Road', '551 Columbia RD', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:43200;}}', '9998', '+42.316098', '-71.066195
3519', 'Jamaica Plain', 'MA', 'Jamaica Plain', '02130', '655 Centre Street', '655 Centre ST', '8002758777', '6175224913', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:63000;}i:1;a:2:{i:0;i:27000;i:1;i:63000;}i:2;a:2:{i:0;i:27000;i:1;i:63000;}i:3;a:2:{i:0;i:27000;i:1;i:63000;}i:4;a:2:{i:0;i:27000;i:1;i:63000;}i:5;a:2:{i:0;i:28800;i:1;i:46800;}}', '9998', '+42.312994', '-71.114241
846', 'Brookline Village', 'MA', 'Brookline', '02445', '207 Washington Street', '207 Washington ST', '8002758777', '6177319668', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:54000;}}', '9998', '+42.332317', '-71.118586
847', 'Brookline', 'MA', 'Brookline', '02446', '1295 Beacon Street', '1295 Beacon ST', '8002758777', '6177388494', 'a:6:{i:0;a:2:{i:0;i:25200;i:1;i:68400;}i:1;a:2:{i:0;i:25200;i:1;i:68400;}i:2;a:2:{i:0;i:25200;i:1;i:68400;}i:3;a:2:{i:0;i:25200;i:1;i:68400;}i:4;a:2:{i:0;i:25200;i:1;i:68400;}i:5;a:2:{i:0;i:25200;i:1;i:57600;}}', '9998', '+42.342357', '-71.120703
849', 'North Quincy', 'MA', 'Quincy', '02171', '454 Hancock Street', '454 Hancock ST', '8002758777', '6174727875', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:61200;}i:1;a:2:{i:0;i:28800;i:1;i:61200;}i:2;a:2:{i:0;i:28800;i:1;i:61200;}i:3;a:2:{i:0;i:28800;i:1;i:61200;}i:4;a:2:{i:0;i:28800;i:1;i:61200;}i:5;a:2:{i:0;i:32400;i:1;i:43200;}}', '9998', '+42.272398', '-71.023893
3523', 'Newton Upper Falls', 'MA', 'Newton Upper Falls', '02464', '81 Oak Street', '81 Oak ST', '8002758777', '6179642456', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:61200;}i:1;a:2:{i:0;i:27000;i:1;i:61200;}i:2;a:2:{i:0;i:27000;i:1;i:61200;}i:3;a:2:{i:0;i:27000;i:1;i:61200;}i:4;a:2:{i:0;i:27000;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:43200;}}', '9998', '+42.309747', '-71.220799
3524', 'Allston', 'MA', 'Allston', '02134', '47 Harvard Avenue', '47 Harvard AVE', '8002758777', '6177879382', 'a:6:{i:0;a:2:{i:0;i:28800;i:1;i:61200;}i:1;a:2:{i:0;i:28800;i:1;i:61200;}i:2;a:2:{i:0;i:28800;i:1;i:61200;}i:3;a:2:{i:0;i:28800;i:1;i:61200;}i:4;a:2:{i:0;i:28800;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:50400;}}', '9998', '+42.354375', '-71.132856
851', 'Soldiers Field', 'MA', 'Boston', '02163', '117 Western Avenue,Suite 1', '117 Western AVE STE 1', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:31500;i:1;i:59400;}i:1;a:2:{i:0;i:31500;i:1;i:59400;}i:2;a:2:{i:0;i:31500;i:1;i:59400;}i:3;a:2:{i:0;i:31500;i:1;i:59400;}i:4;a:2:{i:0;i:31500;i:1;i:59400;}i:5;i:0;}', '9998', '+42.363663', '-71.124173
3525', 'Brighton', 'MA', 'Brighton', '02135', '424 Washington Street', '424 Washington ST', '8002758777', '6172549319', 'a:6:{i:0;a:2:{i:0;i:25200;i:1;i:61200;}i:1;a:2:{i:0;i:25200;i:1;i:61200;}i:2;a:2:{i:0;i:25200;i:1;i:61200;}i:3;a:2:{i:0;i:25200;i:1;i:61200;}i:4;a:2:{i:0;i:25200;i:1;i:61200;}i:5;a:2:{i:0;i:28800;i:1;i:46800;}}', '9998', '+42.349041', '-71.156605
856', 'Porter Square Financial Unit', 'MA', 'Cambridge', '02140', '1953 Massachusetts Avenue', '1953 Massachusetts AVE', '8002758777', '8002758777', 'a:6:{i:0;a:2:{i:0;i:27000;i:1;i:63000;}i:1;a:2:{i:0;i:27000;i:1;i:63000;}i:2;a:2:{i:0;i:27000;i:1;i:63000;}i:3;a:2:{i:0;i:27000;i:1;i:63000;}i:4;a:2:{i:0;i:27000;i:1;i:63000;}i:5;a:2:{i:0;i:27000;i:1;i:50400;}}', '9998', '+42.389956', '-71.120617[/code]

A simple example script to do the search and sort!

[code][code=php:0]<?

// variable $find explained
// array ( 'search type', 'search in' => 'search for' );

/*
* search type
*
* em = exact match (case sensitive)
* im = exact match (case insensitive)
* es = in string (case sensitive)
* is = in string (case insensitive)
* eb = in string (word boundry case sensitive)
* ib = in string (word boundry case insensitive)
*/

/*
* search in = column to search -> (string) column name
*/

/*
* search for = data to search for -> anything
*/

// each thing you want to search for, is an array within the $find array()
// we do this so we can search more than (1) column at a time.
// you could also add (OR) if you needed that logic

$find = array ( array ( 'im', 'state' => 'ma' ), array ( 'im', 'city' => 'boston' ) );


// variable $sort explained
// array ( 'column type', 'sort type', 'sort column' );

/*
* column type
*
* i = treat column as a NUMERIC
* s = treat column as a STRING
*
*/

/*
* column sort type
*
* a = sort column in ascending order
* d = sort column in descending order
*
*/

/*
* sort column = column to sort on
*/


$sort = array ( 'i', 'a', 'zip' );


/* column separator */

$separator = "', '";

// run it

$column = array (
'number'  => 0,
'name'    => 1,
'state'  => 2,
'city'    => 3,
'zip'    => 4,
'address' => 5,
'map'    => 6,
'phone'  => 7,
'fax'    => 8,
'hours'  => 9,
'plus4'  => 10,
'lat'    => 11,
'lon'    => 12
);


$fs = sizeof ( $find );

$io = fopen ( 'one.txt', 'rb' );

$do = array ();
$dk = array ();

while ( ! feof ( $io ) )
{
$fi = 0;

$cl = array_map ( 'trim', explode ( $separator, fgets ( $io, 4096 ) ) );

foreach ( $find AS $search )
{
$type = $search[0];
unset ( $search[0] );

foreach ( $search AS $k => $v )
{
switch ( $type )
{
case 'em' :
if ( $cl[$column[$k]] == $v )
{
$fi++;
}
break;
case 'im' :
if ( strtolower ( $cl[$column[$k]] ) == $v )
{
$fi++;
}
break;
case 'es' :
if ( strpos ($cl[$column[$k]], $v ) !== false )
{
$fi++;
}
break;
case 'is' :
if ( stripos ($cl[$column[$k]], $v ) !== false )
{
$fi++;
}
break;
case 'eb' :
if ( preg_match ( "/\b(" . $cl[$column[$k]] . ")\b/", $v ) !== false )
{
$fi++;
}
break;
case 'ib' :
if ( preg_match ( "/\b(" . $cl[$column[$k]] . ")\b/i", $v ) !== false )
{
$fi++;
}
break;
}
}
}

if ( $fi == $fs )
{
$do[] = $cl;
$dk[] = $cl[$column[$sort[2]]];
}
}

fclose ( $io );

if ( ! empty ( $do ) )
{
array_multisort ( $dk, ( $sort[1] == 'a' ? SORT_ASC : SORT_DESC ), ( $sort[0] == 'i' ? SORT_NUMERIC : SORT_STRING ), $do );

print_r ( $do );
}
else
{
echo 'no search results found!';
}

?>[/code][/code]

me!

Share this post


Link to post
Share on other sites
[quote author=thorpe link=topic=109328.msg440643#msg440643 date=1159152845]
Have you access to sqlite?
[/quote]

thorpe, i checked on that, and i was told "no", but i think that just means that i won't be given access to set it up. my guess is that the server will run it, but it's just more than they're wanting to do right now. i think i'll be stuck to the flat files on this one.

Share this post


Link to post
Share on other sites
[quote author=printf link=topic=109328.msg440846#msg440846 date=1159188827]
If the text file is really not super big you can use array_multisort()
[/quote]

that looks as though it may work nicely. i can't say i'm thrilled with the way it's set up, but it may be just what i'm after. when all is said and done, after i've placed my filter on, there really isn't that much data to sort, so thanks for the bump. i've seen the function, but i've never used it myself.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.