how to retrieve and display the last data entered in MSSQL DATABASE

Question

uselessninja 0 Junior Poster

15 Years Ago

hi everyone i have this code(dont think its a code spam ..its a trick)
which display the date(only):

<a href="index.php" STYLE="TEXT-DECORATION: NONE">&laquo;Back•</a>
<br>
<br>
<hr width="200" align="left">
<br>


<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);

if (!$conn)
  {exit("Connection Failed: " . $conn);}
  
if($search==null){

header("location:index.php");

}else{

$sql="SELECT * from generators WHERE  convert(datetime,convert(char(10),TIMESTOMPX,101))= '$search'";

};

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"resultx\"><font size=\"4\">Result for:</font>&nbsp;&nbsp;<font color=\"red\"> $search </font></div><br><hr width=\"200\" align=\"left\"><br><div class=\"rightdv\"><div class=\"topg\"><FONT SIZE=\"5\" color=\"#FFFFFF\">•• TOTAL ••</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>TOTALMW</th>";
echo "<th>TOTALMVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
  $genmw=odbc_result($rs,"TOTALMW");
   $genvar=odbc_result($rs,"TOTALMVAR");


  
  echo "<tr>";
    echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$genmw</td>";
	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$genvar</td></tr>";

	  
  }

echo "</table></div>";







?>
<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);



if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT GEN1MW,GEN1PF,GEN1MVAR FROM generators WHERE convert(datetime,convert(char(10),TIMESTOMPX,101)) = '$search'";

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"leftdv\"><div class=\"topb\"><FONT SIZE=\"5\">GENERATOR 1</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>GEN1MW</th>";
echo "<th>GEN1PF</th>";

echo "<th>GEN1MVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
    
  $gen1mw=odbc_result($rs,"GEN1MW");
  $gen1pf=odbc_result($rs,"GEN1PF");
    $gen1mvar=odbc_result($rs,"GEN1MVAR");
	
  
  echo "<tr><td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen1mw</td>";
   echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen1pf</td>";
  
	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen1mvar</td></tr></div>";


  }

echo "</table>";




?>

<br>
<br>


<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT GEN2MW,GEN2PF,GEN2MVAR FROM generators WHERE convert(datetime,convert(char(10),TIMESTOMPX,101)) = '$search'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"topc\"><FONT SIZE=\"5\">GENERATOR 2</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>GEN2MW</th>";

echo "<th>GEN2PF</th>";

echo "<th>GEN2MVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
    
  $gen2mw=odbc_result($rs,"GEN2MW");
  $gen2pf=odbc_result($rs,"GEN2PF");
    $gen2mvar=odbc_result($rs,"GEN2MVAR");
  
  echo "<tr><td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen2mw</td>";
   echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen2pf</td>";

	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen2mvar</td></tr>";

	  
  }

echo "</table>";




?>

<br>
<br>

<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT GEN3MW,GEN3PF,GEN3MVAR FROM generators WHERE convert(datetime,convert(char(10),TIMESTOMPX,101)) = '$search'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"topd\"><FONT SIZE=\"5\">GENERATOR 3</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>GEN3MW</th>";

echo "<th>GEN3PF</th>";

echo "<th>GEN3MVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
  $gen3mw=odbc_result($rs,"GEN3MW");
  $gen3pf=odbc_result($rs,"GEN3PF");
    $gen3mvar=odbc_result($rs,"GEN3MVAR");
  
  echo "<tr><td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen3mw</td>";
   echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen3pf</td>";
	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen3mvar</td></tr>";

	  
  }

echo "</table>";
?>
<br>
<br>

<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT GEN4MW,GEN4PF,GEN4MVAR FROM generators WHERE convert(datetime,convert(char(10),TIMESTOMPX,101))  = '$search'";


$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"tope\"><FONT SIZE=\"5\">GENERATOR 4</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>GEN4MW</th>";
echo "<th>GEN4PF</th>";
echo "<th>GEN4MVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
  $gen4mw=odbc_result($rs,"GEN4MW");
  $gen4pf=odbc_result($rs,"GEN4PF");
    $gen4mvar=odbc_result($rs,"GEN4MVAR");
  
  echo "<tr><td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen4mw</td>";
   echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen4pf</td>";
	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen4mvar</td></tr>";

	  
  }

echo "</table>";




?>
<br>
<br>



<?php
$conn=odbc_connect('datasourcegaby','','');

