Selecting Rows That Start With A Certain Letter?

[RedChair]

Here's what I'm looking to do. I have a local business directory where people can browse by the business' name. I have everything split into three columns by counting the rows, dividing them by 3 and rounding so the first two columns display the same amount of rows and the third displays the remainder.

However, since the list is growing quite a bit, I want to divide it up so people can browse by the first letter of the business' name. I still want to keep the three column layout for each letter but I'm not sure how to do it.

Any ideas?

[cameraman]

Use the LIKE operator in your select statement, for example:
SELECT * FROM thetable WHERE businessname LIKE 'A%'
will select all records whose businessname starts with A.

[omoutop]

I don't think thats the question....

he already has some sort of array with his results...

he just needs to show them alphabetical in his 3 colum table.

example (if i understand corectly):
Letter A
--------------
nameA1--nameA4--nameA7
nameA2--nameA5--nameA8
nameA3--nameA6--nameA9
Letter B
--------------
nameB1--nameB4--nameB7
nameB2--nameB5--nameB8
nameB3--nameB6--nameB9

With cameraman's way, you must create 27 arrays (one for each letter and one for any name witch starts with a number or any other special character)

Perhaps we could come up with a faster and cleaner way to do that, but unfortunatly i can't think of anything right now. Sorry.

[cameraman]

I was visualizing a row of clickable letters across the top of the page, maybe even select unique existing ones out of the table, so that if you don't have any businesses starting with 'q' or 'x' then it's not even shown.
<a href="example.com?beg=a">A</a>
then
if(isset($_GET['beg'])) {
$beg = $_GET['beg'];
$sql = "SELECT * from thetable where businessname like '$beg%'";
.
.
}

If you wanted to show all, just sort them by business name and start a new section when the first letter changes.
right after you get the first record (this is all ad-hoc and I excel at creating syntax errors, so don't shoot me):
$lastletter = substr($businessname,0,1);
echo "<div>\n";

in the retrieval loop:
if(($thisletter = substr($businessname,0,1))!= $lastletter) {
echo "</div>\n<div>\n";
$lastletter = $thisletter;
}
echo $businessname_in_columns;

And a closing </div> after the loop.

[omoutop]

Clickable letters? Not bad...

lets say this:
$letter_array = randge[A,Z];

foreach ($letter_array as $letter_check)
{
// query database like cameraman suggested
if ($found_results>0)
{
$new_letter_array[] = $letter_check
}
}

//now we have an array with all valid letters (companies that start with this letter
foreach ($new_letter_array as $new_letter)
{
//query database like cameraman suggested but for this particular letter and show results your way in 3 colums
}

[Nutter]

But wouldn't that require 27 queries just to figure out what letters are being used plus the query that actually pulls the business names?

How 'bout something like this? Dunno if it would work, but it's worth a try.

SELECT DISTINCT(UCASE(LEFT(field_name,1))) AS letter FROM table ORDER BY letter ASC;

That should give you a set of all the starting letters of business and you could use that to do the A-B-C-D links on top.

[omoutop]

you are correct nutter :)

[justageek]

You could also just add another column that holds just the first letter. Then you could select distinct from that column for the characters that are used and index the column for fast selects. Or you could create a look up table to hold the characters and relate that table to the main table.

JAG