Limiting a phone number search PHP/MySQL

Beginners - Learning PHP
#1

I have a database that has customer first and last names as well as phone numbers, the problem is when anyone just types in the area code it will show all customers with that same area code.

how do I write the code that tells the user they HAVE to put in all 10 digits of their phone number or else the SQL will die?

SUB-QUESTION: The database phone numbers are all hyphenated like this - 777-555-1234. What code can I use to trim or boolean those hyphens out so that it wouldn’t matter if they put hyphens in or not.

Here is my code for those interested in helping out.

<?php
/**************************************************************************************
 * Main Search Page - search.php
 * Author: Your Name <[email protected]>
 * This file searches the database
 **************************************************************************************/
 
//Get variables from config.php to connect to mysql server
require 'config.php';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

//search variable = data in search box or url
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/s+/', ' ', $search);


//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);

//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i<count($keywords); $i++) {
$query = "SELECT * FROM Customer " .
"WHERE Phone LIKE '%".$keywords[$i]."%'".
" ORDER BY LastName";
}

//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
}
if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}

echo "<html>";
echo "<head>";
echo "<title>Your Title Here</title>";
echo "<link rel="stylesheet" type="text/css" href="style.css" />";
echo "</head>";
echo "<body onLoad="self.focus();document.searchform.search.focus()">";
echo "<center>";
echo "<br /><form name="searchform" method="GET" action="search.php">";
echo "<input type="text" name="search" size="20" TABINDEX="1" />";
echo " <input type="submit" value="Search" />";
echo "</form>";
//If search variable is null do nothing, else print it.
if ($search == NULL) {
} else {
echo "You searched for <b><FONT COLOR="blue">";
foreach($keywords as $value) {
   print "$value ";
}
echo "</font></b>";
}
echo "<p> </p><br />";
echo "</center>";

//If users doesn't enter anything into search box tell them to.
if ($search == NULL){
echo "<center><b><FONT COLOR="red">Please enter a search parameter to continue.</font></b><br /></center>";
} elseif ($search == '%'){
echo "<center><b><FONT COLOR="red">Please enter a search parameter to continue.</font></b><br /></center>";
//If no results are returned print it
} elseif ($count <= 0){
echo "<center><b><FONT COLOR="red">Your query returned no results from the database.</font></b><br /></center>";
//ELSE print the data in a table
} else {
//Table header
echo "<center><table width="600px" id="search" bgcolor="#AAAAAA">";
echo "<tr>";
echo "<td style="table-layout:fixed;width:300px"><b>First Name</b></td>";
echo "<td style="table-layout:fixed;width:300px"><b>Last Name</b></td>";
echo "<td style="table-layout:fixed;width:300px"><b>Points</b></td>";
echo "<tr>";
echo "</table></center>";

//Colors for alternation of row color on results table
$color1 = "ffffff";
$color2 = "666666";
//While there are rows, print it.
while($row = mysql_fetch_array($result))
{
//Row color alternates for each row
$row_color = ($row_count % 2) ? $color1 : $color2;

//table background color = row_color variable
echo "<center><table width="600px" bgcolor=".$row_color.">";
echo "<tr>";
echo "<td style="table-layout:fixed;width:300px">".$row['CustName']."</td>";
echo "<td style="table-layout:fixed;width:300px">".$row['LastName']."</td>";
echo "<td style="table-layout:fixed;width:300px">".$row['CustLoyaltyPoints']."</td>";
echo "</tr>";
echo "</table></center>";
$row_count++;
//end while
}
//end if
}

echo "</body>";
echo "</html>";
if ($search == NULL or $search == '%') {
} else {
//clear memory
mysql_free_result($result);
}
?>

Thank you!

#2 
$query = "SELECT * FROM Customer " .
"WHERE Phone LIKE '%".$keywords[$i]."%'".
" ORDER BY LastName";

Do you know what this query does? Why are you using LIKE?

For your subquestion: why are you storing the phone numbers in hyphenated mode?

#3

I am sort of a newbie and have been trying to learn PHP and SQL for about a month and I am doing all that I can to translate the codes.

The only problem I have with this SQL code is that whenever you type in a single digit number all the phone numbers with that digit pop up. I have been making out with this code:

if ($search == "815-" or $search == "815" or $search == "630-" or $search == "630" or $search == "708-" or $search == "708" or $search == "312-" or $search == "312" or $search == "1"){
echo "<center><b><FONT COLOR="red">Please enter a search parameter to continue.</font></b><br /></center>";
}

But it seems like its way to much to be doing to every single area code. If there was a range code someone can show me that would knock out querying the whole phone number database, that would be fine.

Basically if you type in the number 1, all the phone numbers show up. I just want the users to type in their phone number to bring up their information. I don’t want them to type in an area code and show all the customers in that area code.

The reason why the numbers are stored in hyphenated mode is because the program that we are using is storing them like that. I can store them like 2223331234, just thought there was a simpler way to do it through php.

#4

You can check out the user’s search string before using it in a query. If you can detect that the user entered 7 digits, you won’t need to use LIKE. If they enter 3 digits, you can give them a message explaining that searching for area number is disabled (or something). If they entered gibberish, give a different message.

The reason why I’m asking about the hyphenation is because it would make your life as a PHP programmer that much easier to store the phone numbers as 1234567 (or 1234567890). You could simply use an OR option in the WHERE clause of your SQL query:

$search_array[0] = substr($search, 0, 3);
$search_array[1] = substr($search, 3, 3);
$search_array[2] = substr($search, 6);
$search2 = implode("-", $search_array);

$query = "
SELECT
  *
FROM
  Customer
WHERE
  Phone = '".$search."'
OR
  Phone = '".$search2."'
ORDER BY
  LastName
";
#5

Thank you very my Zyppora! This is exactly where I was trying to goto! I trying to learn this all on my own, because I am going to school for graphic design, and I love to design websites, it is just that the backend stuff is weary to me. But I am learning.

Thank you so much again!

LANG LEVEND NEDERLAND!

:)
#6

Just trying to help :smiley:

Sponsor our Newsletter | Privacy Policy | Terms of Service