Category Archives: mysql

How to set up the Gps Tracker MySql database using phpMyAdmin

This problem has plagued a lot of people. I even made a video in 2008 explaining how to use phpMyAdmin. I just watched it again and it’s still relevant 6 years later. Check out the video on my youtube channel, it’s only four minutes and will help:

Google Map GPS Cell Phone Tracker and PhpMyAdmin

The following are the written instructions for those who prefer it. The first thing you need to do is create a new database. When you have the name of the database, make sure and change it in the php connection file:

https://github.com/nickfox/GpsTracker/blob/master/servers/php/dbconnect.php

$dbhost = 'localhost';
$dbuser = 'gpstracker_user';
$dbpass = 'gpstracker';
$dbname = 'gpstracker';

You need to make sure all those values are correct.

Next take a look at the sql script in the github repo, you’ll first want to cut and paste the CREATE TABLE command into the SQL tab of phpMyAdmin. After you’ve created the table, you’ll want to add all the stored procedures. When you look in the sql script, you’ll see something like this for the procedure prcGetRouteForMap:

CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRouteForMap`(

This is generated automatically by mySql but should not be used if you are creating the procedure by hand. This is how the procedure should be run:

CREATE PROCEDURE prcGetRouteForMap(
_sessionID VARCHAR(50),
_phoneNumber VARCHAR(50))
BEGIN
  SELECT
  CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json
  FROM gpslocations
  WHERE sessionID = _sessionID
  AND phoneNumber = _phoneNumber
  ORDER BY lastupdate;
END

Note that the DEFINER is now gone. Now look in the lower left hand corner of the SQL tab of phpMyAdmin. Do you see the delimiter box? Remove the semi-colon ; if there is one and add two forward slashes // to the box. Do this for each of the stored procedures (total of 6) and you’re almost done. Finally do the INSERT INTO command and insert the location data into the db. Now are you done setting up the database. The next step is to test your installation by going to the following page:

http://www.yoursite.com/gpstracker/displaymap.php

on your website. You should see the red message at top saying “Choose a route below” and with that you should be good to go. Just point one of the phone clients to your updatelocation.php file and start receiving updates.