Help with paginating and anchor link.

major dynamic123 · September 10th, 2003, 11:41 PM

Hi. I'm major. I own a bookstore and I am planning to develop my company's webpage using php scripts. I am planning to develop a webpage to display the list of book categories from MySQL. I've successfully done that already.

Now, I want to list categories using alphabetical letters A till Z. This is also done manually using HTML scripts.

My idea is to allow users click on letter "A" and let PHP extract from MySQL all the book categories starting with letter "A".

My question is:
1. How do I use <a href> with php on the alphabets to allow PHP do the extraction from MySQL?
2. I do not know how to code the PHP script to do this and I need some example on how to do this.

I hope those reading this forum will help me out with my problem. I prefer if someone can show me how to code the PHP script.

Thank you. And many thanks to Wrox for having this useful forum. Keep it up.

Regards,
Major

nikolai · September 11th, 2003, 12:06 AM

Well, you'll need to write a query that returns everything that starts with the letter you're selecting. Special characters, called wildcards, are used to specify "anything" in a query. The wildcard character in MySQL is '%'.

So, when someone clicks a link to select all categories that start with 'M', for example, your query will look like:

SELECT column list FROM table WHERE category LIKE 'M%'

Notice we use the LIKE operator instead of the equality operator when dealing with wildcards.

The links to your page can set a simple variable, say "cat_letter". The PHP script recieves this as $_GET['cat_letter']. Your script can just test for the existence of this variable, and if it's set, append the WHERE clause to your query.

$where_clause = '';
if (isset($_GET['cat_letter']) && !empty($_GET['cat_letter']))
{
$where_clause = "WHERE category LIKE '{$_GET['cat_letter']}%'";
}

$query = "SELECT columns FROM table $where_clause";

Hope this helps!

Take care,

Nik
http://www.bigaction.org/

nikolai · September 11th, 2003, 12:08 AM

I just realized -- I might have misinterpreted your original request... If you didn't ask how to get MySQL to only return a subset of categories that begin with a specified letter, please rephrase your question and possibly give me an example of what you'd like your output to be.

Take care,

Nik
http://www.bigaction.org/

major dynamic123 · September 11th, 2003, 01:31 AM

Hi, Nikolai. Thank you for helping me out with the problem.

Your explanation is almost to what I wanted to do. Actually I have a page called books.php. I plan to use only books.php to do the work. My initial plan is to:

1. create a list of alphabetical letters(a to z) using html code. I've done this already using A | B | ...Z

2. when a user visits the books.php page, I want the user to click any of the letters available in books.php, A till Z. The letters, A till Z represents the book categories; ie:A for Animals, Z for Zoology. Then the user will be presented with all the book categories beginning with letter A.

3. My initial plan is to allow PHP do the work in retrieving book titles in the Animals book category(assuming that the user selects A and then selects Animals).

I already have the idea on how to do this, but I need a complete php code to guide me and even give me an idea how it should be written.

Currently, my database(MySQL) has a table called "categories". The attributes are
1. category_id(primary key) and
2. category(for various book categories).

I hope this explanation will help clarify some of the doubts you have.

Thanks for the reply.

Regards,
major dynamic123

major dynamic123 · September 11th, 2003, 02:56 AM

Hi. Attached herewith is the complete source code on the book category problem. I have defined a variable, $cat_letter like what you've said.

How can I make corrections to the anchor link<a href"...">A</a> so that whenever the user clicks any letter, say C, the page will display the relevant book categories with letter 'c'?

Thanks

Regards,
major dynamic123

Filename:books.php
==================
<html>
<head>
<title>Books</title>
</head>

<body>
<?php
 $cat_letter='a';

 include("./ex/psa.inc");
 $connection = mysql_connect($host, $user, $pass) or die("Cannot connect to database");
 mysql_select_db($db) or die("cannot select database");

 echo '<a href="PHP_SELF?cat_letter=A">A</a>';
 echo ' ';
 echo $cat_letter;

 $query = "SELECT category FROM categories WHERE category like '$cat_letter%' ";
 $result = mysql_query($query) or die("error");

 if (mysql_num_rows($result)) {
 while ($row = mysql_fetch_object($result)) { ?>
 <? echo $row->category; ?>
 <? echo ' '; ?>
 <? }
 }
 mysql_close($connection);
?>
</body>
</html>

nikolai · September 11th, 2003, 01:47 PM

Quote:

quote:Originally posted by major dynamic123
Your explanation is almost to what I wanted to do. Actually I have a page called books.php. I plan to use only books.php to do the work. My initial plan is to:

1. create a list of alphabetical letters(a to z) using html code. I've done this already using A | B | ...Z

I'd stay away from using the tag as it's been deprecated. If you're going to use a stylesheet, use either the <div> or tags around your text and set all your font properties within you CSS class.

Quote:

quote:Originally posted by major dynamic123
2. when a user visits the books.php page, I want the user to click any of the letters available in books.php, A till Z. The letters, A till Z represents the book categories; ie:A for Animals, Z for Zoology. Then the user will be presented with all the book categories beginning with letter A.

Okay, so before a user can select a book, they have to select a letter for a category, which brings up a list of all the categories, then select the category they're interested in, which brings up a list of all the books in that category, then select the book(s) they were interested in?

If you don't have too many categories, it might be more user friendly to have a single page with ALL your categories listed alphabetically. Clicking on the anchor link will work just like a regular anchor and take you to the part of the page where that letter's categories start.

