[SOLVED] PHP+XML+MYSQL - Loop through rows

[Fetchitnow]

Hello Everyone.

 

I found this forum and I hope that I can get the answer from one of you regarding some code that I have written. I am just learning all this and find it very interesting. Hopefully one of you will have the answer to this little problem.

 

Here is what I am trying to do.

 

I have a set of members who have a tracking facility on their phones. Each member has a unique reference number. What I am trying to do is call their 'location' from a unique URL and then store the information in a table. The problem I am having at the moment is that it gets all the information and displays it but does not save each individual record?

 

?php

 

 

 

//opens database

 

$username="username";

 

$password="password";

 

$database="database";

 

mysql_connect("localhost",$username,$password) or die("Connection failed");

 

@mysql_select_db($database) or die( "Unable to select database");

 

 

 

//creates the table

 

mysql_query ("CREATE TABLE tracking_location(

 

userid VARCHAR( 25 ) NOT NULL ,

 

lat VARCHAR( 25 ) NOT NULL ,

 

lng VARCHAR( 25 ) NOT NULL ,

 

alt VARCHAR( 25 ) NOT NULL ,

 

date VARCHAR( 25 ) NOT NULL ,

 

time VARCHAR( 25 ) NOT NULL ,

 

local_date VARCHAR( 25 ) NOT NULL ,

 

local_time VARCHAR( 25 ) NOT NULL ,

 

mph VARCHAR( 25 ) NOT NULL ,

 

kph VARCHAR( 25 ) NOT NULL ,

 

heading VARCHAR( 25 ) NOT NULL ,

 

icon VARCHAR( 25 ) NOT NULL ,

 

degrees VARCHAR( 25 )");

 

 

 

//writes the information to the table “tracked_vehicles

 

function writelnglat()

 

{

 

mysql_query("INSERT INTO tracking_location (locate,lat,lng,alt,date,time,local_date,local_time,mph,kph,heading,icon,degrees)

 

VALUES ('$locate','$lat','$lng','$alt','$date','$time','$local_date','$local_time','$mph','$kph','$heading','$icon','$degrees')");

 

}

 

 

 

//Checks the table where all tracked vehicles ID’s are stored.

 

$result = mysql_query ('SELECT locate FROM tracked_vehicles WHERE locate > 0') or die(mysql_error());

 

while ($row=mysql_fetch_array($result)){

 

$locateid=$row["locate"];

//Calls the information from the website. Each ID eg 8377 needs to be different.

$xml = simplexml_load_file("http://www.website/data.php?userid=$locateid"); 

 

$locate = $xml->marker['userid'];

 

$lat = $xml->marker['lat'];

 

$lng = $xml->marker['lng'];

 

$alt = $xml->marker['alt'];

 

$date = $xml->marker['date'];

 

$time = $xml->marker['time'];

 

$local_date = $xml->marker['local_date'];

 

$local_time = $xml->marker['local_time'];

 

$mph = $xml->marker['mph'];

 

$kph = $xml->marker['kph'];

 

$heading = $xml->marker['heading'];

 

$icon = $xml->marker['icon'];

 

$degrees = $xml->marker['degrees'];

 

 

 

// output the values to the screen

 

echo 'locate = '.$locate.'<br>';

 

echo 'lat = '.$lat.'<br>';

 

echo 'lng = '.$lng.'<br>';

 

echo 'alt = '.$alt.'<br>';

 

echo 'date = '.$date.'<br>';

 

echo 'time = '.$time.'<br>';

 

echo 'local date = '.$local_date.'<br>';

 

echo 'local time = '.$local_time.'<br>';

 

echo 'mph = '.$mph.'<br>';

 

echo 'kph = '.$kph.'<br>';

 

echo 'heading = '.$heading.'<br>';

 

echo 'icon = '.$icon.'<br>';

 

echo 'degrees = '.$degrees.'<br>';

 

 

 

//writes information to the table.

if ($locate > 0) {

writelnglat();

}

}

 

 

?>

[artacus]

This is more a PHP question. Your variables are not going to be visible from inside the function unless you use global $var...

 

I'd just take the sql out of the function.

[Fetchitnow]

Hello Everyone.

 

After looking again I have come up with the following solution.

 

?php



//opens database

$username="username";

$password="password";

$database="database";

mysql_connect("localhost",$username,$password) or die("Connection failed");

@mysql_select_db($database) or die( "Unable to select database");



//creates the table

mysql_query ("CREATE TABLE tracking_location(

userid VARCHAR( 25 ) NOT NULL ,

lat VARCHAR( 25 ) NOT NULL ,

lng VARCHAR( 25 ) NOT NULL ,

alt VARCHAR( 25 ) NOT NULL ,

date VARCHAR( 25 ) NOT NULL ,

time VARCHAR( 25 ) NOT NULL ,

local_date VARCHAR( 25 ) NOT NULL ,

local_time VARCHAR( 25 ) NOT NULL ,

mph VARCHAR( 25 ) NOT NULL ,

kph VARCHAR( 25 ) NOT NULL ,

heading VARCHAR( 25 ) NOT NULL ,

icon VARCHAR( 25 ) NOT NULL ,

degrees VARCHAR( 25 )");

//Checks the table where all tracked vehicles ID’s are stored.

$result = mysql_query ('SELECT locate FROM tracked_vehicles WHERE locate > 0') or die(mysql_error());

while ($row=mysql_fetch_array($result)){

$locateid=$row["locate"];
//Calls the information from the website. Each ID eg 8377 needs to be different.
$xml = simplexml_load_file("http://www.website/data.php?userid=$locateid"); 

$locate = $xml->marker['userid'];

$lat = $xml->marker['lat'];

$lng = $xml->marker['lng'];

$alt = $xml->marker['alt'];

$date = $xml->marker['date'];

$time = $xml->marker['time'];

$local_date = $xml->marker['local_date'];

$local_time = $xml->marker['local_time'];

$mph = $xml->marker['mph'];

$kph = $xml->marker['kph'];

$heading = $xml->marker['heading'];

$icon = $xml->marker['icon'];

$degrees = $xml->marker['degrees'];



// output the values to the screen

echo 'locate = '.$locate.'<br>';

echo 'lat = '.$lat.'<br>';

echo 'lng = '.$lng.'<br>';

echo 'alt = '.$alt.'<br>';

echo 'date = '.$date.'<br>';

echo 'time = '.$time.'<br>';

echo 'local date = '.$local_date.'<br>';

echo 'local time = '.$local_time.'<br>';

echo 'mph = '.$mph.'<br>';

echo 'kph = '.$kph.'<br>';

echo 'heading = '.$heading.'<br>';

echo 'icon = '.$icon.'<br>';

echo 'degrees = '.$degrees.'<br>';



//writes information to the table.
if ($locate > 0) {
function writelnglat()

{

mysql_query("INSERT INTO tracking_location (locate,lat,lng,alt,date,time,local_date,local_time,mph,kph,heading,icon,degrees)

VALUES ('$locate','$lat','$lng','$alt','$date','$time','$local_date','$local_time','$mph','$kph','$heading','$icon','$degrees')");

}
}
}


?>

 

Seems that the function was hindering things as mentioned. Took just a little while to think through but thanks for the pointer.