Creating an A-Z index from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Creating an A-Z index from MySQL

Post by kdidymus »

I'm becoming a pain and for this I apologise.

I have two more questions and this is the first one.

What I want to do is create a page of PHP which automatically produces a list of my ancestors in whatever order the visitor chooses. Let's take for example an A-Z list by surname. They click on a hyperlink defined as http://www.didymus.org.uk/a-z.php?order=surname.

I know I have to define a loop for the PHP file to automatically download the required data but I don't know how this is done.

The results page would display:

urn | surname | forename | yearofbirth

I know how to make the results "clickable" using an <a> and </a> tag at the appropriate place. What I need to know though is how to walk through an array to automatically produce a list from my database.

Any ideas?
User avatar
lafever
Forum Commoner
Posts: 99
Joined: Sat Apr 05, 2008 2:03 pm
Location: Taylor, MI

Re: Creating an A-Z index from MySQL

Post by lafever »

You will have to do something like so.

Code: Select all

 
$allowed_order = array('urn', 'surname', 'forename', 'yearofbirth');
$order_by = in_array($_GET['order'], $allowed_order) ? $_GET['order'] : 'urn';
 
$query = 'SELECT * FROM `table` ORDER BY '$order_by' ASC';
$res = mysql_query($query);
 
while ($row = mysql_fetch_array($res)) {
    // display your table here by $row['rowname']
}
 
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

I see how that WOULD work but sadly it doesn't. Here is my code:

Code: Select all

<?php
/* Program: display.php
 * Desc:    Displays all details of selected individual.
 */
?>
<html>
<head><title>Results</title></head>
<body>
<?php
include_once("*******.inc");
  $cxn = mysql_connect($host,$user,$password)
         or die ("couldn't connect to server");
  mysql_select_db($database);
  $allowed_order = array('urn', 'surname', 'forename', 'yearofbirth');
  $order_by = in_array($_GET['order'], $allowed_order) ? in_array($_GET['order'], $allowed_order) : 'urn';
  $query = 'SELECT * FROM `tree` ORDER BY '$order_by' ASC';
  $result = mysql_query($query)
            or die ("Couldn't execute query.");
  $link = "http://www.didymus.org.uk/display.php?urn=";
  $rowht = "valign='top'";
  $rowcl = "bgcolor='#CCFFCC' valign='top'";
  $text = "font face='Arial' size='1'";
  $hltext = "font face='Arial' size='1' color='#000000'";
  $nw = "nowrap";
 
  /* Display results in a table */
  while ($row = mysql_fetch_array($res))
  {
     extract($row);
  echo "<font face='Arial' size='3'><b>Results</font></b>";
  echo "<table cellspacing='0' width='490'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
     echo " <tr $rowht><td><a href='$link$urn'><$hltext>$row['$urn']</font></td><td><$text>$row['surname']</font></td><td><$text>$row['forename']</font></td><td><$text>$row['yearofbirth']</font></td></tr>\n
        <tr $rowcl><td><a href='$link$urn'><$hltext>$row['$urn']</font></td><td><$text>$row['surname']</font></td><td><$text>$row['forename']</font></td><td><$text>$row['yearofbirth']</font></td>\n
        ";
  }
  echo "</table>\n";
?>
</body></html>
And here is the error I get:

Parse error: syntax error, unexpected T_STRING in /home/kdidymus/public_html/a-z.php on line 16

Hmm...

KD.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Creating an A-Z index from MySQL

Post by VladSun »

So ... look at line #16 and make it work - you need string concatenation operators there.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
lafever
Forum Commoner
Posts: 99
Joined: Sat Apr 05, 2008 2:03 pm
Location: Taylor, MI

Re: Creating an A-Z index from MySQL

Post by lafever »

It's a simple error that you should have caught. I didn't test the code prior of course. I also added the mysql_real_escape_string().. whoops

Code: Select all

 
$query = 'SELECT * FROM `tree` ORDER BY `'.mysql_real_escape_string($order_by).'` ASC';
 
