Page 1 of 1
Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 2:18 am
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?
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 2:27 am
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']
}
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 2:47 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 2:49 am
by VladSun
So ... look at line #16 and make it work - you need string concatenation operators there.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 3:08 am
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...
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 3:09 am
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>
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 3:21 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 3:28 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 3:45 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 4:09 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 5:12 am
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.
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 5:29 am
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++;
}
Re: Creating an A-Z index from MySQL
Posted: Fri May 16, 2008 5:55 am
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.
Re: Creating an A-Z index from MySQL
Posted: Sat May 17, 2008 3:25 am
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>
Re: Creating an A-Z index from MySQL
Posted: Sat May 17, 2008 3:30 am
by kdidymus
Cancel that. Discovered that I can user ORDER BY surname,forename and vice versa.
Sorry.
KD.