Jump to content

dreamweaver: how to make "sort by" column header links?

saw

By saw
in Editor Help (PhpStorm, VS Code, etc)

 Share

Recommended Posts

saw Newbie

saw

Posted
Posted

I am using dreamweaver.

I have a dynamic table, which just displays the data in a mysql table, along with pagingation.

 

I would like to learn how to make the column names be links to sort the table.

 

Any advice or help with this would be much appreciated.

I have tried googling for a tutorial but havent had much luck.

 

Saw

Link to comment
Share on other sites
  • 3 weeks later...
mreish Newbie

mreish

Posted
Posted

I just posted a question in the MyQSL forum so I'd better keep the karma coming by helping someone else. Ha ha ha.

 

The idea is that the column names are links with urls like "<?php $_SERVER['PHP_SELF'] ?>?filter=transactionDate&dir=ASC". Up in your code (before HTML) you check to see if 'filter' and 'direction' have been set.

 

if (isset($_GET['filter'])) {
$filter = sanitize($_GET['filter']);
} else {
$filter = "transactionDate";
}

if (isset($_GET['dir'])) {
$direction = sanitize($_GET['dir']);
} else {
$direction = "DESC"; 
}

 

Sanitize is a function I made that, well, sanitizes the input.  You are filtering or sanitizing your input, right?  :)

 

Then we toss that into the query:

 

"SELECT * FROM transactions ORDER BY $filter $direction"

 

Does this help?

 

 

 

 

 

 

I trimmed out the stuff that's not pertinent to your question and hope I didn't trim out anything important.

 

 

