Page 1 of 1

String Search Form using PHP and MySQL

Posted: Thu May 07, 2009 8:40 am
by phphelpseeker
I have this search form to get information from database based on first letter of lastname. When I click on a letter, it should give contact information of all users whose lastname start with that clicked letter. But it is not doing what is should do. It is not giving any results nor throwing any errors. Can anybody please point out the mistake?

Thank you.
Priya

Here is my HTML code:

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<h3>Search  Contacts Details</h3>
</head>
 
<body>   
       
     <p>You may search based on Lastname.</p>
        
     <form  action="UserInformationSearch.php?go"  method="post">   
     <p><a  href="?by=A">A</a> | <a  href="?by=B">B</a> | <a  href="?by=C">C</a> | <a  href="?by=D">D</a> | <a  href="?by=E">E</a> | <a  href="?by=F">F</a> | <a  href="?by=G">G</a> | <a  href="?by=H">H</a> | <a  href="?by=I">I</a> | <a  href="?by=J">J</a> | <a  href="?by=K">K</a> | <a  href="?by=L">L</a> | <a  href="?by=M">M</a> | <a  href="?by=N">N</a> | <a  href="?by=O">O</a> | <a  href="?by=P">P</a> | <a  href="?by=Q">Q</a> | <a  href="?by=R">R</a> | <a  href="?by=S">S</a> | <a  href="?by=T">T</a> | <a  href="?by=U">U</a> | <a  href="?by=V">V</a> | <a  href="?by=W">W</a> | <a  href="?by=X">X</a> | <a  href="?by=Y">Y</a> | <a  href="?by=Z">Z</a></p>    
     </form>
    
  </body> 
</html>
 
Here is my PHP code:

Code: Select all

 
<?php
$db = mysql_connect(localhost, 'username', 'password') or die('Error: ' . mysql_error());
mysql_select_db('DBname') or die('Could not select database');
if(isset($_GET['by'])){   
$letter=$_GET['by']; 
$sql = "SELECT  Firstname, Lastname, Street, City, State, Zipcode, `Primary Phone`, Email FROM Tablename WHERE Lastname LIKE '" . $letter . "%'";
$result=mysql_query($sql); 
while($row=mysql_fetch_array($result)){   
        $firstname =$row['firstname'];   
        $lastname=$row['lastname'];
        $str=$row['str'];
        $city=$row['city'];
        $state=$row['state'];
        $zipcode=$row['zipcode'];
        $phone=$row['phone'];   
        $email=$row['email']; 
//-display  the result of the array   
 echo  "<ul>\n";   
 echo  "<li>" . $firstname . " " . $lastname .  "</li>\n";
 echo  "<li>" . $str . " " . $city . " " . $state . " " . $zipcode .  "</li>\n"; 
 echo  "<li>" . $phone . "</li>\n";   
 echo  "<li>" . "<a href=mailto:" . $email .  ">" . $email . "</a></li>\n";   
 echo  "</ul>";   
}
}else{
echo "No such records found.<br/>";   
die();
}   
?>
 

Re: String Search Form using PHP and MySQL

Posted: Thu May 07, 2009 8:56 am
by jazz090
i dont think theres anything wrong with your code other than the fact that outgoing data is not being escaped. as for why its not working, its becuase mysql LIKE statement is used for searches anywhere in the string not just the first letter. the only solution i can think of at the top of my head would be to select all users and use PHP to filter them i.e. using strpos() == 0:

Code: Select all

if (strpos($user_last_name, $letter) == 0){
   // push user into an array or something like that
}
if you have small number of users that will do but if you have users passing 5000, i would look around for a better solution.

Re: String Search Form using PHP and MySQL

Posted: Thu May 07, 2009 9:01 am
by phphelpseeker
Thank you.

Priya

Re: String Search Form using PHP and MySQL

Posted: Thu May 07, 2009 10:00 am
by phphelpseeker
Is there a Left() function I can use to display lastnames in a database that start with a particular letter? Somebody please help me. I really need help.

Thank you.
Priya

Re: String Search Form using PHP and MySQL

Posted: Thu May 07, 2009 11:44 am
by Benjamin
Don't bump phphelpseeker
Forum Rules 1 1.1 4 wrote: 4. All users of any level are restricted from bumping (as defined here) any given thread within twenty-four (24) hours of its last post. Non-trivial posts are not considered bumping. A bump post found in violation will be deleted, and you may or may not receive a warning. Persons bumping excessively be considered as spammers and dealt with accordingly.