Creating an A-Z index from MySQL
Moderator: General Moderators
Creating an A-Z index from MySQL
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?
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
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
I see how that WOULD work but sadly it doesn't. Here is my code:
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.
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>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
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
Re: Creating an A-Z index from MySQL
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
Simple debugging man...
Code: Select all
$query = 'SELECT * FROM `tree` ORDER BY `'.mysql_real_escape_string($order_by).'` ASC';
Last edited by lafever on Fri May 16, 2008 3:09 am, edited 1 time in total.
Re: Creating an A-Z index from MySQL
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:
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
Change $order_by also my bad
The other way would just return 1 because that's what in_array() returns.
Code: Select all
$order_by = in_array($_GET['order'], $allowed_order) ? $_GET['order'] : 'urn';
Re: Creating an A-Z index from MySQL
I would rewrite it as follows:
while keeping the "order" URL parameter numerical.
This way one will not expose the DB table structure.
Code: Select all
$order_by = empty($allowed_order[intval($_GET['order'])]) ? 'urn' : $allowed_order[intval($_GET['order'])];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
Re: Creating an A-Z index from MySQL
From what I seen on the site you have to modify your interogation to stop duplicate entries.
Modify to
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.
Modify to
Code: Select all
SELECT DISTINCT TABLE colums names FROM tree ORDER BY name, surname;
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
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.
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
Here is the latest version of my A-Z by surname code:
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.
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>
Any ideas?
KD.
Re: Creating an A-Z index from MySQL
I don't know why you switched them to separate pages.
They are double posting because of your while statement
To make the backgrounds still change and stop your double posting you should add a counter of some sort
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>";
}
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
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.
KD.
Re: Creating an A-Z index from MySQL
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:
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
Cancel that. Discovered that I can user ORDER BY surname,forename and vice versa.
Sorry.
KD.
Sorry.
KD.