$search=mysql_escape_string($_POST['SEARCH']);

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT GEN5MW,GEN5PF,GEN5MVAR FROM generators WHERE convert(datetime,convert(char(10),TIMESTOMPX,101))= '$search'";

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<div class=\"topf\"><FONT SIZE=\"5\">GENERATOR 5</FONT></div><table border=\"1\" style=\"table-layout:fixed\" class=\"totalinsidet\"><tr class=\"topbth\">";
echo "<th>GEN5MW</th>";
echo "<th>GEN5PF</th>";
echo "<th>GEN5MVAR</th></tr>";

while (odbc_fetch_row($rs))
  {
  $gen5mw=odbc_result($rs,"GEN5MW");
  $gen5pf=odbc_result($rs,"GEN5PF");
    $gen5mvar=odbc_result($rs,"GEN5MVAR");
  
  echo "<tr><td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen5mw</td>";
   echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen5pf</td>";
	  echo "<td style=\"white-space:pre-wrap;white-space:-moz-pre-wrap;white-space:-pre-wrap;white-space:-o-pre-wrap;word-wrap:break-word\">$gen5mvar</td></tr></div>";

	  
  }

echo "</table>";




?>
<br>
<br>


<br>

<h4><a href="index.php" STYLE="TEXT-DECORATION: NONE">&laquo;Back</a></h4>

and i like to DISPLAY THE LAST DATA THAT HAS BEEN ENTERED IN THE DATABASE(MSSQL) in a specific date:

example: let say i want to display the last data entered in the database of september 1 ... like that how can i do that with my code? ..

microsoft:sql-server php

Edited 15 Years Ago by uselessninja because: n/a

3 Contributors
13 Replies
311 Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by uselessninja

Dani AI

Generated 9 Months Ago

Short answer: filter by a day-range on the actual datetime column and return the newest row for that range. Do not wrap the datetime column in functions for the WHERE clause (that prevents using an index). Validate the incoming date in PHP, build a start/end datetime for that day, then ask MSSQL for the single row with the latest timestamp on that day.

Example SQL (choose the date you want in place of '2011-09-27'):

WITH last_for_day AS (
  SELECT TOTALMW, TOTALMVAR, TIMESTOMPX,
         ROW_NUMBER() OVER (ORDER BY TIMESTOMPX DESC) AS rn
  FROM generators
  WHERE TIMESTOMPX >= '2011-09-27' 
    AND TIMESTOMPX <  DATEADD(day, 1, '2011-09-27')
)
SELECT TOTALMW, TOTALMVAR, TIMESTOMPX
FROM last_for_day
WHERE rn = 1;

Recommended PHP flow (validate the posted date, build start/end, use a parameterized ODBC statement):

$raw = trim($_POST['SEARCH']);                 // expect e.g. "09/27/2011"
$dt  = DateTime::createFromFormat('m/d/Y',$raw);
if (! $dt) { exit('invalid date'); }
$start = $dt->format('Y-m-d').' 00:00:00';
$end   = $dt->modify('+1 day')->format('Y-m-d').' 00:00:00';

$sql = 'WITH last_for_day AS (
          SELECT TOTALMW, TOTALMVAR, TIMESTOMPX,
                 ROW_NUMBER() OVER (ORDER BY TIMESTOMPX DESC) AS rn
          FROM generators
          WHERE TIMESTOMPX >= ? AND TIMESTOMPX < ?
        )
        SELECT TOTALMW, TOTALMVAR, TIMESTOMPX FROM last_for_day WHERE rn = 1';

$stmt = odbc_prepare($conn, $sql);
if ($stmt && odbc_execute($stmt, array($start,$end)) && odbc_fetch_row($stmt)) {
  $totalmw  = odbc_result($stmt,'TOTALMW');
  $totalmvar= odbc_result($stmt,'TOTALMVAR');
  echo $totalmw.' / '.$totalmvar;
}

Notes and quick tips: was right to suggest echoing and testing the SQL directly; do that in SSMS to confirm results. pointed toward picking a single row — make sure you order by the timestamp column (TIMESTOMPX) rather than ID unless ID is truly a reliable insertion-order key. , stop using MySQL escape functions with an ODBC/MSSQL connection; validate the date server-side and use prepared statements to avoid injection. Index TIMESTOMPX or use a date-range for performance.

All 13 Replies

vibhaJ 126 Master Poster

15 Years Ago

Ohhh... you have same code 6 times..Is this code by you?
for debugging echo sql query and run it directly in database and check if you are getting desired result.

echo $sql="SELECT * from generators WHERE  convert(datetime,convert(char(10),TIMESTOMPX,101))= '$search'";
exit;

vibhaJ 126 Master Poster

15 Years Ago

What about this:

$sql="SELECT TOTALMW,TOTALMVAR,ID FROM generators 
WHERE date='2011-08-11' // search with date
ORDER BY ID desc limit 0,1";

