MySQL Query to Search City Name by First Letter

[daveh33]

I have a mysql database with a table which contains two fields -areaid,area

 

I am trying to return a set of results in a while statement which will get any records in my table which start with the letters A,B,C,D,E

 

This is my code..

 

$start="A";
$end="E";
$query=mysql_query("SELECT * FROM mytable WHERE area LIKE 'A%%%'");
while ($data=mysql_fetch_array($query)) {
$areaid=$data['areaid'];
$area=$data['area'];
echo "<a href=\"area.php?id=$areaid\">$area</a><br /><br />";
}

 

I know my query isn't quite right, but im not sure if this is the best way to do this?

 

Any help is appreciated

[trq]

SELECT areaid, area FROM mytable WHERE area LIKE 'A%' || LIKE 'B%' || LIKE 'C%' || LIKE 'D%'  || LIKE 'E%' ORDER BY area

[daveh33]

Thats great- but look at my actual code

 

$list=$_GET['list'];
list($start,$end) = explode("-", $list);

 

This produces the

$start="A";

$end="E";

 

how can I use variables in that statement?

 

[uniflare]

do the number of letters and actual characters change or are they fixed?

[daveh33]

the $start & $end will either be: -

A

E

 

F

J

 

K

P

 

Q

Z

[uniflare]

ok you will need to form a where query string within a loop (i cannot work out an sql syntax for this);

 

eg:

 

<?php

$fieldname = "area";
$tablename = "mytable";

// alphabet string of characters for character position reference
$alphabet = "abcdefghijklmnopqrstuvwxyz";

$start="A";
$end="D";

// get alphabet charcter pos (start)
$substr_a = strpos($alphabet,strtolower($start));

// get alphabet character position (end)
$substr_b = strpos($alphabet,strtolower($end))+1;

// Create array of letters by getting all the letter from $start to $end and splitting the string every 1 character.
$Queries = str_split(substr($alphabet,$substr_a,$substr_b-$substr_a),1);

// Set count to save execution time by not putting in for loop argument
$x = count($Queries);

// Preset $where variable
$where = array();

// Loop to loop-thru each character in the $Queries array
For($i=0;$i<$x;$i++){

// Each loop add a where query to the array $where
$where[] = "UCASE($fieldname) LIKE '".strtoupper($Queries[$i])."%'";
}

// Implode the query array ($where) and into a string and insert " AND " between each array item in the string.
$where = "WHERE ".implode(" OR ",$where);

// resulting mysql statement
echo("SELECT * FROM $tablename ".$where);
?>

 

hope this helps,