Simple debugging man...
Last edited by lafever on Fri May 16, 2008 3:09 am, edited 1 time in total.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

I've corrected the code but the results are unexpected. What am I doing wrong?

http://www.didymus.org.uk/a-z.php?order=surname

If you click on this link you'll see that lines are repeated and that it's NOT in alphabetical order by surname!!!

Here is my revised code:

Code: Select all

<?php
/* Program: display.php
 * Desc:    Displays all details of selected individual.
 */
?>
<html>
<head><title>Results</title></head>
<body>
<?php
include_once("*******.inc");
  $cxn = mysql_connect($host,$user,$password)
         or die ("couldn't connect to server");
  mysql_select_db($database);
  $allowed_order = array('urn', 'surname', 'forename', 'yearofbirth');
  $order_by = in_array($_GET['order'], $allowed_order) ? in_array($_GET['order'], $allowed_order) : 'urn';
  $query = "SELECT * FROM tree ORDER BY $order_by";
  $result = mysql_query($query)
            or die ("Couldn't execute query.");
  $link = "http://www.didymus.org.uk/display.php?urn=";
  $rowht = "valign='top'";
  $rowcl = "bgcolor='#CCFFCC' valign='top'";
  $text = "font face='Arial' size='1'";
  $hltext = "font face='Arial' size='1' color='#000000'";
  $nw = "nowrap";
  echo "<font face='Arial' size='3'><b>Results</font></b>";
  echo "<table cellspacing='0' width='490'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  /* Display results in a table */
  while ($row = mysql_fetch_array($result))
  {
  extract($row);
  echo "<tr $rowht><td><a href='$link$urn'><$hltext>$urn</font></td><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td></tr>\n
      <tr $rowcl><td><a href='$link$urn'><$hltext>$urn</font></td><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td></tr>\n
        ";
  }
  echo "</table>\n";
?>
</body></html>
User avatar
lafever
Forum Commoner
Posts: 99
Joined: Sat Apr 05, 2008 2:03 pm
Location: Taylor, MI

Re: Creating an A-Z index from MySQL

Post by lafever »

Change $order_by also my bad

Code: Select all

 
$order_by = in_array($_GET['order'], $allowed_order) ? $_GET['order'] : 'urn';  
 
The other way would just return 1 because that's what in_array() returns.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Creating an A-Z index from MySQL

Post by VladSun »

I would rewrite it as follows:

Code: Select all

$order_by = empty($allowed_order[intval($_GET['order'])]) ? 'urn' : $allowed_order[intval($_GET['order'])];
while keeping the "order" URL parameter numerical.

This way one will not expose the DB table structure.
There are 10 types of people in this world, those who understand binary and those who don't
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Re: Creating an A-Z index from MySQL

Post by Rovas »

From what I seen on the site you have to modify your interogation to stop duplicate entries.
Modify to

Code: Select all

 
SELECT DISTINCT TABLE colums names FROM tree ORDER BY name, surname;
 
Read more about here. Some programmers prefer to the data sorting in php not using sql because they say it' s faster and doesn' t load the server so much.
Also protect your queries from SQL injection by checking, escaping and validate the data from the user that the query uses.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

Okay. I've decided to use separate PHP files for each type of search. So far just by SURNAME and FORENAME.

The result can be seen here:

http://www.didymus.org.uk/a-zsn.php and http://www.didymus.org.uk/a-zfn.php

There is still the issue of duplicate lines.

I understand why this is happening but I can't figure out how to solve it.

I'd like the alternating line colours because it makes it easier to read.

KD.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

Here is the latest version of my A-Z by surname code:

Code: Select all

<?php
/* Program: display.php
 * Desc:    Displays all details of selected individual.
 */
