Solved

fetching data from MySQl database from php webpage

Posted on 2010-09-19
13
723 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:Kent Olsen
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:Kent Olsen
ID: 33711821
If you're using HTML, do you have access to the appropriate cookies?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 45

Expert Comment

by:Kent Olsen
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:Kent Olsen
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:
Kent Olsen 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In this tutorial viewers will learn how to code links for mobile sites that, once clicked, send a call or text to a specified number. For a telephone link (once clicked, calls a number), begin with a normal "<a href=" link tag. For the href, specify…
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …

856 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