Change timeouts in my.ini (mysql ini file in xampp) change the following values to:
SET GLOBAL connect_timeout = 600;
SET GLOBAL net_read_timeout = 600;
Now restart mysql in xampp.
Put .sql file to import somewhere where you can find it
Put the .sql file into c:/xampp/mysql/
Now from the command prompt at c:/xampp/mysql/bin, type
mysql -u root -p … and hit enter
Now hit enter at password prompt as no password is needed on XAMPP default root user in mysql
Now type use [database]
Now we want to change the timeout settings within mysql:
SHOW VARIABLES LIKE "%timeout";
we want connect_timeout to be 600 (seconds) and also net_read_timeout
So type
SET GLOBAL connect_timeout = 600;
SET GLOBAL net_read_timeout = 600;
Now crucially, at the top of the .sql (worldcities) database from simple maps, add this line ABOVE the create table statement
SET NAMES utf8mb4;
This will stop the database table being filled up with garbage characters when there are language accents in the words.
Now in the console at MariaDB[[databasename]]> type
SET autocommit=0 ; source ../[mysqlfilename].sql ; COMMIT;
and the file will start being executed
Now lets create a copy of this only containing the French country
DROP TABLE IF EXISTS worldcitiesfrance;
CREATE TABLE worldcitiesfrance (
city VARCHAR(120),
city_ascii VARCHAR(120),
city_alt VARCHAR(1000),
lat FLOAT,
lng FLOAT,
country VARCHAR(120),
iso2 VARCHAR(2),
iso3 VARCHAR(3),
admin_name VARCHAR(120),
admin_name_ascii VARCHAR(120),
admin_code VARCHAR(6),
admin_type VARCHAR(27),
capital VARCHAR(7),
density FLOAT,
population FLOAT,
population_proper FLOAT,
ranking INT,
timezone VARCHAR(120),
same_name VARCHAR(5),
id VARCHAR(10)
);
Now copy only the france data from the original table into the new one.
INSERT INTO worldcitiesfrance
SELECT * FROM worldcities
WHERE worldcities.iso2="FR";
———–
Changing new worldcitiesfrance table to work in pmf
-> = needs to become
city_ascii -> city_name_ascii
admin_name_ascii -> state
lat -> latitude
lng -> longitude
id -> france_full_search_id
On displays of the locations, we will show the names with characters
So
“city” will be used as the city/cillage DISPLAY (this is the country with french accents)
“admin_name” will be used as the state DISPLAY (this is the state with french accents)
“country” will be used as the country DISPLAY (this is FRANCE)
Now rename the original francefullsearch to francefullsearch_original
RENAME TABLE francefullsearch TO francefullsearch_original
now rename the new table to francefullsearch
RENAME TABLE worldcitiesfrance TO francefullsearch