Button/Custom page question

LiquidCD

Greetings everyone,

Okay, I currently have my first page up and working with php and mySql. By default, the code in the page will pull all the information from the table and list the information in HTML table form by last name of the person.

I would like to add some functionality so that a person could click on the letter of the alphabet and it would either reorder the entire list starting with the letter clicked, or would just list the last names in the table of the letter clicked (in which the user would need to go back to default view, which I think would be taken care of by a refresh).

A | B | C | D | E | F (etc...) | Original View |

My question is, how do I create a button list or simple text link that when clicked on will reorder the information based on the letter clicked? I have tried a few different things, but no success.
Here is the current php code

<?php 
include("../../../includes/test.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname"); 
$query = "SELECT * FROM testtable ORDER BY 'Name of Child'"; 
$result = mysql_query($query); 
echo "<TABLE BORDER=1 cellpadding=5>"; 
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=18%><strong>Name of Child</strong></td>";
echo "<td width=20%><strong>Name(s) of Parents</strong></td>";
echo "<td width=25%><strong>Name(s) of Godparents</strong></td>";
echo "</tr>";

while ($row = mysql_fetch_array($result)) 
{ 
echo "<tr>"; 
echo "<td><strong>{$row['Name of Child']}</strong><br> Baptismal Date:<em>{$row['Baptismal Date']}</em></td>"; 
echo "<td>{$row['Name of Parents']}</td>"; 
echo "<td>{$row['Name of Godparents']}</td>"; 
echo "</tr>"; 
} 
echo "</TABLE>"; 

$today = date("l dS of F Y h:i a");
print "<CENTER>Today is $today (server time)</CENTER>";
?>

ereptur

Originally posted by LiquidCD

Here is the current php code

<?php 
include("../../../includes/test.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname"); 
$query = "SELECT * FROM testtable ORDER BY 'Name of Child'"; 
$result = mysql_query($query); 
echo "<TABLE BORDER=1 cellpadding=5>"; 
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=18%><strong>Name of Child</strong></td>";
echo "<td width=20%><strong>Name(s) of Parents</strong></td>";
echo "<td width=25%><strong>Name(s) of Godparents</strong></td>";
echo "</tr>";

while ($row = mysql_fetch_array($result)) 
{ 
echo "<tr>"; 
echo "<td><strong>{$row['Name of Child']}</strong><br> Baptismal Date:<em>{$row['Baptismal Date']}</em></td>"; 
echo "<td>{$row['Name of Parents']}</td>"; 
echo "<td>{$row['Name of Godparents']}</td>"; 
echo "</tr>"; 
} 
echo "</TABLE>"; 

$today = date("l dS of F Y h:i a");
print "<CENTER>Today is $today (server time)</CENTER>";
?>

[/B]

when executing your query after the user clicks the letter put in a LIKE criteria. Such as:
SELECT * FROM testtable ORDER BY 'Name of Child' WHERE 'Name of Child' LIKE "$letter%". You would of course need your links to read
<a href="$PHP_SELF?letter=a">A</a>
<a href="$PHP_SELF?letter=b">B</a>
etc....

LiquidCD

Okay, what you replied with maks sense, but at what point in my code would I put that little bit since I still want the default look to list everything from A-Z when the page is visited. Once the default view is loaded, then they can click on the "link" of the letter to reorder.

Tried adding a search function, but that is another story....

Is this a case of trying to have my cake and eat it to? Thanks!

ereptur

before you run your query perform a test.

$letter = $_GET["letter"];

if (isset($letter)) {
   $query = "SELECT * FROM testtable ORDER BY 'Name of Child' WHERE 'Name of Child' LIKE '$letter%'";
} else {
   $query = "SELECT * FROM testtable ORDER BY 'Name of Child';
}

$result = mysql_query($query);

volleytotal_ch

you may do it like this, so that you don't have to write all links!
(replace [Script_Name] by the filename!)

<?php 
// Output letters as links: (Rem. ascii range from 65 to 90 are letters A to Z!)
  for($x=65;$x<=90;$x++)
  {
     if($x==ord($subsel)) echo "<b>".chr($x)."</b>";
     else echo "<a href=\"[Script_Name]?letter=".chr($x)."\">".chr($x)."</a> ";
  }

and change the query to:

$query = "SELECT * FROM testtable ORDER BY 'Name of Child' WHERE 'Name of Child' LIKE '$letter%' OR 'Name of Child' LIKE '".strtolower($letter)."%'";
// So that you get all entries with lower or upper case starting!

hth

ereptur

good point. writing that many links would get annoying.

and the strtolower works well if you setup the field to be case sensitive, but I never do 🙂

volleytotal_ch

