obsidian Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
onlyican Posted September 25, 2006 Share Posted September 25, 2006 jesus, a chance to help the masterFirst, I agree with agreeing, You want this, Sure, its only a months extra work, but sureAnywayI would run a script monday to put the results in a secure XML fileThen simply read and run the results from the XML file Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Author Share Posted September 25, 2006 [quote author=onlyican link=topic=109328.msg440582#msg440582 date=1159144846]I would run a script monday to put the results in a secure XML fileThen 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 Quote Link to comment Share on other sites More sharing options...
trq Posted September 25, 2006 Share Posted September 25, 2006 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 Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Author Share Posted September 25, 2006 [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? Quote Link to comment Share on other sites More sharing options...
trq Posted September 25, 2006 Share Posted September 25, 2006 [quote]thoughts?[/quote]Have you access to sqlite? Quote Link to comment Share on other sites More sharing options...
printf Posted September 25, 2006 Share Posted September 25, 2006 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.015918830', '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.997963831', '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.042261832', '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.061595833', '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.055780834', '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.1386753511', '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.024360836', '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.039195840', '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.073122838', '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.074289839', '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.0850693514', '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.065015841', '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.086777843', '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.0813043516', '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.0859953517', '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.0832733518', '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.0661953519', '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.114241846', '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.118586847', '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.120703849', '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.0238933523', '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.2207993524', '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.132856851', '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.1241733525', '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.156605856', '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! Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Author Share Posted September 25, 2006 [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. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Author Share Posted September 25, 2006 [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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.