Solved

fetching data from MySQl database from php webpage

Posted on 2010-09-19
13
718 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

758 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now