For example:

<a href="#A">A</a> | <a href="#B">B</a> ...

<a name="A">--A--</a>
Animals
Automobiles

<a name="B">--B--</a>
Bookkeeping
...

All of this code can be generated very easily with PHP. Your original list of anchors is simple:

// first we create an array containing all 26 letters.
$letters = array();
$begin = ord('A');
$end = ord('Z');

for ($i = $begin; $i <= $end; ++$i)
{
 $letters[] = chr($i);
}

// now we use that array to create the list of anchor links:
$anchors = array();
foreach($letters as $letter)
{
 $anchors[] = " <a href=\"#{$letter}\">--{$letter}--<a>\n";
}

$anchor_string = join(" | ", $anchors);
echo "{$anchor_string}\n";

// now output all the categories.
$query = "SELECT id, category FROM categories ORDER BY category ASC";
$result = mysql_query($query);

if($result)
{
 $categories = array();
 while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 {
 $categories[] = $row;
 }

 $first_letter = '';
 foreach($categories as $cat)
 {
 // check if we're starting a new letter
 if($first_letter != $cat[0])
 {
 $first_letter = $cat[0];
 echo "<a name=\"{$first_letter}\">--{$first_letter}--</a> \n";
 }
 echo "{$cat} \n";
 }
}

Keep in mind that I haven't tested this code -- I just wrote it from scratch here in this forum reply window. There's definitely other ways to do it, but it's a start.

Quote:

quote:Originally posted by major dynamic123
3. My initial plan is to allow PHP do the work in retrieving book titles in the Animals book category(assuming that the user selects A and then selects Animals).

I already have the idea on how to do this, but I need a complete php code to guide me and even give me an idea how it should be written.

Currently, my database(MySQL) has a table called "categories". The attributes are
1. category_id(primary key) and
2. category(for various book categories).

I hope this explanation will help clarify some of the doubts you have.

Well, where are your books stored? What's the schema there? Do books only belong to one category? What about a PHP book? Doesn't that belong in the Computers, Programming, and PHP categories?

Your question boils down to: "How can I select the books that belong to a single category?", but you haven't told me how you store the books in your database, which is vital to know before answering the question.

Take care,

Nik
http://www.bigaction.org/

nikolai · September 11th, 2003, 01:47 PM

Quote:

quote:Originally posted by major dynamic123
Filename:books.php
==================
<html>
<head>
<title>Books</title>
</head>

<body>
<?php
 $cat_letter='a';

 include("./ex/psa.inc");
 $connection = mysql_connect($host, $user, $pass) or die("Cannot connect to database");
 mysql_select_db($db) or die("cannot select database");

 echo '<a href="PHP_SELF?cat_letter=A">A</a>';
 echo ' ';
 echo $cat_letter;

 $query = "SELECT category FROM categories WHERE category like '$cat_letter%' ";
 $result = mysql_query($query) or die("error");

 if (mysql_num_rows($result)) {
 while ($row = mysql_fetch_object($result)) { ?>
 <? echo $row->category; ?>
 <? echo ' '; ?>
 <? }
 }
 mysql_close($connection);
?>
</body>
</html>

Okay, this doesn't do anything but select the categories. Also, you forgot the dollar sign in front of "PHP_SELF" in your link. It should actually be $_SERVER['PHP_SELF'] to conform to the register_globals = off setting, which is the default config setting since PHP 4.1.0.

Here's what I would do:

Create your book table. At it's simplest, it should just be

id primary key
title book title

Then create a separate table to hold the booksXcategories data:

book_id category_id

This allows you to assign multiple books to multiple categories.

For example, let's populate your database with a couple books:

TABLE: categories
id category
1 Computers
2 Programming
3 MySQL
4 PHP
5 C/C++
6 Zoology
7 Amusement Parks

TABLE: books
id title
1 Beginning PHP4
2 The Greater Yellowstone Ecosystem
3 PHP and MySQL
4 History of Sea World
5 Aardvarks

TABLE booksXcategories
book_id category_id
  1 1
  1 2
  1 4
  2 6
  3 1
  3 2
  3 3
  3 4
  4 6
  4 7
  5 7

Take some time to understand these tables. You'll notice that while numbers can repeat within a single column in the booksXcategories table, each row is unique. You can create an index on both rows, and a single unique index over BOTH rows.

To find all the books belonging to a specific category, say Programming:

Code:

SELECT books.title
  FROM books, categories, booksXcategories
 WHERE categories.category = 'Programming'
   AND categories.id       = booksXcategories.category_id
   AND books.id            = booksXcategories.book_id

See, the first part of the WHERE clause specifies that you're only interested in the Programming category. The second part links the categoriesXbooks table to the categories table by category ID. Since we've filtered out everything but the Programming category, the subset of the booksXcategories table we're dealing with is just the list of books who are in the programming category. The last part links the booksXcategories table to the books table by ID so we can retrieve the title of the book associated with the ID in the booksXcategories table.

Simple, when you think about it.

Similarly, we can get all the categories that a specific book belongs to:

SELECT categories.category
  FROM books, categories, booksXcategoriess
WHERE books.title = 'Beginning PHP4'
   AND books.id = booksXcategories.book_id
   AND categories.id = booksXcategories.category_id

Take care,

Nik
http://www.bigaction.org/