How to detect the location from a visitor’s IP using the IP2Location database in Symfony 4

In this tutorial, we’ll show you how to get the current location from a visitor’s IP using the Free IP2Location™ LITE Database in Symfony 4 project. This project only supported for Symfony 4.

Step 1. Download Free IP2Location™ LITE Database
As the first step, you will need the CSV database of IP2Location in your Symfony 4 project. We recommend starting with the IP2Location™ DB11 LITE IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE Database which is free! Unzip the database file and import it into your MySQL table after you have downloaded it. You may also get the commercial IP Geolocation Database if you need higher accuracy.

1.Create ‘ip2location_db11’ table.

CREATE DATABASE ip2location; USE ip2location; CREATE TABLE `ip2location_db11`( `ip_from` INT(10) UNSIGNED, `ip_to` INT(10) UNSIGNED, `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `latitude` DOUBLE, `longitude` DOUBLE, `zip_code` VARCHAR(30), `time_zone` VARCHAR(8), INDEX `idx_ip_from` (`ip_from`), INDEX `idx_ip_to` (`ip_to`), INDEX `idx_ip_from_to` (`ip_from`, `ip_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. Import the data into table ‘ip2location_db11’.

LOAD DATA LOCAL INFILE 'IP2LOCATION-LITE-DB11.CSV' INTO TABLE `ip2location_db11` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;

Step 2. Create a Symfony project
Run the following command to create a Symfony 4 project.
> composer create-project symfony/website-skeleton <projectname>

Step 3. Creating routes & twig
Run these commands once in your project to add support.> composer require annotations
> composer require twig

Step 4. Configure env. file
Open your env. file and configure the database user, password and name in at this line.

DATABASE_URL=mysql://db_user:db_password@127.0.0.1:3306/db_name

Step 5. Change the following code.
Since our database has no primary key, you need to change some code in the following files.

1. Copy & paste the following codes in /vendor/doctrine/orm/lib/Doctrine/ORM/Mapping/Driver/DatabaseDriver.php file.

<?php /* * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * * This software consists of voluntary contributions made by many individuals * and is licensed under the MIT license. For more information, see * <http://www.doctrine-project.org>. */ namespace Doctrine\ORM\Mapping\Driver; use Doctrine\Common\Inflector\Inflector; use Doctrine\Common\Persistence\Mapping\Driver\MappingDriver; use Doctrine\Common\Persistence\Mapping\ClassMetadata; use Doctrine\DBAL\Schema\AbstractSchemaManager; use Doctrine\DBAL\Schema\SchemaException; use Doctrine\DBAL\Schema\Table; use Doctrine\DBAL\Schema\Column; use Doctrine\DBAL\Types\Type; use Doctrine\ORM\Mapping\ClassMetadataInfo; use Doctrine\ORM\Mapping\MappingException; /** * The DatabaseDriver reverse engineers the mapping metadata from a database. * * @link www.doctrine-project.org * @since 2.0 * @author Guilherme Blanco <guilhermeblanco@hotmail.com> * @author Jonathan Wage <jonwage@gmail.com> * @author Benjamin Eberlei <kontakt@beberlei.de> */ class DatabaseDriver implements MappingDriver { /** * @var AbstractSchemaManager */ private $_sm; /** * @var array|null */ private $tables = null; /** * @var array */ private $classToTableNames = []; /** * @var array */ private $manyToManyTables = []; /** * @var array */ private $classNamesForTables = []; /** * @var array */ private $fieldNamesForColumns = []; /** * The namespace for the generated entities. * * @var string|null */ private $namespace; /** * @param AbstractSchemaManager $schemaManager */ public function __construct(AbstractSchemaManager $schemaManager) { $this->_sm = $schemaManager; } /** * Set the namespace for the generated entities. * * @param string $namespace * * @return void */ public function setNamespace($namespace) { $this->namespace = $namespace; } /** * {@inheritDoc} */ public function isTransient($className) { return true; } /** * {@inheritDoc} */ public function getAllClassNames() { $this->reverseEngineerMappingFromDatabase(); return array_keys($this->classToTableNames); } /** * Sets class name for a table. * * @param string $tableName * @param string $className * * @return void */ public function setClassNameForTable($tableName, $className) { $this->classNamesForTables[$tableName] = $className; } /** * Sets field name for a column on a specific table. * * @param string $tableName * @param string $columnName * @param string $fieldName * * @return void */ public function setFieldNameForColumn($tableName, $columnName, $fieldName) { $this->fieldNamesForColumns[$tableName][$columnName] = $fieldName; } /** * Sets tables manually instead of relying on the reverse engineering capabilities of SchemaManager. * * @param array $entityTables * @param array $manyToManyTables * * @return void */ public function setTables($entityTables, $manyToManyTables) { $this->tables = $this->manyToManyTables = $this->classToTableNames = []; foreach ($entityTables as $table) { $className = $this->getClassNameForTable($table->getName()); $this->classToTableNames[$className] = $table->getName(); $this->tables[$table->getName()] = $table; } foreach ($manyToManyTables as $table) { $this->manyToManyTables[$table->getName()] = $table; } } /** * {@inheritDoc} */ public function loadMetadataForClass($className, ClassMetadata $metadata) { $this->reverseEngineerMappingFromDatabase(); if ( ! isset($this->classToTableNames[$className])) { throw new \InvalidArgumentException("Unknown class " . $className); } $tableName = $this->classToTableNames[$className]; $metadata->name = $className; $metadata->table['name'] = $tableName; $this->buildIndexes($metadata); $this->buildFieldMappings($metadata); $this->buildToOneAssociationMappings($metadata); foreach ($this->manyToManyTables as $manyTable) { foreach ($manyTable->getForeignKeys() as $foreignKey) { // foreign key maps to the table of the current entity, many to many association probably exists if ( ! (strtolower($tableName) === strtolower($foreignKey->getForeignTableName()))) { continue; } $myFk = $foreignKey; $otherFk = null; foreach ($manyTable->getForeignKeys() as $foreignKey) { if ($foreignKey != $myFk) { $otherFk = $foreignKey; break; } } if ( ! $otherFk) { // the definition of this many to many table does not contain // enough foreign key information to continue reverse engineering. continue; } $localColumn = current($myFk->getColumns()); $associationMapping = []; $associationMapping['fieldName'] = $this->getFieldNameForColumn($manyTable->getName(), current($otherFk->getColumns()), true); $associationMapping['targetEntity'] = $this->getClassNameForTable($otherFk->getForeignTableName()); if (current($manyTable->getColumns())->getName() == $localColumn) { $associationMapping['inversedBy'] = $this->getFieldNameForColumn($manyTable->getName(), current($myFk->getColumns()), true); $associationMapping['joinTable'] = [ 'name' => strtolower($manyTable->getName()), 'joinColumns' => [], 'inverseJoinColumns' => [], ]; $fkCols = $myFk->getForeignColumns(); $cols = $myFk->getColumns(); for ($i = 0, $colsCount = count($cols); $i < $colsCount; $i++) { $associationMapping['joinTable']['joinColumns'][] = [ 'name' => $cols[$i], 'referencedColumnName' => $fkCols[$i], ]; } $fkCols = $otherFk->getForeignColumns(); $cols = $otherFk->getColumns(); for ($i = 0, $colsCount = count($cols); $i < $colsCount; $i++) { $associationMapping['joinTable']['inverseJoinColumns'][] = [ 'name' => $cols[$i], 'referencedColumnName' => $fkCols[$i], ]; } } else { $associationMapping['mappedBy'] = $this->getFieldNameForColumn($manyTable->getName(), current($myFk->getColumns()), true); } $metadata->mapManyToMany($associationMapping); break; } } } /** * @return void * * @throws \Doctrine\ORM\Mapping\MappingException */ private function reverseEngineerMappingFromDatabase() { if ($this->tables !== null) { return; } $tables = []; foreach ($this->_sm->listTableNames() as $tableName) { $tables[$tableName] = $this->_sm->listTableDetails($tableName); } $this->tables = $this->manyToManyTables = $this->classToTableNames = []; foreach ($tables as $tableName => $table) { $foreignKeys = ($this->_sm->getDatabasePlatform()->supportsForeignKeyConstraints()) ? $table->getForeignKeys() : []; $allForeignKeyColumns = []; foreach ($foreignKeys as $foreignKey) { $allForeignKeyColumns = array_merge($allForeignKeyColumns, $foreignKey->getLocalColumns()); } // if ( ! $table->hasPrimaryKey()) { // throw new MappingException( // "Table " . $table->getName() . " has no primary key. Doctrine does not ". // "support reverse engineering from tables that don't have a primary key." // ); // } $pkColumns = []; if($table->hasPrimaryKey()){ $pkColumns = $table->getPrimaryKey()->getCoumns(); sort($pkColumns); } sort($allForeignKeyColumns); if ($pkColumns == $allForeignKeyColumns && count($foreignKeys) == 2) { $this->manyToManyTables[$tableName] = $table; } else { // lower-casing is necessary because of Oracle Uppercase Tablenames, // assumption is lower-case + underscore separated. $className = $this->getClassNameForTable($tableName); $this->tables[$tableName] = $table; $this->classToTableNames[$className] = $tableName; } } } /** * Build indexes from a class metadata. * * @param \Doctrine\ORM\Mapping\ClassMetadataInfo $metadata */ private function buildIndexes(ClassMetadataInfo $metadata) { $tableName = $metadata->table['name']; $indexes = $this->tables[$tableName]->getIndexes(); foreach ($indexes as $index) { if ($index->isPrimary()) { continue; } $indexName = $index->getName(); $indexColumns = $index->getColumns(); $constraintType = $index->isUnique() ? 'uniqueConstraints' : 'indexes'; $metadata->table[$constraintType][$indexName]['columns'] = $indexColumns; } } /** * Build field mapping from class metadata. * * @param \Doctrine\ORM\Mapping\ClassMetadataInfo $metadata */ private function buildFieldMappings(ClassMetadataInfo $metadata) { $tableName = $metadata->table['name']; $columns = $this->tables[$tableName]->getColumns(); $primaryKeys = $this->hasTablePrimaryKeys($this->tables[$tableName]); $foreignKeys = $this->getTableForeignKeys($this->tables[$tableName]); $allForeignKeys = []; foreach ($foreignKeys as $foreignKey) { $allForeignKeys = array_merge($allForeignKeys, $foreignKey->getLocalColumns()); } $ids = []; $fieldMappings = []; foreach ($columns as $column) { if (in_array($column->getName(), $allForeignKeys)) { continue; } $fieldMapping = $this->buildFieldMapping($tableName, $column); if ($primaryKeys && in_array($column->getName(), $primaryKeys)) { $fieldMapping['id'] = true; $ids[] = $fieldMapping; } $fieldMappings[] = $fieldMapping; } // We need to check for the columns here, because we might have associations as id as well. if ($ids && count($primaryKeys) == 1) { $metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_AUTO); } foreach ($fieldMappings as $fieldMapping) { $metadata->mapField($fieldMapping); } } /** * Build field mapping from a schema column definition * * @param string $tableName * @param \Doctrine\DBAL\Schema\Column $column * * @return array */ private function buildFieldMapping($tableName, Column $column) { $fieldMapping = [ 'fieldName' => $this->getFieldNameForColumn($tableName, $column->getName(), false), 'columnName' => $column->getName(), 'type' => $column->getType()->getName(), 'nullable' => ( ! $column->getNotnull()), ]; // Type specific elements switch ($fieldMapping['type']) { case Type::TARRAY: case Type::BLOB: case Type::GUID: case Type::JSON_ARRAY: case Type::OBJECT: case Type::SIMPLE_ARRAY: case Type::STRING: case Type::TEXT: $fieldMapping['length'] = $column->getLength(); $fieldMapping['options']['fixed'] = $column->getFixed(); break; case Type::DECIMAL: case Type::FLOAT: $fieldMapping['precision'] = $column->getPrecision(); $fieldMapping['scale'] = $column->getScale(); break; case Type::INTEGER: case Type::BIGINT: case Type::SMALLINT: $fieldMapping['options']['unsigned'] = $column->getUnsigned(); break; } // Comment if (($comment = $column->getComment()) !== null) { $fieldMapping['options']['comment'] = $comment; } // Default if (($default = $column->getDefault()) !== null) { $fieldMapping['options']['default'] = $default; } return $fieldMapping; } /** * Build to one (one to one, many to one) association mapping from class metadata. * * @param \Doctrine\ORM\Mapping\ClassMetadataInfo $metadata */ private function buildToOneAssociationMappings(ClassMetadataInfo $metadata) { $tableName = $metadata->table['name']; $primaryKeys = $this->hasTablePrimaryKeys($this->tables[$tableName]); $foreignKeys = $this->getTableForeignKeys($this->tables[$tableName]); foreach ($foreignKeys as $foreignKey) { $foreignTableName = $foreignKey->getForeignTableName(); $fkColumns = $foreignKey->getColumns(); $fkForeignColumns = $foreignKey->getForeignColumns(); $localColumn = current($fkColumns); $associationMapping = [ 'fieldName' => $this->getFieldNameForColumn($tableName, $localColumn, true), 'targetEntity' => $this->getClassNameForTable($foreignTableName), ]; if (isset($metadata->fieldMappings[$associationMapping['fieldName']])) { $associationMapping['fieldName'] .= '2'; // "foo" => "foo2" } if ($primaryKeys && in_array($localColumn, $primaryKeys)) { $associationMapping['id'] = true; } for ($i = 0, $fkColumnsCount = count($fkColumns); $i < $fkColumnsCount; $i++) { $associationMapping['joinColumns'][] = [ 'name' => $fkColumns[$i], 'referencedColumnName' => $fkForeignColumns[$i], ]; } // Here we need to check if $fkColumns are the same as $primaryKeys if ( ! array_diff($fkColumns, $primaryKeys)) { $metadata->mapOneToOne($associationMapping); } else { $metadata->mapManyToOne($associationMapping); } } } /** * Retrieve schema table definition foreign keys. * * @param \Doctrine\DBAL\Schema\Table $table * * @return array */ private function getTableForeignKeys(Table $table) { return ($this->_sm->getDatabasePlatform()->supportsForeignKeyConstraints()) ? $table->getForeignKeys() : []; } /** * Retrieve schema table definition primary keys. * * @param \Doctrine\DBAL\Schema\Table $table * * @return array */ private function hasTablePrimaryKeys(Table $table) { try { return ($table->hasPrimaryKey())?$table->getPrimaryKey()->getColumns():array(); } catch (SchemaException $e) { // Do nothing } return []; } /** * Returns the mapped class name for a table if it exists. Otherwise return "classified" version. * * @param string $tableName * * @return string */ private function getClassNameForTable($tableName) { if (isset($this->classNamesForTables[$tableName])) { return $this->namespace . $this->classNamesForTables[$tableName]; } return $this->namespace . Inflector::classify(strtolower($tableName)); } /** * Return the mapped field name for a column, if it exists. Otherwise return camelized version. * * @param string $tableName * @param string $columnName * @param boolean $fk Whether the column is a foreignkey or not. * * @return string */ private function getFieldNameForColumn($tableName, $columnName, $fk = false) { if (isset($this->fieldNamesForColumns[$tableName]) && isset($this->fieldNamesForColumns[$tableName][$columnName])) { return $this->fieldNamesForColumns[$tableName][$columnName]; } $columnName = strtolower($columnName); // Replace _id if it is a foreignkey column if ($fk) { $columnName = str_replace('_id', '', $columnName); } return Inflector::camelize($columnName); } }

2. Find the function identifierRequired($entityName) and comment on the following codes in /vendor/doctrine/orm/lib/Doctrine/ORM/Mapping/MappingException.php file.

// /** // * @param string $entityName // * // * @return MappingException // */ // public static function identifierRequired($entityName) // { // if (false !== ($parent = get_parent_class($entityName))) { // return new self(sprintf( // 'No identifier/primary key specified for Entity "%s" sub class of "%s". Every Entity must have an identifier/primary key.', // $entityName, $parent // )); // } // return new self(sprintf( // 'No identifier/primary key specified for Entity "%s". Every Entity must have an identifier/primary key.', // $entityName // )); // }

3. Find and comment on the following codes in /vendor/doctrine/orm/lib/Doctrine/ORM/Mapping/ClassMetadataInfo.php file.

// // Verify & complete identifier mapping // if ( ! $this->identifier) { // throw MappingException::identifierRequired($this->name); // }

Step 6. Import database & generate getter and setter methods.
Run these commands to import the database and generate getter and setter.> php bin/console doctrine:mapping:import "App/Entity" annotations --path=src/Entity
> php bin/console make:entity --regenerate App

Sep 7. Below are the sample codes. DefaultController.php

<?php namespace App\Controller; use App\Entity\Ip2location; use Symfony\Component\HttpFoundation\Request; use App\Repository\Ip2locationRepository; use Symfony\Component\Form\Extension\Core\Type\SubmitType; use Symfony\Component\Form\Extension\Core\Type\TextType; use Symfony\Bundle\FrameworkBundle\Controller\AbstractController; use Symfony\Component\Routing\Annotation\Route; /** * @Route("/", name="ip2location.") */ class IPController extends AbstractController { /** * @Route("/home", methods={"GET", "HEAD"}) * @param Ip2locationRepository $rep * @return \Symfony\Component\HttpFoundation\Response */ public function index(Ip2locationRepository $rep) { $Ip2location = new Ip2location(); return $this->render('ip/index.html.twig',[]); } //Create a search form to search the location of the user by their IP address. public function searchBar() { $form = $this->createFormBuilder() ->setAction($this->generateUrl('ip2location.result')) ->add('ip', TextType::class, [ 'label' => 'IP Address: ' ]) ->add('submit', SubmitType::class, [ 'attr' => [ 'class' => 'btn btn-primary' ] ]) ->getForm(); return $this->render('ip/searchBar.html.twig', [ 'form' => $form->createView(), ]); } //Display the location result /** * @Route("/result", name="result") * @param Request $request */ public function result(Request $request, Ip2locationRepository $rep) { $query = $request->request->get('form')['ip']; if($query){ $ip = $rep->Dot2LongIP($query); $ipnum = $rep->findByIP($ip); } return $this->render('ip/show.html.twig', [ 'ipnum' => $ipnum, 'ip' => $query ]); } }

Repository.php

//SQL query string to match the recordset that the IP number fall between the valid range public function findByIP($ipaddress) { $conn = $this->getEntityManager()->getConnection(); $sql = ' SELECT * FROM ip2location i WHERE :ip <= i.ip_to LIMIT 1'; $stmt = $conn->prepare($sql); $stmt->execute(['ip' => $ipaddress]); return $stmt->fetchAll(); } //Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1) public function Dot2LongIP($ipaddr) { if($ipaddr == ""){ return 0; } else { $ips = explode(".", $ipaddr); return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256); } }

searchBar.html.twig

{{ form(form) }}
<h3>Your Location</h3> <p>IP Address: {{ ip }} </p> {% for key in ipnum %} <div> <p>Country Code: {{ key.country_code }} </p> <p>Country Name: {{ key.country_name }} </p> <p>Region Name: {{ key.region_name }} </p> <p>City Name: {{ key.city_name }} </p> <p>Latitude: {{ key.latitude }} </p> <p>Longitude: {{ key.longitude }} </p> <p>Zip Code: {{ key.zip_code }} </p> <p>Time Zone: {{ key.time_zone }} </p> </div> {% endfor %}

base.html.twig

<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>{% block title %}Welcome!{% endblock %}</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous"> {% block stylesheets %}{% endblock %} </head> <body> <div class="container"> {{ render(controller( 'App\\Controller\\IPController::searchBar' ))}} {% block body %}{% endblock %} </div> {% block javascripts %}{% endblock %} </body> </html>

Originally published at https://blog.ip2location.com on November 8, 2019.