MySQL query results sorted alphabeltically

[webguync]

I have a MySQL query that displays data from a table in a long list of about 100 entries. it requires the user to scroll quite a bit down the page. I know I can limit the number of results per page using the LIMIT clause, but how can I display the results alphabetically (in this case by last name), so that initially all of the A's will display, and I can provide links for the rest of the alpha's, so when you click on B's, all of the B's will display, C's, D's etc.

 

Let me know if I need to explain further, but I believe this should be understandible.

[Barand]

SELECT firstname, lastname FROM tablename
WHERE lastname LIKE '$letter%'
ORDER BY lastname

 

If $letter contains "A", the above will pull just those beginning with "A"

[webguync]

I see, thanks.

 

is there an easier way to create results for each letter of the alpha instead of a new query to correspond to each letter? Right now I am setting the variable $letter=A, but then I will want to do that for B through Z as well.

 

thanks for the help!

[ohdang888]

i assume A will be on a different "page" than B

 

such as .... "search.php?letter=A" compared to "search.php?letter=B"

 

then its really easy to change the letter...

 

$letter = sql_quote['letter'];

 

however that won't work alone, you need a script to call the url. I'm not on my home computer, so i don't have that script with me....

[webguync]

yes, A, B C etc will all be on different pages so I can link from the initial results page (page A). If you could post the code I would beed when you get a chance that would be much appreciated.

[ohdang888]

i will later, but i can't get it tonight....

 

send me an email at

 

tommycrush@gmail.com

[Barand]

<?php
$let = 'A';
for ($i=0; $i<26; $i++) 
{
   echo "<a href='?letter=$let'>$let</a> ";
   $let++;
}

if (isset($_GET['letter']))
{
    $letter = $_GET['letter'];
    echo "<h3>$letter</h3>";
    
    $sql = "SELECT firstname, lastname FROM tablename              
            WHERE lastname LIKE '$letter%'
            ORDER BY lastname";                                                          // adjust column and table names
    $res = mysql_query($sql) or die(mysql_error());
    while (list($f, $l) = mysql_fetch_row($res))
    {
        echo "$l, $f<br/>";
    }
}
?>

[webguync]

thanks the code supplied above is pretty much what I want, but only the first name is displayed for some reason. In my SQL query I have

$sql ="SELECT id, f_name, l_name, company FROM $table_name WHERE l_name LIKE '$letter%' ORDER BY l_name ";
$result = @mysql_query($sql, $connection) or die(mysql_error());

 

so I should be getting the result of first name, last name and company

[Barand]

try this with your query

<?php
$let = 'A';
for ($i=0; $i<26; $i++) 
{
   echo "<a href='?letter=$let'>$let</a> ";
   $let++;
}

if (isset($_GET['letter']))
{
    $letter = $_GET['letter'];
    echo "<h3>$letter</h3>";
    
    $sql = "SELECT id, f_name, l_name, company 
            FROM $table_name 
            WHERE l_name LIKE '$letter%' 
            ORDER BY l_name ";                                                          // adjust column and table names
    $res = mysql_query($sql) or die(mysql_error());
    while (list($id, $fn, $ln, $co) = mysql_fetch_row($res))
    {
        echo "$id $fn $ln, $co<br/>";
    }
}
?>

[webguync]

thanks that worked!