Originally posted by ereptur
the strtolower works well if you setup the field to be case sensitive, but I never do 🙂

didn't know that 😉

ereptur

by default MySQL field are marked as Case Sensitive, but you can alter them to be Case Insensitive.

Makes it much easier during queries if you really don't care about the case.

LiquidCD

Well, attempting to use the above snippets that were given, I am getting the following error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/web/lithuani/public_html/test/test6.php on line 116

The code is:

<?php 
include("../../includes/test.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname"); 
$query = "SELECT * FROM testtable ORDER BY 'Name of Child' WHERE 'Name of Child' LIKE '$letter%'";
$result = mysql_query($query);

// Output letters as links: (Rem. ascii range from 65 to 90 are letters A to Z!)
for($x=65;$x<=90;$x++)
{
if ($x==ord($subsel)) echo "<b>".chr($x)."</b>";
else 
echo "<a href=\"test6.php?letter=".chr($x)."\">".chr($x)."</a> ";
}
$letter = $_GET["letter"];

echo "<TABLE BORDER=1 cellpadding=5>"; 
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=18%><strong>Name of Child</strong></td>";
echo "<td width=20%><strong>Name(s) of Parents</strong></td>";
echo "<td width=25%><strong>Name(s) of Godparents</strong></td>\n";
echo "</tr>";

while ($row = mysql_fetch_array($result))      //THIS IS LINE 116
{ 
echo "<tr>"; 
echo "<td><strong>{$row['Name of Child']}</strong><br> Baptismal Date:<em>{$row['Baptismal Date']}</em></td>"; 
echo "<td>{$row['Name of Parents']}</td>"; 
echo "<td>{$row['Name of Godparents']}</td>"; 
echo "</tr>"; 
} 
echo "</TABLE>"; 

$today = date("l dS of F Y h:i a");
print "<CENTER>Today is $today (server time)</CENTER>";
?>

This is the webpage that I am testing when I get this:

http://www.lithuaniangenealogy.org/test/test6.php

As you can see, the alphabet links are generated, but the table from the database is not. Right now, the original page looks like:

http://www.lithuaniangenealogy.org/databases/il/HolyCross.html

This is what I want to happen as default (full table listed alphabetcically), with the alphabet character links before the table is generated.

I have tried changing this in several ways, but this is the closest I get without having multiple errors.

Thanks for all your help so far.

volleytotal_ch

You got a wrong order in your SQL sytax!

$query = "SELECT * FROM testtable WHERE 'Name of Child' LIKE '$letter%' ORDER BY 'Name of Child'";

LiquidCD

Thanks for all your help. Okay, making the switch in the above code and going to the following page, will be default, now list the entire contents of the table from A-Z (like I wanted).

http://www.lithuaniangenealogy.org/test/test6.php

However, now when I click on the letter to reorder the data, no information is pulled out when it rebuilds the page. Do I need to move the the "output letters as links" to another place in the code?

For example: When I click on the "S", I get a good page, no errors, just no information - I'll be darned if I can figure out why this is. Any thoughts?

http://www.lithuaniangenealogy.org/test/test6.php?letter=S

        <?php 
include("../../includes/test.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname"); 
$query = "SELECT * FROM testtable WHERE 'Name of Child' LIKE '$letter%' ORDER BY 'Name of Child'";
$result = mysql_query($query);

// Output letters as links: (Rem. ascii range from 65 to 90 are letters A to Z!)
for($x=65;$x<=90;$x++)
{
if ($x==ord($subsel)) echo "<b>".chr($x)."</b>";
else 
echo "<a href=\"test6.php?letter=".chr($x)."\">".chr($x)."</a> ";
}
$letter = $_GET["letter"];

echo "<TABLE BORDER=1 cellpadding=5>"; 
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=18%><strong>Name of Child</strong></td>";
echo "<td width=20%><strong>Name(s) of Parents</strong></td>";
echo "<td width=25%><strong>Name(s) of Godparents</strong></td>\n";
echo "</tr>";

while ($row = mysql_fetch_array($result)) 
{ 
echo "<tr>"; 
echo "<td><strong>{$row['Name of Child']}</strong><br> Baptismal Date:<em>{$row['Baptismal Date']}</em></td>"; 
echo "<td>{$row['Name of Parents']}</td>"; 
echo "<td>{$row['Name of Godparents']}</td>"; 
echo "</tr>"; 
} 
echo "</TABLE>"; 

$today = date("l dS of F Y h:i a");
print "<CENTER>Today is $today (server time)</CENTER>";
?>

ereptur

You need to retrieve the $letter variable from $_GET.

$letter = $_GET["letter"];

then put it in you query string