pritaeas 2,276 Most Valuable Poster

15 Years Ago

Minor correction, uselessninja says he's using mssql (which does not support limit)

$sql="SELECT TOP 1 TOTALMW, TOTALMVAR, ID 
      FROM generators 
      WHERE date='2011-08-11'
      ORDER BY ID DESC";

pritaeas 2,276 Most Valuable Poster

15 Years Ago

Use ORDER BY so the largest one will be first, then use TOP 1 to get only that row.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

uselessninja 0 Junior Poster · Answer 1 · 2011-08-30T11:04:42+00:00

Ohhh... you have same code 6 times..Is this code by you?
for debugging echo sql query and run it directly in database and check if you are getting desired result.
echo $sql="SELECT * from generators WHERE  convert(datetime,convert(char(10),TIMESTOMPX,101))= '$search'";
exit;

yeah its mine ...i know its kinda messy but i think its a good trick... no one cares on how do i code anyway.....

my code is fine...i dont need to debug it.. i just want to retrieve the last data entered in the mssql database and i dont know how to do it..

uselessninja 0 Junior Poster · Answer 2 · 2011-08-30T13:19:26+00:00

Ohhh... you have same code 6 times..Is this code by you?
for debugging echo sql query and run it directly in database and check if you are getting desired result.
echo $sql="SELECT * from generators WHERE  convert(datetime,convert(char(10),TIMESTOMPX,101))= '$search'";
exit;

hey i found one but not this one.. it displays the last row of table... but i want to "display the last data entered in a specific date"

heres the code btw:
$sql="SELECT TOTALMW,TOTALMVAR,ID FROM generators WHERE ID=(select max(ID) from generators)";

uselessninja 0 Junior Poster · Answer 3 · 2011-08-30T15:07:32+00:00

What about this:

$sql="SELECT TOTALMW,TOTALMVAR,ID FROM generators 
WHERE date='2011-08-11' // search with date
ORDER BY ID desc limit 0,1";

OPSS ... "LIMIT in query is not available in MSSQL" :)

uselessninja 0 Junior Poster · Answer 4 · 2011-08-30T15:08:22+00:00

Minor correction, uselessninja says he's using mssql (which does not support limit)
$sql="SELECT TOP 1 TOTALMW, TOTALMVAR, ID 
      FROM generators 
      WHERE date='2011-08-11'
      ORDER BY ID DESC";

ill try this..i hope it works

uselessninja 0 Junior Poster · Answer 5 · 2011-08-31T05:54:51+00:00

Minor correction, uselessninja says he's using mssql (which does not support limit)
$sql="SELECT TOP 1 TOTALMW, TOTALMVAR, ID 
      FROM generators 
      WHERE date='2011-08-11'
      ORDER BY ID DESC";

uhmm..little bit confuse about ur code.. it only display the first row of table right? .. if does so i think its not the right one...

btw ... i was wrong about this code..

$sql="SELECT TOTALMW,TOTALMVAR,ID FROM generators WHERE ID=(select max(ID) from generators)";

> It displays the BIGGEST number of the row NOT the last row of the table .......I think it is not always the biggest number will be on the last row coz i change the position of 15 and 14 (the 14 is now on the last row) but it displays the 15! so i think this code is not appropriate.. can u give the appropriate one?

uselessninja 0 Junior Poster · Answer 6 · 2011-09-01T10:01:45+00:00

Use ORDER BY so the largest one will be first, then use TOP 1 to get only that row.

yeah thats what i said ... the biggest row is not always the last row ..... i want to display the last data entered in a specific day... lets say i want to display the last data entered as of 9/27/2011 ...

vibhaJ 126 Master Poster · Answer 7 · 2011-09-01T12:02:54+00:00

Hope ID is auto increamented and primary field.
When you insert new record then its id is greater than prev.
thus if you search all results with one date and see MAX(ID), it returns last inserted row for that date.

pritaeas 2,276 Most Valuable Poster Moderator Featured Poster · Answer 8 · 2011-09-01T14:01:22+00:00

pritaeas 2,276 Most Valuable Poster

15 Years Ago

Add the date to your WHERE clause: WHERE [yourdate] = '2011-09-27'

uselessninja 0 Junior Poster · Answer 9 · 2011-09-02T07:22:43+00:00

Use ORDER BY so the largest one will be first, then use TOP 1 to get only that row.

i know this is not the appropriate one BUT i use this one now in my form hehe ...thankzz sir..i appreciate ur help.. :D

how to retrieve and display the last data entered in MSSQL DATABASE

Dani AI

Recommended Answers Collapse Answers

All 13 Replies

Recommended Answers