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> ';
}
?>
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 </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:
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.