Page 1 of 1

trimming text for a query, and making it efficient

Posted: Sun Aug 21, 2005 8:12 am
by robster
There's more to it than the title.

Basically I have a list in my database, it's a client list. It's growing rapidly and in order to make more sense of it I've created (am creating) a way to click A-Z and get back all clients whose name starts with whatever you click.

ie: click on 'S' and all clients whose name starts with S comes back in the list.


I was going to do it this way, but I think it's not efficient:

Code: Select all

$letter = $_GET['letter'];

loop thru database.
{
  if ($string{0} == $letter)  //using substring to find first letter
  {
    show the name
  }
}
all well and good, but I'd rather trim the fat in the SQL query if it can be done... can it be done or is that it, searching through the WHOLE database in a loop?!

Thanks for your time :)

Rob

Posted: Sun Aug 21, 2005 8:27 am
by John Cartwright
I see 2 possible solutions here, LIKE clause or SUBSTRING..

Code: Select all

$sql = 'SELECT * FROM `people` WHERE `lastname` LIKE  \''.(isset($_GET['letter']) ? $_GET['letter'] : '*').'%');

Posted: Sun Aug 21, 2005 11:13 am
by raghavan20
If you want to create an alphabetical listing, this is how I did

I do the same for all the characters. either you can type manually or display the same using PHP arrays and a for loop

Code: Select all

<a href="viewClient.php?startChar=A">A</a><a href="viewClient.php?startChar=B">B</a>

Code: Select all

if (isset($_GET["startChar"])){
$query = "select * from Clients_tbl where `LastName` like '{$_GET["startChar"]}%' ";
}

Posted: Sun Aug 21, 2005 11:18 am
by feyd
beware of SQL injection threats with that code. ;)

If you want to know more about SQL injection: viewtopic.php?t=37095 may be of interest.

Posted: Sun Aug 21, 2005 11:22 am
by raghavan20
PHP:
if (isset($_GET["startChar"])){
$query = "select * from Clients_tbl where `LastName` like '{$_GET["startChar"]}%' ";
}

Code: Select all

if (isset($_GET["startChar"])){ 
$startChar = $_GET["startChar"];
if (!preg_match("/^[a-zA-Z]{1}$/", $startChar)){//if its not within the range of a-z, A-Z and only one char, make it A
$startChar = "A";
}
$query = "select * from Clients_tbl where `LastName` like '{$startChar}%' "; 
}