trimming text for a query, and making it efficient

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
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

trimming text for a query, and making it efficient

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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'] : '*').'%');
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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"]}%' ";
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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}%' "; 
}
Post Reply