Solved

fetching data from MySQl database from php webpage

Posted on 2010-09-19
13
722 Views
Last Modified: 2013-12-12
Hi,
I have a mobile phone application that sends the devices gps location to a MySQL database every 30 seconds.  Using Google Static maps , my location can be tracked in real-time on  a web page
 webpage code
How it works is, the webpage uses php to get the latest coordinates from the database and uses these to position the car. The webpage is reloaded every 20 seconds by putting a refresh tag in the header.
when the page reloads the car is redrawn with the new coordinates.....
This all works fine live..ie i can be seen driving to my destination on  the webpage...

What I want to do now is play back my journeys .
I thought about  selecting the gps coordinates from the database, letting the page display, and then incrementing the sessionID before the next fetch of coordinates from the Database...
eg
$sessionID = 1
$query = "SELECT lat, lng
FROM Table1
WHERE sessionID = $sessionID;
$sessionID =($sessionID + 1);

however this wont work because the $sessionID variable will be reset everytime the page refreshes...
Anyone any ideas on how i might be able to "store" the $sessionID variable so that it can be used to increment the data fetched from the MySQL table?

thanks in advance for your attenntion :-)
(see webpage code)
 webpage code
<html>
  <head>
<meta http-equiv="refresh" content="20">
    <title>Static2</title>
  </head>

 <body>
<?php // RAY_google_static_map_class.php....from Ray Pasteur...
error_reporting(E_ALL);

// DEMONSTRATE HOW TO USE THE GOOGLE MAPS API
// FOR A STATIC MAP OF SPECIFIC SIZE
// OPTIONAL CENTERED PUSHPIN
// OPTIONAL OTHER PUSHPIN(S) OR ICONS
// MAN PAGE: http://code.google.com/apis/maps/documentation/staticmaps/

// DEFINE THE GOOGLE STATIC MAPPING CLASS
include("gsm_Class.php");

// MAKE A GOOGLE STATIC MAP OBJECT AND TEST IT OUT
$blue_car_url = 'http://www.blossompark.net/v3/cars.png';

//THIS SECTION GETS THE CURRENT LOCATION OF THE CAR FROM THE DATABASE
//this section defines the query to be fired at the database table

$query = "SELECT lat, lng
FROM Table1
ORDER BY sessionid DESC 
LIMIT 1";

$result = mysql_query($query, $sql);
if (mysql_num_rows($result) >= '1'){
while ($row = mysql_fetch_assoc($result)){
$lat = $row['lat'] ;
$lng = $row['lng'];
$comma =",";
$currentLocation= ($lat.$comma.$lng);

}
}else{
echo 'No records found.';
}

mysql_close($sql);

$x = new GSM;

// SET A CENTER POINT (BUT NO MARKER)
$x->setCenter($currentLocation);
$x->setZoom(15);

// PUT THE CAR ON THE MAP

$x->setIcon($currentLocation, $blue_car_url);

// ECHO THE <img /> TAG INTO OUR HTML TO DRAW THE MAP
echo $x->asIMG();
echo PHP_EOL . '<br clear="all" />';

// SHOW THE OBJECT
//echo "<pre>" . PHP_EOL;
?>

</body>
</html>

Open in new window

0
Comment
Question by:blossompark
  • 8
  • 5
13 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 33711406
What about basing the session ID on segments?  If the GPS coordinate between any two consecutive polls is identical, you're stopped and the session ID increments.  If the values are different, you're moving and the same session ID applies?


Kent
0
 

Author Comment

by:blossompark
ID: 33711458
Hi Kent,
            thanks for your response...regarding
"If the GPS coordinate between any two consecutive polls is identical,"
how can I compare the GPS coordinates between any  two consecutive polls? When the page is redrawn, my understanding is that all variables associated with that page are deleted and new ones are created so to compare I would somehow need to store the previous GPS coordinate...or am i missing something here?
table.jpg
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33711821
If you're using HTML, do you have access to the appropriate cookies?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 45

