MYSQL Scroll LIST IN PHP

The programming code will list 12 records at a time from a data table contained within a MySQL database. It can scroll forwards and backwards by the same number of rows by clicking the labeled buttons above the list, “Next” and “Previous”. This is a simple to use utility and it works in a clean and seamless manner.

In this patch of code, you can see how the data table row counter is advanced forward by 12 rows in the code enclosed in the ifthen data structure beginning with “if ($HTTP_POST_VARS['submit_page_forward']) “. And it will go backwards by the same number in the programming under the ifthen data structure starting with “if ($HTTP_POST_VARS['submit_page_backward']) “. The PHP session variable, “$_SESSION[start_row]“, holds the first of the next 12 data records to be displayed in the scroll list. “$_SESSION[totalrecords]” is another session variable that contains the total number of records in the data table that is used to populate the scroll list. It is used to detect the end of the scroll list used with the forward scrolling function mentioned before.

<?php

session_start();  
// include the file containing log in constants.
include 'generic_config.php';

// redirect to home page.
if ($_POST['submit_return_to']) 
{
header("Location: http://www.generic.com/index.html");  
}

// redirect to comment posting page.
if ($_POST['submit_add_feedback']) 
{
header("Location: http://www.generic.com/submit_sheet_customer_feedback.php");  
}

// scroll forward 12 rows.
if ($HTTP_POST_VARS['submit_page_forward']) 
{
$_SESSION[start_row] = $_SESSION[start_row] + 12;
if ( $_SESSION[start_row] > $_SESSION[totalrecords] ) {
$_SESSION[start_row] = $_SESSION[start_row] - 12;
}
}

// scroll backward 12 rows.
if ($HTTP_POST_VARS['submit_page_backward']) 
{
$_SESSION[start_row] = $_SESSION[start_row] - 12;
if ( $_SESSION[start_row] < 0 ) {
$_SESSION[start_row] = 0;
}
}

?>

The display of the scroll list consists of a PHP script enclosed in a HTML form. When the site visitor clicks on the “Next” or “Previous” <input> element buttons, the form will send the selection to the server. It will then be handled by the code in the previous section of this article.

At the beginning of the PHP code within the HTML form (see below), a connection is made to the website’s server and the MySQL database is set. The predefined constants for the server and database connection can be found in the configuration file specified near the beginning of this web page, include 'generic_config.php';.

All the records in the customer_ feedbackdata table are queried by the PHP directive, $result=mysql_query("SELECT * FROM customer_feedback“) or die('Could not select table');. The total number of records in the data table is retrieved by another PHP directive, $total_records = mysql_num_rows($result);. Then that value is assigned to a session variable used by the forward scrolling function, $_SESSION[totalrecords] = $total_records;.

The contents of the “remarks” field from the data table is assigned to a PHP variable like this, $remark = mysql_result($result, $num, "remarks");. Then the variable is displayed on the scroll list like this, echo nl2br($remark);. This is done within a for loop data structure that repeats 12 times for each row to be displayed.

Also, note how the HTML is encapsulated within the PHP scripting language. The idea is to “weave” a HTML table into the fabric of PHP during the display of each of the 12 rows. This “weaving” technique is also applied to the PHP ifendif data structure that is used to enable and disable the navigation buttons located just above the row listing. I’m stuck on PHP coding!

Lastly, the queried resource is freed like this, mysql_free_result($result);. Then the server connection is closed like so, mysql_close($conn);.

<form action="<?=$PHP_SELF?>" method="post" enctype="multipart/form-data">

<?php

// make the sql connection then select database using constants from the 
// included configuration php file.
$conn = mysql_connect(DB_HOSTX, DB_USERX, DB_PASSWORDX) or die('Could not connect: ' . mysql_error());
$db_selected = mysql_select_db(DB_NAMEX, $conn) or die('Could not select database');   

// query the customer feedback records.
$result=mysql_query("SELECT * FROM customer_feedback") or die('Could not select table'); 
// the number of records in your result set; assign to the php session
// variable, $_SESSION[totalrecords].
$total_records = mysql_num_rows($result); 
$_SESSION[totalrecords] = $total_records;

// assign the current starting row to a row variable, '$num'.
$num = $_SESSION[start_row];

// encapsulate html with php code to display buttons for redirection
// operations as well as forward and backward scrolling.
echo "<p>";

echo "<input type=submit name=submit_return_to value='Home'>";
echo "<input type=submit name=submit_add_feedback value='Add Customer Feedback'>";

if ( $_SESSION[start_row] + 11 >= $_SESSION[totalrecords] || $_SESSION[totalrecords] < 11 ) {
echo "<input type=submit name=submit_page_forward value=Next disabled>";
} else {
echo "<input type=submit name=submit_page_forward value=Next>";
}
if ( $_SESSION[start_row] == 0 ) {
echo "<input type=submit name=submit_page_backward value=Previous disabled>";
} else {
echo "<input type=submit name=submit_page_backward value=Previous>";
}

echo "</p>";

// place displayed results in a html table.
echo "<table border='3' cellpadding='10' cellspacing='10'>";

// next, run the "for loop" to display the current 12 rows.
for ($rows = 0; $rows < 12; $rows++) {

if ($num < $total_records) {

// assign the customer feedback to a row variable from the current table record.
$remark = mysql_result($result, $num, "remarks");

echo "<tr>";    
echo "<td>";    
// display the customer service feedback remark.
echo nl2br($remark);
echo "</td>";    
echo "</tr>";    

}

// increment the row variable by one for the next table record.
$num++; 

}

echo "</table>";

// free the resource, '$result'.
mysql_free_result($result);
// close the server connection, '$conn'.
mysql_close($conn);

?>

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.