php sql using odbc

Question

Hi Im making a website in php with a microsoft access database through odbc and i have come across a small problem let me show you my codes first.

<form method ="POST" action="maxtimestamplog.php">

Longitude <input type="text" name="longitude"  /><br/>
Latitude 
<input type ="text" name = "latitude"/>
<input name="ok" type="submit" value="Submit" />
</form>

<?

$dbc = odbc_connect("X1","","");    // Trying to establish connection
                                            // with database
echo "<br>";

$datetime =date('Y-m-d H:i:s', strtotime('+8 hours'));
echo "The Current date/time is $datetime";
echo "<br><br><br>";



if (!$dbc)
{
    exit("Connection failed:".$dbc);
}
$x_origin = $_POST['longitude'];
$y_origin = $_POST['latitude'];
$query = " SELECT m.vehicle_no, 
l.longitude, 
  l.latitude, 
  l.timestamp
FROM
  vehicle_log AS l,
  GPS_modem   AS m 
WHERE
  m.modem_ID = l.modem_ID
  AND l.timestamp = (
    SELECT MAX(timestamp) FROM vehicle_log WHERE modem_ID = l.modem_ID

  ) order by timestamp desc
";  // SQL Statement

$rs = odbc_exec($dbc,$query);
    if (!$rs)
    {
        exit("Error in SQL. ".$rs);
    }
    $i=1;
while (odbc_fetch_row($rs))
{
    $lng = odbc_result($rs, "longitude");
    $lat = odbc_result ($rs, "latitude");
    $ts = odbc_result ($rs, "timestamp");
    $vno = odbc_result ($rs, "vehicle_no");


    $yyyy= substr($ts, 0, 4);
    $mm= substr($ts, 5, 2);
    $dd= substr($ts, 8,2);
    $hr= substr($ts, 11, 2);
    $min= substr($ts, 14,2);
    $sec= substr($ts,17, 2);
    $cyyyy= substr($datetime, 0, 4);
    $cmm= substr($datetime, 5, 2);
    $cdd= substr($datetime, 8,2);
    $chr= substr($datetime, 11, 2);
    $cmin= substr($datetime, 14,2);
    $csec= substr($datetime,17, 2);

    $ctss = $csec 
                     + ($cmin * 60) 
                     + ($chr * 60 * 60)
                     + ($cdd * 24 * 60 * 60) 
                     + ($cmm * 30 * 24 * 60 * 60) 
                     + ($cyyyy * 365 * 24 * 60 * 60);
    $tss = $sec 
                     + (cmin * 60) + ($hr * 60 * 60) 
                     + ($dd * 24 * 60 * 60) 
                     + ($mm * 30 * 24 * 60 * 60) 
                     + ($yyyy * 365 * 24 * 60 * 60);

    $tssd = $ctss - $tss;

    $x = $lng;
    $y = $lat;
    $i = $i + 1;
    $xd = ($x - $x_origin);
    $yd = ($y - $y_origin);
    $d = ($xd*$xd) + ($yd*$yd);
    $td = sqrt($d);
    echo "Car Number $vno is at a distance $td away at timestamp $ts";
    echo "</br></br>";
}
odbc_close($dbc);
    ?>
</HTML>

what I want to be done now is to only display one output not all. Firstly I want to select only the data where variable $tssd is less than or equal to 10800 then i want to display the smallest $td out of what i selected and it shoud display

echo "Car Number $vno is at a distance $td away at timestamp $ts";
echo "</br></br>";

where $td is the least within $tssd is less than 10800 It should only display one

please try to help me im quite new to php and my sql statement is quite messy as it is i make blunder out of it.

Strictly speaking, you're not using an "MS Access" database, but a Jet database. The "Access" part of an MDB (forms/reports/etc.) is not usable via ODBC -- only the data tables and queries (i.e., the pure Jet objects) are usable. And yes, you're right that MS named the ODBC driver "MS Access". — David-W-Fenton
– David-W-Fenton, Commented Feb 15, 2009 at 21:38
Strictly speaking, its a DBMS, not a 'database', and they named the driver 'Microsoft Access'. But, hey, who needs to be strict? ;-) — onedaywhen
– onedaywhen, Commented Feb 16, 2009 at 9:40
Of course, folk often reference MS Access ojects (a form's control, the NZ() function, etc) in ACE/Jet objects (Validation Rule, VIEW, PROCEDURE, etc), thereby rendering ACE/Jet objects unusable via ODBC, OLE DB, etc. — onedaywhen
– onedaywhen, Commented Feb 16, 2009 at 9:42
I think it's pretty damned important to maintain the distinction between Access and Jet precisely because of what you point out -- they are two different problem spaces, and solutions for either one are going to be completely different. — David-W-Fenton
– David-W-Fenton, Commented Feb 16, 2009 at 22:56

Taylor Gerring · Accepted Answer · 2009-02-18 20:35:52Z

Try to separate the SQL from the PHP.

I believe you're asking how to return only a single row rather than all the rows. To accomplish this, use the "TOP" keyword. For example,

SELECT TOP 1
  m.vehicle_no, 
  l.longitude, 
  l.latitude, 
  l.timestamp

For the second part of your question, "where $td is the least within $tssd is less than 10800", we do not know the column names that these relate to. If we pretend they're named TD and TSSD in the vehicle_log table, then your statement might look like this:

SELECT TOP 1 m.vehicle_no, 
l.longitude, 
  l.latitude, 
  l.timestamp,
  l.TD,
  l.TSSD
FROM
  vehicle_log AS l,
  GPS_modem   AS m 
WHERE
  m.modem_ID = l.modem_ID
  AND l.timestamp = (
    SELECT MAX(timestamp) FROM vehicle_log WHERE modem_ID = l.modem_ID

  )
  AND l.TD <= 10800
  order by l.TD ASC

Lastly, I changed the ORDER BY to accommodate "i want to display the smallest $td".

If this is not what you are looking for, can you provide more information?

Collectives™ on Stack Overflow

php sql using odbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related