Expert Comment

by:Kdo
ID: 33711829

Actually, if you structure the query correctly you should be able to get the last two points.  You may be able to assume "same session" by their delta.
0
 

Author Comment

by:blossompark
ID: 33714592
Hi Kdo,
             firstly my knowledge in this area is limited....
also just  to be clear as to what I'm trying to achieve....I am looking for a way to replay my journey ie
fetch a location from database
....display it
...wait a few seconds
...fetch next location
...display it..
fetch next location until all locations displayed...
..loop

this I hope will show some sort of movement...I think you understood this  already so forgive me if i am overstating the point,but just to be clear about it :-)

Now as my knowledge re browsers, sessions etc is limited I am looking for guidance here....what s the best way for me to proceed? what is the best technology for me to learn about to proceed?
Are you suggesting that maybe I can store some persistent data (eg  the sessionID that was previously retrieved)   in a browser cookie that can be retrieved when the page refreshes?
if  this is the case, any recommendations of sites/documents i can look up to help me implement?
thanks for your time
0
 

Author Comment

by:blossompark
ID: 33714600
ps  just in case there is any confusion...the sessionID in the database table has nothing to do with  browser/server sessions,
0
 

Author Comment

by:blossompark
ID: 33714637
found something interesting here
http://econym.org.uk/gmap/cookies.htm
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33715495

If you're not going to use a middle-ware to manage the sessions, you'll need to make the application smart enough to know what data it has so it knows what data to seek next.

Assuming the database is capable of recording the tracking of multiple devices simultaneously, the table will need to contain at least these rows:

  ID integer,            -- auto_increment primary key.  Not strictly required
  Session  integer,  -- the "trip" identifier
  timex timestamp,  -- the event time
  lat float,                -- location latitude
  lon float                -- location longtitude


If you're using a middleware product, the connect to the database can be managed by that software and your application can simply "get next" via the middleware.

Otherwise, you'll have to send the session ID (the trip session, not application connection) and the ID (primary key) back to the host and read the next event in the trip.

There's a lot less overhead involved with running a middleware product.


Kent
0
 

Author Comment

by:blossompark
ID: 33716152
based on your last entry, i'm thinking of doing the following....let me know if you think it's credible?

to replay the car movement i create a php file (the middleware) that is fired each time the page is loaded (using a httprequest query string perhaps )…
1/ the php file is responsible for querying the database and retrieving the lat lng values ...

2/ the php file  returns the lat lng values to the "map" page which then uses these values to display the icon
 the php file is responsible for incrementing a counter that's value is used to determine the next set of lat lng values to return

3/ the page reloads
 4/ the php file fires again
5/ the php file returns the “next set of values”
6/ repeat


0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 33716184
That's an idea, but seems like the hard way.

Middleware normally runs on the server, or on a separate server logically between the application and DB server.  Putting it on the application isn't really middleware.

How many points can you accept in one call?  Can you fetch the entire trip (up to some limit, say 100 points) into the application in one call?  That way you can replay the entire trip, pan forward and backwards, and not have to go back to the database for additional points.


Kent
0
 

Author Comment

by:blossompark
ID: 33716714
here is an example of loading 100s in one call using xml ..
http://www.blossompark.net/v3/dispatcher2.htm

.i modified a tutorial from google maps website to create this... if i could replay these one at a time  i'd be sorted...
0
 

Author Comment

by:blossompark
ID: 33760506
hi all,
i've opened a new question regarding this issue
overlaying xml data onto google map incrementally using GDownloadUrl function
0
 

Author Closing Comment

by:blossompark
ID: 33760515

opened a new question titled
overlaying xml data onto google map incrementally using GDownloadUrl function
for this issue...
thanks for your input
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql date time 14 32
Can't get second accordion on page to work 2 7
Html CheckBox obtain Its Value 5 17
VBScript on Html 11 16
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question