Search sql db alphebetically

ChicoMendez

in SQL how do i search a colum by alpha.
i dont want to order it i want to select only the rows with a TITLE field beginning with A for exmple

Tnx

Ph3n0m

Basically your db query has to be like this


SELECT * from table where TITLE LIKE '$letter%' order by TITLE ASC;

Where you define $letter as which as A -> Z

That select will select all items where Title is like "A....."

As far as I remember, you do have to order your select (I havent tested the above select)

ChicoMendez

hi tnx very much for that.

ive one little question off that....

If i want to create a dropdown list with the letters of the alphabet... but showing only the letters for which there is an entry in the db TITLE field beginning with that letter..

How would i go about that.
the drop down etc is ok... its the sql side that im not too sure about.

tnx alot

Ph3n0m

Well you would basically do the same search but count the rows that are returned and the put that within a loop

I was bored so I did a rough version for you


To generate your dropdown list, use this code

[PHP]<?
echo "<select name='letter'>";
foreach (range(A, Z) as $letter) {

$sql_alpha = "SELECT * from table where title LIKE '$letter%'";
$sql_alpha_check_result = mysql_query($sql_alpha) or die("Error checking database");
$sql_alpha_check_count = mysql_numrows($sql_alpha_check_result);
if ($sql_alpha_check_count == "0"){
}
else {
echo "<option value='$letter'>$letter</option>";
}
}
echo "</select>";
?>[/PHP]

cgarvey

You can also do it with just one query which would be a bit more efficient as Ph3n0m's example...

$sql_alpha = "SELECT DISTINCT LEFT( title, 1 ) ORDER BY title";

.. will give you "A,C,D,H,T,S,Z" or whatever first letters are present in the column called title.

.cg