Recordset paging by A,B,C....instead of Next Page, Prev Page, etc.?

[gbergan52]

Hello geniuses,

I was wondering if there is a way to do the following:

I currently have a member database (MySQL) that I manage for a client. Using DW CS4, I used the built-in Recordset paging to add a navigation bar to the top of the member listing, limiting it to 50 per page. Since there are so many members in this organization, my client has asked me to create an A,B,C...navigation bar, rather than First Page - Next Page - Prev Page - Last Page. Is there a way to do this? Note that if a member is added or dropped from the list and they happen to be the first of the B's (for example), then the marker has to move accordingly. Thanks for pointing me in the right direction.

Regards,

Gail

[Guest]

By A,B,C... do you mean that clicking on A will only show memebers whose name starts with A or do you mean like 1,2,3...10 etc where clicking on 1 will show the first 50 people that were entered into the DB? Please specify.

You can find useful information by searching for:

(your unmentioned server side scripting language) pagination

[gbergan52]

I mean that clicking on A will only display those whose last names

start with A. Sorry for any confusion.

Gail

[Guest]

Your scripting language is still unmentioned.

On list page create links to yourwebsite.com/results?surname=a for a yourwebsite.com/results?surname=b for b etc.

In database create a column for surname and enter the first letter of the last name or use a function to retrieve the first letter of the last name and then create a variable out of that. Unfortunately vague solutions are provided when the server side scripting language is not provided by the poster.

On index page for results create a filtered recordset where URL parameter surname = the variable for the first letter of surname or the value of the surname databse column. Loop the results of the recordset on the results page.

[gbergan52]

Sorry, I'm using PHP. Does that help?

Gail

[ultrasuite]

Gail,

You need four things:

1.  Your links should have a URL parameter like this:  ?id=a

2.  Retrieve the value of "id" like" $_SERVER["id"[;

3.  Create a variable:  $LETTER = $_SERVER["id"]

4.  Insert variable in your SQL statement to filter the recordset:  SELECT * FROM table WHERE field LIKE '$LETTER%';

And if this helps solve your problem, do post back to let us know or post what worked for you.

J.S.
UltraSuite Extensions

[gbergan52]

I will let you know. Thanks for all the helpful suggestions. It will be Wed or Thurs before I can get back on this, but I will

post back when I have a solution that works.

Gail

[Günter_Schenk]

So here is a working "filter lastname by initial letter " solution which will can be nicely integrated into Dreamweaver´s PHP Recordset Paging and will (of course) not affect the number of recordsets which are displayed on first page load.

This time I´m not going to provide some ready-made code for everyone to copy & paste -- nope, this time I´ll be an unkind chap and attach several screenshots of fthe code (step1, step 2, step 3) in order to make interested readers reproduce the steps the hard way, as every serious coder should

Have fun !

Günter

[gbergan52]

I think I'm following it. In the div class "letters", I just reproduce

all the lines down to letter Z, correct?

Thanks so very much for a great response. I'm going to try to

incorporate this into my page next week and I'll let you know how it

goes! I'm excited to try it.

Gail

Message was edited by: Günter Schenk. Reason: Contact details removed at the request of the OP

[Günter_Schenk]

gbergan52 wrote:

I think I'm following it. In the div class "letters", I just reproduce

all the lines down to letter Z, correct?

Bingo !

[gbergan52]

Gunter,

Finally getting back to this; sorry for the delay. I'm thinking I have some of the code in the wrong place, as it's not displaying correctly. Here is my salient code:

------------------

// dynamic where clause: letter

if(!empty($_GET['letter']) && ctype_alpha($_GET['letter']))
{
    $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";
}
else {
    $whereclause = "";
}

//END

$maxRows_listUsers = 50;
$pageNum_listUsers = 0;
if (isset($_GET['pageNum_listUsers'])) {
  $pageNum_listUsers = $_GET['pageNum_listUsers'];
}
$startRow_listUsers = $pageNum_listUsers * $maxRows_listUsers;

mysql_select_db($database_GCSMembership, $GCSMembership);
$query_listUsers = "SELECT user_id, lastname, firstname, email FROM users WHERE activity_status = 'y' ORDER BY lastname ASC";
$query_limit_listUsers = sprintf("%s LIMIT %d, %d", $query_listUsers, $startRow_listUsers, $maxRows_listUsers);
$listUsers = mysql_query($query_limit_listUsers, $GCSMembership) or die(mysql_error());
$row_listUsers = mysql_fetch_assoc($listUsers);

if (isset($_GET['totalRows_listUsers'])) {
  $totalRows_listUsers = $_GET['totalRows_listUsers'];
} else {
  $all_listUsers = mysql_query($query_listUsers);
  $totalRows_listUsers = mysql_num_rows($all_listUsers);
}
$totalPages_listUsers = ceil($totalRows_listUsers/$maxRows_listUsers)-1;