<?php require_once('../../Connections/db.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

if (isset($_GET['filter'])) {
$filter = sanitize($_GET['filter']);
} else {
$filter = "transactionDate";
}

if (isset($_GET['dir'])) {
$direction = sanitize($_GET['dir']);
} else {
$direction = "DESC"; 
}

$maxRows_transactions = 20;
$pageNum_transactions = 0;
if (isset($_GET['pageNum_transactions'])) {
  $pageNum_transactions = $_GET['pageNum_transactions'];
}
$startRow_transactions = $pageNum_transactions * $maxRows_transactions;

mysql_select_db($database_db, $db);
if (isset($_POST['search'])) {
$searchString = sanitize($_POST['searchString']);
$query_transactions = "SELECT * FROM transactions WHERE transactionReasonText LIKE '%$searchString%' "
."OR transactionIP LIKE '%$searchString%' "
."OR transactionIDAuthorizeNet LIKE '%$searchString%' "
."OR transactionApprovalCode LIKE '%$searchString%' "
."OR transactionZip LIKE '%$searchString%' "
."OR transactionPhoneNumber LIKE '%$searchString%' "
."OR transactionState LIKE '%$searchString%' "
."OR transactionCity LIKE '%$searchString%' "
."OR transactionAddress LIKE '%$searchString%' "
."OR transactionEmail LIKE '%$searchString%' "
."OR transactionLastName LIKE '%$searchString%' "
."OR transactionFirstName LIKE '%$searchString%' "
."OR transactionTime LIKE '%$searchString%' "
."OR transactionDate LIKE '%$searchString%' "
."OR transactionCardExperation LIKE '%$searchString%' "
."OR transactionCCV LIKE '%$searchString%' "
."OR transactionCardFirst4 LIKE '%$searchString%' "
."OR transactionCardLast4 LIKE '%$searchString%' "
."OR transactionOrderID LIKE '%$searchString%' "
."OR transactionAmount LIKE '%$searchString%' "
."OR cardTypeID LIKE '%$searchString%' "
."OR statusTypeID LIKE '%$searchString%' "
."OR walkerID LIKE '%$searchString%' "
."OR transactionTypeID LIKE '%$searchString%' "
."OR transactionIP LIKE '%$searchString%' "
."ORDER BY $filter $direction";
} else {
$query_transactions = "SELECT * FROM transactions ORDER BY $filter $direction";
}
$query_limit_transactions = sprintf("%s LIMIT %d, %d", $query_transactions, $startRow_transactions, $maxRows_transactions);
$transactions = mysql_query($query_limit_transactions, $db) or die(mysql_error());
$row_transactions = mysql_fetch_assoc($transactions);

if (isset($_GET['totalRows_transactions'])) {
  $totalRows_transactions = $_GET['totalRows_transactions'];
} else {
  $all_transactions = mysql_query($query_transactions);
  $totalRows_transactions = mysql_num_rows($all_transactions);
}
$totalPages_transactions = ceil($totalRows_transactions/$maxRows_transactions)-1;



session_start(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="Robots" content="NOINDEX, NOFOLLOW" />
<title>: : <?php echo $row_siteWideSettings['siteName']; ?> - Administration ::</title>

</head>

<body>
     
     <h2>Reports - Overview</h2>

     <?php include('../includes/reportsMenu.php'); ?>

     <form id="genericSearchForm2" name="genericSearchForm" action="<?php $_SERVER['PHP_SELF'] ?>" method="post">
          <input type="text" name="searchString" id="searchString" /><input class="submitButton" type="submit" name="Submit" value="Find" />
          <input type="hidden" value="search" name="search" />
     </form>
     <p> </p>
     <?php //echo $query_limit_transactions.'<br>'.$filter.'<br>'.$direction.'<br>'.$searchString; ?>

     <table border="0" cellpadding="10" cellspacing="0">
          <tr>
               <td width="20"> </td>
               <th>Date
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionDate&dir=ASC"><img src="../images/arrow_up.png" width="11" height="11" /></a>
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionDate&dir=DESC"><img src="../images/arrow_down.png" width="11" height="11" /></a>
               </th>
               <td width="20"> </td>
               <th>
                    Type
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionTypeID&dir=ASC"><img src="../images/arrow_up.png" width="11" height="11" /></a>
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionTypeID&dir=DESC"><img src="../images/arrow_down.png" width="11" height="11" /></a>
               </th>
               <td width="20"> </td>
               <th>
                    Card
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=cardTypeID&dir=ASC"><img src="../images/arrow_up.png" width="11" height="11" /></a>
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=cardTypeID&dir=DESC"><img src="../images/arrow_down.png" width="11" height="11" /></a>
               </th>
               <td width="20"> </td>
               <th>
                    Aprov
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=statusTypeID&dir=ASC"><img src="../images/arrow_up.png" width="11" height="11" /></a>
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=statusTypeID&dir=DESC"><img src="../images/arrow_down.png" width="11" height="11" /></a>
               </th>
               <td width="20"> </td>
               <th>
                    Amount
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionAmount&dir=ASC"><img src="../images/arrow_up.png" width="11" height="11" /></a>
                    <a href="<?php $_SERVER['PHP_SELF'] ?>?filter=transactionAmount&dir=DESC"><img src="../images/arrow_down.png" width="11" height="11" /></a>
               </th>
          </tr>
          <?php do { 
          
               $query_transactionCardTypes = sprintf("SELECT cardTypeDesc FROM transactionCardTypes WHERE cardTypeID = %s", GetSQLValueString($row_transactions['cardTypeID'], "int"));
               $transactionCardTypes = mysql_query($query_transactionCardTypes, $db) or die(mysql_error());
               $row_transactionCardTypes = mysql_fetch_assoc($transactionCardTypes);
                                   
               $query_transactionStatusTypes = sprintf("SELECT statusTypeDesc FROM transactionStatusTypes WHERE statusTypeID = %s", GetSQLValueString($row_transactions['statusTypeID'], "int"));
               $transactionStatusTypes = mysql_query($query_transactionStatusTypes, $db) or die(mysql_error());
               $row_transactionStatusTypes = mysql_fetch_assoc($transactionStatusTypes);

               $query_transactionTypes = sprintf("SELECT transactionTypeDesc FROM transactionTypes WHERE transactionTypeID = %s", GetSQLValueString($row_transactions['transactionTypeID'], "int"));
               $transactionTypes = mysql_query($query_transactionTypes, $db) or die(mysql_error());
               $row_transactionTypes = mysql_fetch_assoc($transactionTypes);
          ?>
          <tr class="zebra">
               <td><a href="viewTransactionDetails.php?transactionID=<?php echo $row_transactions['transactionID']; ?>"><img src="../images/icons/zoom.png" width="16" height="16" alt="View" title="View details of this transaction" /></a></td>
               <td><?php echo $row_transactions['transactionDate']; ?></td>
               <td>  </td>
               <td><?php echo $row_transactionTypes['transactionTypeDesc']; ?></td>
               <td>  </td>
               <td><?php echo $row_transactionCardTypes['cardTypeDesc']; ?></td>
               <td>  </td>
               <td><?php echo $row_transactionStatusTypes['statusTypeDesc'];  
                    if ($row_transactions['statusTypeID'] != 1) {
                    echo ' <img src="../images/icons/help_gray.png" width="16" height="16" onmouseover="Tip(\''.$row_transactions['transactionReasonText'].'\')" />';
                    }
                ?></td>
               <td>  </td>
               <td align="right"><?php echo $row_transactions['transactionAmount']; ?></td>
          </tr>
          <?php } while ($row_transactions = mysql_fetch_assoc($transactions)); ?>
          <tr>
               <td colspan="10"> </td>
          </tr>
          <tr>
               <td colspan="3">  </td>
               <td colspan="7">Page <?php echo ($pageNum_transactions + 1) ?> of <?php echo ($totalPages_transactions + 1) ?>.</td>
          </tr>
          <tr>
               <td> </td>
               <td class="subNavigationAdmin" align="center" colspan="9">
                    <a class="button" href="<?php printf("%s?pageNum_transactions=%d%s", $currentPage, 0, $queryString_transactions); ?>"><img src="../images/icons/resultset_first.png" /> First</a>
                    <a class="button" href="<?php printf("%s?pageNum_transactions=%d%s", $currentPage, max(0, $pageNum_transactions - 1), $queryString_transactions); ?>"><img src="../images/icons/resultset_previous.png" /> Prev</a>
                    <a class="button" href="<?php printf("%s?pageNum_transactions=%d%s", $currentPage, min($totalPages_transactions, $pageNum_transactions + 1), $queryString_transactions); ?>">Next <img src="../images/icons/resultset_next.png" /></a>
                    <a class="button" href="<?php printf("%s?pageNum_transactions=%d%s", $currentPage, $totalPages_transactions, $queryString_transactions); ?>">Last <img src="../images/icons/resultset_last.png" /></a>
               </td>
          </tr>
     </table>

</div>
</body>
</html>
<?php
mysql_free_result($transactions);
mysql_free_result($transactionTypes);
mysql_free_result($transactionStatusTypes);
mysql_free_result($transactionCardTypes);
mysql_free_result($siteWideSettings);
?>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.