run code in 300+ PHP versions simultaneously
<?php /*TODO... parse the sql query make a query object or something to put almost everything in an object and make this code simpler return nested name-value pairs in an associative array and convert it to json before sending aa s JSON content type send all changes made from Deletes, Updates and Append (mutations) changes for passive data binding */ $sql = "SELECT * FROM 'main' WHERE 'destination' = 'Helsinki' AND 'price' > 120"; //example of sql query //the following assumes the above SQL query has been parsed into fields and conditions //main header file. Stored as JSON. $main_db_header_contents = '[{"rowlength":60},{"id":{"length":3,"datatype":"integer"}},{"name_sname":{"length":9,"datatype":"String"}},{"identity":{"length":9,"datatype":"integer"}},{"address":{"length":8,"datatype":"String"}},{"region":{"length":7,"datatype":"String"}},{"hours":{"length":5,"datatype":"Time"}},{"destination":{"length":8,"datatype":"String"}},{"price":{"length":3,"datatype":"integer"}}]'; //json string. fairly immutable so it can be cached on server $main_db_header = json_decode($main_db_header_contents,true); //main database file. Columns are fixed length and padded. Stored as strings only. ID field must be sorted alphabetically. //When fields in this file are updated/added to, their respective index file must be too $main_db_file_contents = "001|Al-Farabi|123456789|Baghdad |Iraq |21:02|Ottawa |120;002|Smith |123456788|Ottawa |Canada |22:03|Helsinki|131;003|Locke |123456787|Helsinki|Finland|23:01|Istanbul|122;004|Jones |123456786|Istanbul|Turkey |21:02|Helsinki|123;005|Steinman |123455785|Havana |Cuba |22:03|Havana |104;006|Booker |123456784|Moscow |Russia |23:01|Helsinki|115;"; //fixed-length field values //Destination index file with record id and destination value. Sorted alphabetically. //E.g., The record with the id of 004 has the destination value of 'Moscow '. $destination_index_file_contents = "005|Havana ;002|Helsinki;004|Helsinki;006|Helsinki;003|Istanbul;001|Ottawa "; //Destination index file. Sorted alphabetically despite being numberic. //E.g., The record with the id of 005 has the price value of 104. $price_index_file_contents = "005|104;006|115;001|120;003|122;004|123;002|131"; function apply_condition($index_file_contents, $ptype, $value) { //applies basic logical conditions from the the query //Uses the given index file and checks its value based on given condition and given value $found_matches = []; $index_file_contents_split = explode(";", $index_file_contents); foreach($index_file_contents_split as $key => $props){ $pairs = explode("|",$props); $id = $pairs[0]; $val = trim($pairs[1]); //removes trailing spaces for matching non-fiexed-length input value if ($ptype === "=") { //checks to see if the given value is equal to the value in the index file if ($val === $value) { array_push($found_matches,$id); //add to new array of matches } } elseif ($ptype === ">") { //checks to see if the given value is greater than the value in the index file if ($val > $value) { array_push($found_matches,$id);//add to new array of matches } } } return $found_matches; } $parsed_destinations = apply_condition($destination_index_file_contents, "=", "Helsinki"); $parsed_prices = apply_condition($price_index_file_contents, ">", "120"); $parsed_ids = array_intersect($parsed_destinations, $parsed_prices); sort($parsed_ids); //alphabetically sorts array by ascending (lowest to highest) //now go through $main_db_file_contents and get selected fields in records matching the ids in $parsed_ids. $id_length = $main_db_header[1]['id']['length']; //id, length, datatype $destination_offset = get_field_offset("destination", $main_db_header); $price_offset = get_field_offset("price", $main_db_header); $destination_length = $main_db_header[7]['destination']['length']; //8 $price_length = $main_db_header[8]['price']['length']; //3 $row_length = $main_db_header[0]['rowlength']; //147 $main_file_length = strlen($main_db_file_contents); //can be done on a text file using the OS $total_main_file_rows = ($main_file_length/$row_length); $results =[]; $starting_row = 0; for ($i = 0; $i < count($parsed_ids); $i++){ //Starting row is advanced (increased) with each loop. It advances half as far as the previous advancement $starting_row = get_starting_row($main_db_file_contents, $parsed_ids[$i], $id_length, $row_length, $total_main_file_rows, $starting_row); $destination = get_field_from_row($main_db_file_contents, $starting_row, $row_length, $destination_offset, $destination_length); $price = get_field_from_row($main_db_file_contents, $starting_row, $row_length, $price_offset, $price_length); $result = [$parsed_ids[$i], $destination, $price]; array_push($results, $result); } function get_field_from_row($db, $current_row, $row_length, $field_offset, $field_length) { //gets the row index and uses the field's offset (start) and length (end) to get its value in the database text $value = substr($db, ($current_row * $row_length) + $field_offset, $field_length); return $value; } function get_starting_row($db, $first_id, $id_length, $row_length, $total_main_file_rows, $offset){ //Finds the record that the given ID is located in and returns its row index $start_row = (int)floor($total_main_file_rows / 2) + $offset; //halfway point of file $start_row_id = substr($db, ($start_row) * $row_length , $id_length); //gets current row id while ($start_row > -1 && $start_row <= $total_main_file_rows) { if ( $first_id < $start_row_id){ //go back a bit less $start_row = (int)floor($start_row / 2); $start_row_id = substr($db, ($start_row) * $row_length , $id_length); } elseif($start_row_id < $first_id) { //go forward a bit less $start_row = $start_row + (int)round($start_row / 2); $start_row_id = substr($db, ($start_row) * $row_length , $id_length); } else { //found the row id that matches the given id. Return its index. return $start_row; } } } function get_field_offset($field, $header) { //goes through the JSON file to get length of each field preceding the given field //used to get the start of the field in the row (a row offset is needed too) $total_offset = 0; foreach ($header as $key => $value){ foreach ($value as $k => $val) { if ($k != "rowlength"){ if ($field === (string)$k){ return $total_offset; } $total_offset += $val['length'] +1; //+1 is needed for each single-character delimeter (|) } } } } var_dump($results); //works!!
Output for 7.2.0 - 7.2.34, 7.3.0 - 7.3.23, 7.4.0 - 7.4.11, 8.0.0alpha1 - rc2
array(2) { [0]=> array(3) { [0]=> string(3) "002" [1]=> string(8) "Helsinki" [2]=> string(3) "131" } [1]=> array(3) { [0]=> string(3) "004" [1]=> string(8) "Helsinki" [2]=> string(3) "123" } }