I am trying to pull out the whole column for row[3] in the following script. Everything works, but unfortunately, the code is only pulling out the last row's data from the whole page while everything else is lost. I have never converted a whole array from datetime to string, and I am unsure of what I'm doing wrong. I would appreciate any help with this:
<?php ini_set('display_errors', 1); error_reporting(E_ALL); ?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript"
src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery- ui.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
rel="stylesheet" type="text/css"/>
</head>
<body>
<table align="center">
<form id="form1" name="form1" method="get" action="test.php">
<td><label for="from">From</label>
<input name="from" type="text" id="from" size="10" name='from' value="" /></td>
<td><label for="from">To</label>
<input name="to" type="text" id="to" size="10" name='to' value="" /></td>
</select>
<td><input type="submit" id="button" value="Filter" /></td>
</label>
<td> <a href="test.php">
reset</a>
</td>
</table>
</form>
<script>
$(function() {
var dates = $( "#from, #to" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
numberOfMonths: 2,
dateFormat: 'yy-mm-dd',
onSelect: function( selectedDate ) {
var option = this.id == "from" ? "minDate" : "maxDate",
instance = $( this ).data( "datepicker" ),
date = $.datepicker.parseDate(
instance.settings.dateFormat ||
$.datepicker._defaults.dateFormat,
selectedDate, instance.settings );
dates.not( this ).datepicker( "option", option, date );
}
});
});
</script>
<?php
function getPage($stmt, $pageNum, $rowsPerPage)
{
$offset = ($pageNum - 1) * $rowsPerPage;
$rows = array();
$i = 0;
while(($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC, SQLSRV_SCROLL_ABSOLUTE,
$offset + $i)) && $i < $rowsPerPage)
{
array_push($rows, $row);
$i++;
}
return $rows;
}
// Set the number of rows to be returned on a page.
$rowsPerPage = 30;
// Connect to the server.
$serverName = 'test';
$connOptions = array("Database"=>"test");
$conn = sqlsrv_connect($serverName, $connOptions);
if (!$conn)
die( print_r( sqlsrv_errors(), true));
// Define and execute the query.
// Note that the query is executed with a "scrollable" cursor.
$date1 = $_GET['from'];
$date2 = $_GET['to'];
$sql = "SELECT * from test WHERE
(([testtime]
>= '".$date1."') AND ([testtime] <= '".$date2."')) ";
$stmt = sqlsrv_query($conn, $sql, array(), array( "Scrollable" => 'static' ));
if ( !$stmt )
die( print_r( sqlsrv_errors(), true));
// Display the selected page of data.
echo "<table border='1px' align='center' width = '480'>";
$pageNum = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$page = getPage($stmt, $pageNum, $rowsPerPage);
foreach($page as $row)
$result[3] = $row[3]->format('Y-m-d H:i:s');
echo "<tr><td width='65'>$row[0]</td><td width='65'>$row[1]</td><td width='65'>$row[2]
</td><td width='65'>$result[3]</td></tr>";
echo "</table>";
?>
</table>
<table align='center'>
<?php
// Get the total number of rows returned by the query.
// Display links to "pages" of rows.
$rowsReturned = sqlsrv_num_rows($stmt);
if($rowsReturned === false)
die( print_r( sqlsrv_errors(), true));
elseif($rowsReturned == 0)
{
echo "No rows returned.";
exit();
}
else
{
// Display page links.
$numOfPages = ceil($rowsReturned/$rowsPerPage);
$renderedEllipses = false;
for($i = 1; $i<=$numOfPages; $i++)
{
//If you always want to render pages 1 - 3
if($i < 4) {
//render link
$pageLink = "?pageNum=$i&from=$date1&to=$date2";
print("<a href=$pageLink>$i</a> ");
}
//If you always want to render current page number
else if($i == $pageNum) {
//render link
$pageLink = "?pageNum=$i&from=$date1&to=$date2";
print("<a href=$pageLink>$i</a> ");
//reset ellipses
$renderedEllipses = false;
}
//if you always want the last page number
else if(abs($i - $pageNum) == 1) {
$pageLink = "?pageNum=$i&from=$date1&to=$date2";
print("<a href=$pageLink>$i</a> ");
}
//if you want the middle number
else if ($i == round($numOfPages / 2) ) {
$pageLink = "?pageNum=$i&from=$date1&to=$date2";
print("<a href=$pageLink>$i</a> ");
//reset ellipses
$renderedEllipses = false;
}
//if you always want the last page number
else if ($i > $numOfPages - 3 ) {
//render link
$pageLink = "?pageNum=$i&from=$date1&to=$date2";
print("<a href=$pageLink>$i</a> ");
}
//make sure you only do this once per ellipses group
else {
if (!$renderedEllipses){
print("...");
$renderedEllipses = true;
}
}
}}
?>
</table>
<table align="center">
<td>PAGE <?php echo $pageNum; ?> of <?php echo $numOfPages; ?> (<?php echo
$rowsReturned;>
items)
<?php // Display Previous Page link if applicable.
if($pageNum > 1)
{
$prevPageLink = "?pageNum=".($pageNum - 1)."&from=$date1&to=$date2";
echo "<a href='$prevPageLink'>Prev</a>";
}
// Display Next Page link if applicable.
if($pageNum < $numOfPages)
{
$nextPageLink = "?pageNum=".($pageNum + 1)."&from=$date1&to=$date2";
echo " <a href='$nextPageLink'>Next</a>";
}
sqlsrv_close( $conn );
?>
</br>
</body>
</html>
If there's any other info I can provide, please let me know.
@JohnMark13 - I am including you as you've helped a lot with the previous answer. Thanks for directing me to the correct area to post. :)
