[SOLVED] PHP MySQL Alphabetical

[jason97673]

Hello,

 

I am trying to add the following functionality to my site.

 

I have a list of lets say 300 authors. I am going to have them listed alphabetically like 20 per page etc. And I would like the user to beable to click on the letter that the authors name begins with and have it display all authors with that letter at the beginning of there name.

 

For example if someone wants to find someone that has the last name beginning with an S, they can click on S and bring up all 20 results that have there name beginning with an S. Im sure it makes sense.

 

Sorta like paging except Alphabetically instead of numerically.

 

I am sure there is a tutorial or someone else has had this problem so it should be an easy solve.

Any help appreciated

[cooldude832]

Look into regular expressions for your letter part, but basicall after you write the regex for all authors that start with the given letter then just say ORDER BY `Author Name` ASC and it will sort alpha inside that letter for the 20 per page just use the LIMIT $start, $end part of a mysql query.  I have a script you can possibly use for displaying search results, but its for more robust that just a single line of text

[per1os]

Regular expressions won't help as you only want to pull out data you will use on that page.

 

Instead structure your query like this:

 

<?php
$letter = "b";
$start = 1;
$end = 20;
$sql = "SELECT * FROM authors WHERE author_name LIKE '" . $letter . "%' LIMIT $start, $end ORDER BY author_name asc";
?>

 

Should do what you want.

[jason97673]

Ok that makes senses except how does the variable letter know to change when clicking on a different letter?

 

Perhaps, <a href="authors.php?letter.php="' .$letter .'">A</a>

then do you make 25 more of the same tags? Seems like a loop would work here, but not sure how to generate that loop.

[per1os]

<?php
$alphabet = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
$alphabet = explode(",", $alphabet);

foreach ($alphabet as $letter) {
   echo '<a href="authors.php?letter.php=' .$letter .'">' . $letter . '</a>';
}
?>

[jason97673]

K almost there,

 

$letter = "a";
$start = 1;
$end = 20;
$sql = "SELECT * FROM authors WHERE last_name LIKE '" . $letter . "%' LIMIT $start, $end ORDER BY last_name asc";

$result = mysql_query($sql);
$row = mysql_fetch_array($result);

$alphabet = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
$alphabet = explode(",", $alphabet);

foreach ($alphabet as $letter) {
   echo '<a href="authors.php?letter.php=' .$letter .'">' . $letter . '</a>';
}
                              
echo $result['last_name'];

 

Now to display the data, I just did a simple test but comes up with the error not a valid mysql result resource.

 

I know Ill need another loop, but for now I am just going to test the first result and if it comes up I can do the rest.

[per1os]

I think that the limit needs to be after the order by, sorry about that.

 

$sql = "SELECT * FROM authors WHERE last_name LIKE '" . $letter . "%' ORDER BY last_name asc LIMIT $start, $end";

[jason97673]

Alright, the first result comes up now but when I click on a letter, the very first result which is a name that starts with A stays up there.

[jason97673]

Still lookin for the last bit of help 

[per1os]

Show the whole code.

[jason97673]

$letter = "a";
$start = 1;
$end = 20;
$sql = "SELECT * FROM authors WHERE last_name LIKE '" . $letter . "%' ORDER BY last_name asc LIMIT $start, $end";

$result = mysql_query($sql);
$row = mysql_fetch_array($result);

$alphabet = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
$alphabet = explode(",", $alphabet);

echo "<br /><br /><br /><br /><br /><br />";   

foreach ($alphabet as $letter) {
   echo '<a href="authors.php?letter.php=' .$letter .'">' . $letter . '</a>';
}


                          
echo $row['last_name'];

[per1os]

<?php
$letter = isset($_GET['letter'])?mysql_real_escape_string($_GET['letter']):'a'; // if not set default to a if it is escape and assign to $letter

$alphabet = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
$alphabet = explode(",", $alphabet);

if (!in_array($letter, $alphabet)) {
     $letter = 'a'; // incase they pass a number etc.
}

$start = 1;
$end = 20;
$sql = "SELECT * FROM authors WHERE last_name LIKE '" . $letter . "%' ORDER BY last_name asc LIMIT $start, $end";

$result = mysql_query($sql);
$row = mysql_fetch_array($result);

echo "<br /><br /><br /><br /><br /><br />";   

foreach ($alphabet as $letters) {
    if ($letters == $letter) {
         echo ' ' . $letter . ' ';
   }else {
       echo '<a href="authors.php?letter=' .$letters .'">' . $letters . '</a>';
  }
}

?>

 

See how that works for you.

[jason97673]

Worked like a charm, thanks for the help 

[anushastar]

well i am making a music site and in artist section i want to display artists by their first letter. i hav tried this code..it is showing all the alphabets but on clicking them it is not taking the value from database.i am using php my admin..plz help me out ..as soon as possible.