Output based on alphabetical order instead of id

Anon

During my most recent project I've run into something that I don't know how to handle yet. I have a database which contains four categories: id, cd_artist, cd_title, and cd_tracks. Since I'm lazy and hate popping open a mysql session on the console I wanted to create a web interface for adding/editing/updating/deleting entries.

I've done this before with a script for a journal system, and it works great. However the cd database presents something of a problem. On the left I like to print out links so I can click them and have the info load into the forms on the right, but with ~200 cds currently in my database having it print out all 200 just to edit one is a waste. In my journal script I can just use the id field and print out the last 5 (or 10, or 15, etc..) and it's no big deal since the newest entry has the largest id and that's how they should be grouped. In the case of the cd database though, it messes things up.

The initial dump has everything in the correct alphabetical order, so the numbers increment along with that. But as you start adding new cd's to the database, the numbering/alpha order gets out of whack (duh). So what I wanted to do is have the script print out something like this:

a b c d e
f g h i j
(etc..)

..when the page is loaded with no cd id number on the url. That way you could click a letter, say C, and only the cds by bands whose name begins with the letter C would get displayed in that list. What's a good way of doing this?

Anon

I'm not quite sure what exactly you are asking...

If you want the cd's to list alphabetically by artist then add "order by cd_artist" to your querry string when retrieving records from the database. If you want to only select artists with name beginning with "D" then:

Select * from cd_table where (cd_artist > 'c') and (cd_artist < 'f') order by cd_artist

-- Rich Rijnders
-- Irvine, CA US

Anon

The only problem with that solution is that I end up with a massive script full of 26 select statements. I was hoping for something more compact.

Anon

I don't know if it is more "compact", but you could pass in the letter of the artists you are looking for, say, as $search below:

<?php

$seed = "abcdefghijklmnopqrstuvwxyz";

$lookup = strpos($seed, strtolower($search));

$sql = "Select * from cdTable where ";

if ($lookup > 0) {
$lower = substr($seed, $lookup-1, 1);
$sql .= "(cd_author > '".$lower."')";
}

if (($lookup > 0) && ($lookup < 25)) {
$sql .= " and ";
}

if ($lookup < 25) {
$upper = substr($seed, $lookup+1, 1);
$sql .= "(cd_author < '".$upper."')";
}

$sql .= " order by cd_author";

// execute querry

You might, additionally, want to substring the first alpha character of $search in case someone tries to break you program by passing a string or a number into it.

-- Rich Rijnders
-- Irvine, CA US

Anon

function LookupBy($character) {

// $character must contain the character you want to see results from, like 'a', 'b', 'c', etc.

$lower_char = strtolower($character);
$up_char = strtoupper($character);

$query = "select * from cd_table where substring(cd_artist,1,1)='$lower_char' or substring(cd_artist,1,1)='$up_char'";

// Execute query and do sumpin' with the result

}

That should do the trick.

Greetz,

Vincent Driessen
Venlo, The Netherlands

Anon

Nifty, this works perfectly. Thanks.

Anon

...just be careful if you are using the substring method. Substring causes the DBMS to read the entire table in order to substring the field and put the record in the result set if necessary. If your database is large and your querries numerous, this could be a factor, but since you are only using it on admin functions you should be fine.

-- Rich Rijnders
-- Irvine, CA US