?>
<html>
<head><title>Results</title></head>
<body>
<?php
include_once("*******.inc");
  $cxn = mysql_connect($host,$user,$password)
         or die ("couldn't connect to server");
  mysql_select_db($database);
  $query = "SELECT * FROM tree ORDER BY surname";
  $result = mysql_query($query)
            or die ("Couldn't execute query.");
  $link = "http://www.didymus.org.uk/display.php?urn=";
  $rowht = "valign='top'";
  $rowcl = "bgcolor='#CCFFCC' valign='top'";
  $text = "font face='Arial' size='1'";
  $hltext = "font face='Arial' size='1' color='#000000'";
  $nw = "nowrap";
  echo "<font face='Arial' size='3'><b>A-Z Results by SURNAME</font></b><br><font face='Arial' size='1'>";
  echo "<table cellspacing='0' width='490'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td><$text><b>FORENAME</b></font></td><td><$text><b>BIRTH YEAR</b></font></td><td><$text><b>VIEW</b></font></td>\n</tr>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  /* Display results in a table */
  while ($row = mysql_fetch_array($result))
  {
  extract($row);
  echo "<tr $rowht><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
  extract($row);
  echo "<tr $rowcl><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
  }
  echo "</table>\n";
?>
</body></html>
 
It now works in alphabetical order but the rows are repeated. This is obviously because the PHP is populating both rows of the table before looping and producing the rest. What I need to find is a way to tell the PHP to extract data for EACH row whilst still giving the alternating colours.

Any ideas?

KD.
User avatar
lafever
Forum Commoner
Posts: 99
Joined: Sat Apr 05, 2008 2:03 pm
Location: Taylor, MI

Re: Creating an A-Z index from MySQL

Post by lafever »

I don't know why you switched them to separate pages.

They are double posting because of your while statement

Code: Select all

 
while ($row = mysql_fetch_array($result)) {
extract($row);
echo "<tr $rowht><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
extract($row); 
echo "<tr $rowcl><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
}
 
To make the backgrounds still change and stop your double posting you should add a counter of some sort

Code: Select all

 
$i=0;
while ($row = mysql_fetch_array($result)) {
extract($row);
$bg = ($i%2) ? $rowht : $rowcl;
echo "<tr $bg><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
$i++;
}
 
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

lafever - thank you. You're a star. It works like a dream. I would never have figured that out with all the books in a reasonable sized library!

KD.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

Hmm. Noticed another thing.

http://www.didymus.org.uk/a-zsn.php

This produces an alphabetical list based on the SURNAME of the person in my MySQL database.

Problem is, it doesn't apply any sorting to the data beyond that and so James COSWAY appears before Edwin COSWAY in the list. Is there a way of combining "ORDER BY" to further sort data by a second column?

Here is my code:

Code: Select all

<?php
/* Program: display.php
 * Desc:    Displays all details of selected individual.
 */
?>
<html>
<head><title>Results</title></head>
<body>
<?php
include_once("*******.inc");
  $cxn = mysql_connect($host,$user,$password)
         or die ("couldn't connect to server");
  mysql_select_db($database);
  $query = "SELECT * FROM tree ORDER BY surname";
  $result = mysql_query($query)
            or die ("Couldn't execute query.");
  $link = "http://www.didymus.org.uk/display.php?urn=";
  $rowht = "valign='top'";
  $rowcl = "bgcolor='#CCFFCC' valign='top'";
  $text = "font face='Arial' size='1'";
  $hltext = "font face='Arial' size='1' color='#000000'";
  $nw = "nowrap";
  echo "<font face='Arial' size='3'><b>A-Z Results by SURNAME</font></b><br><font face='Arial' size='1'>";
  echo "<table cellspacing='0' width='490'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td><$text><b>FORENAME</b></font></td><td><$text><b>BIRTH YEAR</b></font></td><td><$text><b>VIEW</b></font></td>\n</tr>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  /* Display results in a table */
  $i=0;
  while ($row = mysql_fetch_array($result))
  {
  extract($row);
  $bg = ($i%2) ? $rowht : $rowcl;
  echo "<tr $bg><td><$text>$surname</font></td><td><$text>$forename</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
  $i++;
  }
  echo "</table>\n";
?>
</body></html>
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Creating an A-Z index from MySQL

Post by kdidymus »

Cancel that. Discovered that I can user ORDER BY surname,forename and vice versa.

Sorry.

KD.
Post Reply