alphabet search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
allan16
Forum Newbie
Posts: 6
Joined: Mon Feb 16, 2009 10:56 am

alphabet search

Post by allan16 »

Hi,

I am looking to do the following:

I need to have an alphabet search by letter. I need that when a client visits the page cliks on a letter, this action will look for the first letter of a field on the table created on the database. Based on the letter he selected then it would display the results.

Any ideas or where I can try to accomplish this?

Thanks
User avatar
greyhoundcode
Forum Regular
Posts: 613
Joined: Mon Feb 11, 2008 4:22 am

Re: alphabet search

Post by greyhoundcode »

You could use MySQL's SUBSTR() function, something like this (not tested!):

Code: Select all

SELECT * FROM `table` WHERE SUBSTR(`field`,1,1) = '$character';
allan16
Forum Newbie
Posts: 6
Joined: Mon Feb 16, 2009 10:56 am

Re: alphabet search

Post by allan16 »

I have the following:

http://musicaenvivocr.com/artistas.php

This is the code

Code: Select all

<?php
            
            $con = mysql_connect("xxxx","xxxxxxxx","xxxxxxxxx");
            if (!$con)
            {
            die('Could not connect: ' . mysql_error());
            }
            
            $arrList = array_merge(range('A','Z'));
            foreach ($arrList as $value) {
            echo '<a href="http://musicaenvivocr.com/alphabet.php?letter=' . $value . '">' .
            $value . '</a>&nbsp;';
            }
            ?>
it connects fine to the database I have since I dont get any errors.

I dont understand this line

Code: Select all

echo '<a href="http://musicaenvivocr.com/alphabet.php?letter=' . $value . '">' .
To which page is this going? What do I need to do?
allan16
Forum Newbie
Posts: 6
Joined: Mon Feb 16, 2009 10:56 am

Re: alphabet search

Post by allan16 »

I have the current code:

Code: Select all

<?php
                    
            $con = mysql_connect("xxxxxxx","xxxxxxxxx","xxxxxxxxx");
            if (!$con)
            {
            die('Could not connect: ' . mysql_error());
            }
            mysql_select_db("xxxxxxx", $con);
            
            for ($i="A"; $i != "AA"; $i++) echo "<a href=\"artistas2.php?field=$i\"> $i&nbsp; </a>";
            $i=$_REQUEST['field'];
            
            $query = "SELECT * FROM artista where field LIKE '$i%' ";
            
           
           
           
           ?>
go to : http://musicaenvivocr.com/artistas2.php

it shows the alpahabet.

I have a mysql table with a table called artista. There is a field name which is artist_name. I want this to look for the first letter select based on the alphabet showing on the page and display the results of the letter selected.

Thanks
User avatar
greyhoundcode
Forum Regular
Posts: 613
Joined: Mon Feb 11, 2008 4:22 am

Re: alphabet search

Post by greyhoundcode »

allan16 wrote:I dont understand this line

Code: Select all

echo '<a href="http://musicaenvivocr.com/alphabet.php?letter=' . $value . '">' .
To which page is this going?
That's going to your alphabet.php page, which will be able to see what $value contains by looking at the get-data:

Code: Select all

$value = $_GET['letter'];
As always you'd need to take precautions before using $value in a SQL statement.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: alphabet search

Post by VladSun »

greyhoundcode wrote:You could use MySQL's SUBSTR() function, something like this (not tested!):

Code: Select all

SELECT * FROM `table` WHERE SUBSTR(`field`,1,1) = '$character';
This way MySQL is not able to use ANY of your indexies, so it may become a really slow query.
One should use this instead:

Code: Select all

SELECT * FROM `table` WHERE `field` >= 'B' AND `field` < 'C';
(showing records starting with B)

or

Code: Select all

SELECT * FROM `table` WHERE `field` LIKE 'B%';
which will be optimized by the MySQL engine by transforming it into the previous query I've written.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply