Mercurial > hg > LGMap
annotate get_coordinates_for_listed_books.php @ 2:b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
| author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
|---|---|
| date | Tue, 24 Mar 2015 16:23:48 +0100 |
| parents | de2c442b6cbb |
| children | 30b59e7b88c0 |
| rev | line source |
|---|---|
| 0 | 1 <?php |
|
1
de2c442b6cbb
config to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
2 include_once('config/Lib_mb_utf8.php'); |
|
de2c442b6cbb
config to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
3 include_once('config/config.php'); |
| 0 | 4 |
| 5 set_time_limit(0); | |
| 6 ini_set('memory_limit', '-1'); | |
| 7 | |
| 8 $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); | |
| 9 mysql_query("SET NAMES utf8"); | |
| 10 | |
| 11 if (!$link_mysql) { | |
| 12 die('Could not connect: ' . mysql_error()); | |
| 13 } | |
| 14 | |
| 15 $db_selected = mysql_select_db($mysql_database, $link_mysql); | |
| 16 if (!$db_selected) { | |
| 17 die ('Can\'t use foo : ' . mysql_error()); | |
| 18 } | |
| 19 | |
| 20 function readCsvFile($fileName){ | |
|
1
de2c442b6cbb
config to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
21 global $server_host; |
|
de2c442b6cbb
config to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
22 $fp=fopen($server_host."LGSearch/csv_files/".$fileName,"r"); |
| 0 | 23 $data=fgetcsv($fp); |
| 24 $columnNameMappingArray=['BOOK_ID','LEVEL1','LEVEL2', | |
| 25 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','PAGE','SECTION','CONTENT', | |
| 26 'Description']; //columns from input array/file | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
27 $columnNameArray=['Address','LEVEL1','LEVEL2', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
28 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
29 'PAGE','SECTION','CONTENT', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
30 'BOOK_ID','CHGIS_ID','1820_ID','1911_ID','CBDB_ID','Description']; //columns for output array/file (for map.php) |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
31 /* |
| 0 | 32 $columnNameArray=['Address','ADMIN_TYPE','LEVEL1','LEVEL2', |
| 33 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude', | |
| 34 'PAGE','SECTION','CONTENT', | |
| 35 'BOOK_ID','CHGIS_ID','1820_ID','1911_ID','CBDB_ID','Description']; //columns for output array/file (for map.php) | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
36 */ |
| 0 | 37 $columnNameArray=array_flip($columnNameArray); //key, value swap in order to keep the order of the columns after merge |
| 38 $bookArray=array(); | |
| 39 $count=0; | |
| 40 $line=0; | |
| 41 while(!feof($fp)){ | |
| 42 $data=fgetcsv($fp); | |
| 43 if(sizeof($data)<sizeof($columnNameMappingArray)){ | |
| 44 continue; | |
| 45 } | |
| 46 ///$bookId=$data[0]; | |
| 47 foreach($columnNameMappingArray as $idx=>$newName){ //use column names as the array indices (instead of 0,1,2,... as from $data) | |
| 48 $bookInfo[$newName]=$data[$idx]; | |
| 49 } | |
| 50 $bookId=$bookInfo['BOOK_ID']; | |
| 51 $coordinateArray=getCoordinateFromDatabase($bookId); | |
| 52 foreach($coordinateArray as $coordinate){ //merge a record from $booArray and ots cooresponding coordinateArray | |
| 53 $bookArray[$count]=array_merge($bookInfo,$coordinate); | |
| 54 $bookArray[$count]=array_merge($columnNameArray,$bookArray[$count]); | |
| 55 $count++; | |
| 56 } | |
| 57 } | |
| 58 fclose($fp); | |
| 59 return $bookArray; | |
| 60 } | |
| 61 function writeCsvFile($fileName,$bookArray){ | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
62 /* |
| 0 | 63 $columnNameArray=['Address','ADMIN_TYPE','LEVEL1','LEVEL2', |
| 64 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude', | |
| 65 'PAGE','SECTION','CONTENT', | |
| 66 'BOOK_ID','CHGIS_ID','1820_ID','1911_ID','CBDB_ID','Description']; | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
67 */ |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
68 $columnNameArray=['Address','LEVEL1','LEVEL2', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
69 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
70 'PAGE','SECTION','CONTENT', |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
71 'BOOK_ID','CHGIS_ID','1820_ID','1911_ID','CBDB_ID','Description']; |
| 0 | 72 $fp=fopen("./datasets/".$fileName,"w"); |
| 73 fputcsv($fp,$columnNameArray); | |
| 74 | |
| 75 foreach($bookArray as $book){ | |
| 76 fputcsv($fp,$book); | |
| 77 } | |
| 78 fclose($fp); | |
| 79 } | |
| 80 function getCoordinateFromDatabase($bookId){ | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
81 /* |
| 0 | 82 $query="SELECT place_name AS Address, admin_type AS ADMIN_TYPE, x AS Longitude, y AS Latitude, |
| 83 chgis_id AS CHGIS_ID, 1820_id AS 1820_ID, 1911_id AS 1911_ID, cbdb_id AS CBDB_ID | |
| 84 FROM coordinates_books | |
| 85 WHERE books_id='".$bookId."'"; | |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
86 */ |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
87 $query="SELECT place_name AS Address, x AS Longitude, y AS Latitude, |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
88 chgis_id AS CHGIS_ID, 1820_id AS 1820_ID, 1911_id AS 1911_ID, cbdb_id AS CBDB_ID |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
89 FROM coordinates_books |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
90 WHERE books_id='".$bookId."'"; |
| 0 | 91 $result = mysql_query($query); |
|
2
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
92 if (!$result) { |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
93 echo mysql_error(); |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
94 return; |
|
b85894a9b2dc
bug fixed: mysql query error due to updated coordinates_books table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
95 } |
| 0 | 96 $coordinateArray=array(); |
| 97 while ($row = mysql_fetch_assoc($result)) { | |
| 98 $coordinateArray[]=$row; | |
| 99 } | |
| 100 return $coordinateArray; | |
| 101 } | |
| 102 | |
| 103 $fileName=$_GET['file']; | |
| 104 $name=$_GET['name']; | |
| 105 $bookArray=readCsvFile($fileName); | |
| 106 writeCsvFile($fileName,$bookArray); | |
| 107 header("Location: map.php?mode=1&file=".$fileName."&name=".$name); | |
| 108 ?> |