---------------

You stated to put the dynamic whereclause 'above' my document's main recordset. I think I've done this (I put it above my $query_listUsers statement). Should it be incorporated into the query itself instead? Also I'm not sure, if it's in the correct position, where I incorporate my existing whereclause  (WHERE activity_status = 'y'). Is it in this statement: else { $whereclause = "";  In other words should it be AND { $whereclause = activity_status = 'y'; ? AND is the critical delimiter I believe. This is the most important filter as it filters out lapsed members, etc. Another question: do I need the code  ORDER BY lastname ASC"? I'm assuming I still need to keep this, as I want, within each letter, for them to be in alphabetical order.

Another question: do I get rid of the $query_limit_listUsers statement? Because as of now, the page is still just displaying the first 50 members.

The easy part was creating the links for A,B,C. These are displaying correctly except they are run together (no space separating each letter, and I believe this means the links aren't separated either. However I can't test this until I get the A,B,C segregated display of member names working properly.

Thanks for all your help and patience,

Gail

[Günter_Schenk]

Hi Gail,

when taking a look at step2.jpg you´ll see that WHERE activity_status = 'y' is a hard-coded component of the existing query and is by no means mentioned in the code you´ll see in step1.jpg.

Did you try adding the $whereclause (maybe I should have named it "andclause" instead) variable after the existing WHERE activity_status = 'y', as displayed in step2 as well ?

Another question: do I need the code  ORDER BY lastname ASC"? I'm assuming I still need to keep this, as I want, within each letter, for them to be in alphabetical order.

That´s correct.

Another question: do I get rid of the $query_limit_listUsers statement?

Not on any account.

[gbergan52]

Gunter,

If I do this instead:

------------

$query_listUsers = "SELECT user_id, lastname, firstname, email FROM users WHERE activity_status = 'y' ORDER BY lastname ASC";

if(!empty($_GET['letter']) && ctype_alpha($_GET['letter']))
{
    $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";
}
else {
    $whereclause = "";
};

------------

I get the same result: the first 50 members are displayed regardless of lastname letter. There is something wrong with my coding but I don't know what it is.

Also, if I click on a letter, say D, I get the following in the browser:

Notice:  Use of undefined constant letter - assumed 'letter' in C:\wamp\www\2010 new site\list_users_abc_test.php on line 57

Line 57 has the following code: $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";

Thanks,

Gail

[gbergan52]

and...if I put it here:

$query_listUsers = "SELECT user_id, lastname, firstname, email FROM users WHERE activity_status = 'y' if(!empty($_GET['letter']) && ctype_alpha($_GET['letter']))
{
    $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";
}
else {
    $whereclause = "";
} ORDER BY lastname ASC";

I get this error in the browser:

Parse error:  syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\2010 new site\list_users_abc_test.php on line 54

[Günter_Schenk]

Gail,

the image step.1 precisely indicates that you have to put the...

if(!empty($_GET['letter']) && ctype_alpha($_GET['letter']))
{
    $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";
}
else {
    $whereclause = "";
}

...code block *above* the first line of your main query (and not inside the query !), which in your case starts with:

mysql_select_db($database_GCSMembership, $GCSMembership);

What you need to integrate into the query itself, is just the variable $whereclause, and this is well indicated in step2

[gbergan52]

OK, I'm making some progress! I misunderstood where I should first put the dynamic whereclause code, so thank you for clarifying that. I also misunderstood step2.jpg. This is what I have now:

---

// dynamic where clause: letter

if(!empty($_GET['letter']) && ctype_alpha($_GET['letter']))
{
    $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";
}
else {
    $whereclause = "";
}

//END;

mysql_select_db($database_GCSMembership, $GCSMembership);
$query_listUsers = "SELECT user_id, lastname, firstname, email FROM users WHERE activity_status = 'y' ".$whereclause." ORDER BY lastname ASC";

---

What happens now is that on the first loading of the page into the browser, I'm still getting the first 50 members (it goes from lastname A~C). However, oddly enough, if I click on letter D again, I now get only the lastnames starting with D. It appears the initial limiting of the recordset to 50 is still causing a problem.

And even though the letters are functioning properly (each one gives me only a list of lastnames starting with that letter), I'm still getting an error message at the top of the browser that says:

Notice:  Use of undefined constant letter - assumed 'letter' in C:\wamp\www\2010 new site\list_users_abc_test.php on line 46

Line 46 has the code: $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";

I don't know if you need to see this, but my coding for the letters is as follows:

<div class="letters">
  <p>

  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=a">A</a>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=b">B</b>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=c">C</c>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=d">D</d>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=e">E</e>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=f">F</f>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=g">G</g>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=h">H</h>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=i">I</i>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=j">J</j>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=k">K</k>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=l">L</l>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=m">M</m>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=n">N</n>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=o">O</o>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=p">P</p>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=q">Q</q>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=r">R</r>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=s">S</s>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=t">T</t>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=u">U</u>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=v">V</v>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=w">W</w>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=x">X</x>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=y">Y</y>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=z">Z</z>
  </p>
</div>

[Günter_Schenk]

Hi Gail,

congratulations for getting the severe issues sorted out by now, and I´m very happy about this

I don't know if you need to see this, but my coding for the letters is as follows:

<div class="letters">
  <p>

  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=a">A</a>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=b">B</b>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=c">C</c>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=d">D</d>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=e">E</e>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=f">F</f>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=g">G</g>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=h">H</h>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=i">I</i>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=j">J</j>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=k">K</k>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=l">L</l>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=m">M</m>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=n">N</n>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=o">O</o>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=p">P</p>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=q">Q</q>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=r">R</r>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=s">S</s>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=t">T</t>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=u">U</u>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=v">V</v>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=w">W</w>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=x">X</x>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=y">Y</y>
  <a href="<?php echo $_SERVER["PHP_SELF"]; ?>?letter=z">Z</z>
  </p>
</div>

Well, these are meant to be HTML hyperlinks which all require the same closing tag: </a>

[gbergan52]

OK, now I've really embarrassed myself! I knew that! Don't know what I

was thinking.

Only two niggling issues remain. 1) still getting the first 50 members

after loading the page. I tried to delete the lines with "maxusers" in

them, but then it really got mad at me. This only happens the first time

the page loads; if I click on A again, I get only the As. My concern

with the 50 limitation on the pages is that there may be some letters of

the alphabet that have more than 50 members, and it will cut them off on

the bottom. Do you know how I can get rid of that limitation and all

lines that refer to it?

2) Still getting the error "Notice: Use of undefined constant letter -

assumed 'letter' in C:\wamp\www\2010 new site\list_users_abc_test.php

on line *46". *As I mentioned before, line 46 has: *$whereclause = " AND

lastname LIKE '".$_GET[letter]."%'";* The alphabetical links work, but

the error message remains. Any ideas?

Gail

[Günter_Schenk]

My concern with the 50 limitation on the pages is that there may be some letters of

the alphabet that have more than 50 members, and it will cut them off on

the bottom.

Oh, I see -- it seems that your page lacks the Dreamweaver Recordset Navigation Bar which will generate the required "First", "Previous", "Next" and "Last" links to allow for paging.

Click the cursor at the desired position (e.g. below the page headline) and insert it from here:

Insert > Data Objects > Recordset Paging > Dreamweaver Recordset Navigation Bar

[Günter_Schenk]

Notice:  Use of undefined constant letter - assumed 'letter' in C:\wamp\www\2010 new site\list_users_abc_test.php on line 46

Line 46 has the code: $whereclause = " AND lastname LIKE '".$_GET[letter]."%'";

I suppose it´s because you accidently forgot to enclose 'letter' in single quotes here. Try with:

$_GET['letter']

What happens now is that on the first loading of the page into the browser, I'm still getting the first 50 members (it goes from lastname A~C).

It´s possible to set this to a lower (or higher) value, but whatever value you decide on here:

$maxRows_whatever = 10;

$pageNum_whatever = 0;

...will be the number of displayed records when the page first loads, and there´s no way to make this dynamic as well.

It appears the initial limiting of the recordset to 50 is still causing a problem.

It´s not a problem, it´s a feature

[gbergan52]

First, you were correct about the missing single quotes, and that solved

the problem. Thanks very much.

So, a feature it is! I guess there's no real harm done to load more at

the "first look" of the member list. It does work correctly for all

letters. You are right in that if I drop "maxusers" down to 10 or 20,

the bottom half of, say, the Bs, gets cut off.

I originally had the Recordset Paging Navigation Bar on my page, but my

client didn't like it because he had to page through and hunt for

members. Hence my determination to provide an A,B,C navigation for him,

which you have so kindly helped me with. I didn't think about using both

features - the paging navigation bar, plus the A,B,C navigation. That

would definitely solve the worry of someday going over 50 members in any

given letter and not noticing that the list is cut off. I'll add it back.

I want to thank you profusely for all your help on this. Not only have

you stuck with me, but your timely responses meant I could finish this

today. You are my new best friend!

Best regards,

Gail

[Günter_Schenk · Accepted solution]

Gail,

it´s been a real pleasure to participate in such an intense "dialogue", and I salute you for remaining persistent !

Cheers,

Günter