A-Z Navigation of recordset

Forum Moderators: coopster

Message Too Old, No Replies

A-Z Navigation of recordset

imagopher

10:25 am on Jul 3, 2010 (gmt 0)

Hello everyone, I am wondering if someone can help me.

I'm fairly new to coding php. I've got data that is retrieved into a table using php thats all fine I have it in a repeat region for all records. What I want to do though is have an alphabet list at the top of the page to allow users to click on a letter say "B" and that sort through all data in the recordset for the Authors last name to start with "B". and if they click show all it reverts back to showing all records as normal?

Is this possible and if so any ideas on how would be greatly appreciated.

Thank you

Readie

12:37 pm on Jul 3, 2010 (gmt 0)

Welcome to Webmaster World Imagopher

I'll assume you are using a MySQL database to store your data, but please correct me if I'm wrong.

Ok, so for the links I think something like this:

$letters = range('A', 'Z');
$links = '';
// Loop through the array of letters
for($i = 0; $i < 26; $i++) {
  $spacer = ($i == 0)? '' : '&nbsp;|&nbsp;';
  $links .= $spacer . '<a href="/mypage.php?filter=' . $letters[$i] . '>' . $letters[$i] . '</a>';
}
echo $links;

Now, using this in the database query is fairly simple too:

if(isset($_GET['filter'])) {
  $where = ' WHERE author_name LIKE "' . mysql_real_escape_string($_GET['filter']) . '%"';
} else {
  $where = '';
}
$sql = 'SELECT * FROM authors_table' . $where . ' ORDER BY author_name ASC';
$result = mysql_query($sql);
// etc...

imagopher

2:17 pm on Jul 3, 2010 (gmt 0)

Many thanks for your response. I have just tried the code you have given me & can't seem to get it to work. I can get letters to show up but it doesn't show a - z only shows b,d,f,h,j,l,n,p,r,t,v,x,z it also when clicked doesn't sort through and only show the relevant outcome. This is my code, I am using dreamweaver CS3 and yes you were right I am using Mysql database. The name of the field i want to sort by is familyname (the authors surname) I wonder if you can tell me what I am doing wrong? have I put the code in the right places?

Thanks again!

<?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;
}
}

mysql_select_db($database_stockholm2010, $stockholm2010);
$query_papers = "SELECT * FROM papers";
$papers = mysql_query($query_papers, $stockholm2010) or die(mysql_error());
$row_papers = mysql_fetch_assoc($papers);
$totalRows_papers = mysql_num_rows($papers);




if(isset($_GET['filter'])) {
 $where = ' WHERE familyname LIKE "' . mysql_real_escape_string($_GET['filter']) . '%"';
} else {
 $where = '';
}
$sql = 'SELECT * FROM familyname' . $where . ' ORDER BY familyname ASC';
$result = mysql_query($sql);

?>
<head>
</head>

<body>
<p><?php $letters = range('A', 'Z');
$links = '';
// Loop through the array of letters
for($i = 0; $i < 26; $i++) {
 $spacer = ($i == 0)? '' : '&nbsp;|&nbsp;';
 $links .= $spacer . '<a href="sort.php?filter=' . $letters[$i] . '>' . $letters[$i] . '</a>';
}
echo $links;?> </p>
<div>
 <table class="tableResultsHeader"width="692" id-="id-""myTable">
 <thead>
  <tr>
  <th width="244" class="rightborder" ><h3 align="left">Paper title </h3></th>
  <th width="206" class="rightborder" ><h3 align="left">Corresponding Author</h3></th>
  <th width="227" class="bottom" ><h3 align="left">Institution</h3></th>
  </tr>
 </thead>
 <?php do { ?>
 <tbody>
  <tr>
  <td width="244" valign="middle" class="tableResults" ><h4><a href="uploads/<?php echo $row_results['file'];?>"><?php echo $row_results['papertitle']; ?></a><br />
  </h4></td>
  <td width="206" class="tableResults"><h4><?php echo $row_results['firstname']; ?> <?php echo $row_results['familyname']; ?><br />
  </h4></td>
  <td width="227" class="tableResultsBottom"><h4><?php echo $row_results['institution']; ?><br />
  </h4></td>
  </tr>
 </tbody>
 <?php } while ($row_papers = mysql_fetch_assoc($papers)); ?>
 </table>
</div>
</body>
</html>
<?php
mysql_free_result($papers);
?>

Readie

2:49 pm on Jul 3, 2010 (gmt 0)

The letters not showing up was a typo by me:

$links .= $spacer . '<a href="/mypage.php?filter=' . $letters[$i] . '>' . $letters[$i] . '</a>';
$links .= $spacer . '<a href="/mypage.php?filter=' . $letters[$i] . '">' . $letters[$i] . '</a>';

See the missing quotation on the top 1? :)

And I'm afraid I'm a bit unwilling to read through all of your code dude - reading someone else's code and following the logic is a time consuming business.

If you post only the relevant snippets, with maybe a short explanation as to what it should be doing I'll be happy to try and help though.

imagopher

3:12 pm on Jul 3, 2010 (gmt 0)

Please ignore last post. I've managed to get it to work a slightly different way. Thanks again for your help!

imagopher

3:24 pm on Jul 3, 2010 (gmt 0)

Thats great thanks. Do you know of a way to only echo the letters that are relevant to data in the db? i.e if no names begin with "C" then c doesn't create a link?

Readie

5:30 pm on Jul 3, 2010 (gmt 0)

The method that immediatley leaps to mind...

$letters = range('A', 'Z');
$letters_real = array();
for($i = 0; $i < 26; $i++) {
  $sql = 'SELECT id FROM authors_table WHERE author_name LIKE "' . $letters[$i] . '%" LIMIT 1';
  $result = mysql_query($sql);
  if(mysql_fetch_row($result)) {
    $letters_real[] = $letters[$i];
  }
}

$links = '';
$count = count($letters_real);
for($i = 0; $i < $count; $i++) {
  $spacer = ($i == 0)? '' : '&nbsp;|&nbsp;';
  $links .= $spacer . '<a href="/mypage.php?filter=' . $letters_real[$i] . '">' . $letters_real[$i] . '</a>';
}

With this system, I strongly suggest you set up an index on your author_name column, and limit it to the first character of the author name. Will greatly speed up this script.