Page 1 of 1

alphabet search

Posted: Thu Feb 19, 2009 1:03 pm
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

Re: alphabet search

Posted: Thu Feb 19, 2009 1:18 pm
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';

Re: alphabet search

Posted: Thu Feb 19, 2009 1:40 pm
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?

Re: alphabet search

Posted: Thu Feb 19, 2009 3:31 pm
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

Re: alphabet search

Posted: Fri Feb 20, 2009 1:51 pm
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.

Re: alphabet search

Posted: Fri Feb 20, 2009 4:58 pm
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.