Search SQL by first letter

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Search SQL by first letter

Post by gjb79 »

Hi,

I'm not very familure with splitting text strings, and though I have searched all over for a solution, I cannot seem to find one to fit what I am attempting.

I have a my_sql search query that looks like this:

Code: Select all

mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID') ORDER BY `CNameL` ASC";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);
When This page is visited there are three URL variables taking place.
BookID, ChapterID, and LetterID

Currently everything works great with only the BookID and ChapterID in place. Now I want to add functionality with the LetterID variable.

I want to display only the results where the first letter of table character column CNameL begins with the letter LetterID

for example, if this is a list in the database

Abbots
Ansle
Axle
Badrock
Bungstrum
Cunnel

and I sort it with LetterID = B
the result should display only
Badrock and Bungstrum.

What I feel I need to do is some how take the full list from the database, capture the first letter of that column and then pull entries from that list.

Am I close?

Thanks for any help you can give! :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT * FROM table WHERE SUBSTRING(`field`, 0, 1) = 'b'
I think..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

some possibilities...

Code: Select all

$query = "select * from foo where columm like '$letter%'";
$query = "select * from foo where substring(column, 1, 1) = '$letter'";
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

THanks but...

Post by gjb79 »

Hey thanks for the quick reply,

I tried this and did not get any change. it is still displaying all the entries from the database rather than only the ones beginning with the letter I enter.

Code: Select all

mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE (BookID < '$GBookID') OR (BookID = '$GBookID' AND ChapterID <= '$GChapterID') AND CNameL like '$LetterID%' ORDER BY `CNameL` ASC";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);


I tried it with both AND and OR in the WHERE field with the same results.

I also tried

Code: Select all

$query_Characters = "SELECT * FROM `character` WHERE (BookID < '$GBookID') OR (BookID = '$GBookID' AND ChapterID <= '$GChapterID') AND SUBSTRING(`CNameL`, 0, 1) = '$LetterID' ORDER BY `CNameL` ASC";
With no result.

Is there something wrong, perhaps, with the way I laid this code out?

Thanks,
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Your query options are probably cancelling out the 'first letter' part.
To test just run:

Code: Select all

$query_Characters = "SELECT * FROM `character` WHERE CNameL like '$LetterID%' ORDER BY `CNameL`";
If that works then your original query is 'too greedy' and the first letter isn't having any effect.
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

ok - SOLVED

Post by gjb79 »

Ok, well that works,

Odd, I tried what you suggested,
then I plugged it in as the following:

Code: Select all

$query_Characters = "SELECT * FROM `character` WHERE CNameL like '$LetterID%' AND (BookID < '$GBookID') OR (BookID = '$GBookID' AND ChapterID <= '$GChapterID') ORDER BY `CNameL` ASC";
And everything appeared to be working, I had one "A" repeatedly appear at the top of the "B" section, but then I refreshed again and everything is working as it should be.

Thanks so much for your help!!! :D
Post Reply