In this tutorial, we’ll show you how to get geolocation from a visitor’s IP using the Free IP2Location™ LITE Database in Symfony 5 project. This project supports for Symfony version 4 onward.
Step 1. Download Free IP2Location™ LITE Database
As the first step, you will need the CSV database of IP2Location in your Symfony 5 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 from https://lite.ip2location.com/database/db11-ip-country-region-city-latitude-longitude-zipcode-timezone.
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) }}
show.html.twig
<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>