Page 1 of 1

Search SQL by first letter

Posted: Mon Feb 21, 2005 9:34 am
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! :)

Posted: Mon Feb 21, 2005 9:39 am
by feyd

Code: Select all

SELECT * FROM table WHERE SUBSTRING(`field`, 0, 1) = 'b'
I think..

Posted: Mon Feb 21, 2005 9:40 am
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'";

THanks but...

Posted: Mon Feb 21, 2005 11:59 am
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,

Posted: Mon Feb 21, 2005 12:02 pm
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.

ok - SOLVED

Posted: Mon Feb 21, 2005 12:18 pm
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