[surferboy]

I have been modifying a php script that uses a mysql database to build an address book. The script comes with a search function that allows you to search by first name or last name. I created a new variable called $address_state_home and made a table entry for this. Now, i am trying to allow the user to also search the database by state. Can any PHP gurus out there help me figure out how to incorporate the ability to search by state?

Here is the existing code:

<?php

include ("include/header.inc.php");
include ("include/dbconnect.php");

if ($searchstring)
{
$sql="SELECT * FROM $table WHERE $searchtype LIKE '%$searchstring%' ORDER BY lastname ASC";


$result = mysql_query($sql);
$resultsnumber = mysql_numrows($result);

echo "<TABLE BORDER=0>";
echo "number of results: $resultsnumber";

$alternate = "2";
while ($myrow = mysql_fetch_array($result))
{
$firstname = $myrow["firstname"];
$id = $myrow["id"];
$lastname = $myrow["lastname"];

if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#efefef";
$alternate = "1";
}
echo "<TR bgcolor=$color><TD>$lastname, $firstname</TD>";
echo "<td><a href='view.php?id=$id'>see details</a></td><td><a href='edit.php?id=$id'>edit record</a></td><td><a href='delete.php?id=$id' onClick=\"return confirm('Are you sure?')\">delete record</a></td>";
}
echo "</TR></TABLE>";
echo "<p>Look <a href='az_index.php'>alphabetically</a> or <a href='$PHP_SELF'>search again</a></p>";

}


else
{
?>
<form method="POST" action="<? $PHP_SELF ?>">

<table border="0" cellspacing="2" width="380">
<tr>
<td><h1>Search for</h1></td>
<td><b>Part of address book to search</b></td>
</tr>
<tr>
<td valign="top">
<input type="text" name="searchstring" size="35">
</td>
<td>
<input type="radio" name="searchtype" value="lastname">Lastname<br>
<input type="radio" name="searchtype" value="firstname" checked>Firstname<br>
<input type="radio" name="searchtype" value="address">State<br>
<input type="radio" name="searchtype" value="email">Email</td>
</tr>
<tr><td><input type="submit" value="Submit"></td>
<td>&nbsp;</td></tr>

</table>
</form>
<form method="POST" action="<? $PHP_SELF ?>">

<table border="0" cellspacing="2" width="380">
<tr>
<td>Or show all people with birthdays in selected month:<b></b></td>
</tr>
<tr>
<td valign="top">
<input type="hidden" name="searchtype" value="bmonth">
<select name="searchstring">
<option value="January" selected>January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>
</select>
</td>
</tr>
<tr>
<td>
<input type="submit" value="show">
</td>
</tr>
</table>
</form>
<?
}

#include ("include/footer.inc.php");
?>

[SirCastor]

We need the header and footer code too.

[surferboy]

All that's in there is Things like style sheet and non-essential stuff- I can post it if it helps.

Thanks

[registered_user]

$sql="SELECT * FROM $table WHERE $searchtype LIKE '%$searchstring%' ORDER BY lastname ASC";

translates to :

SELECT everything FROM [the table named $table] WHERE [the search type is $searchtype -- probably something like 'last_name'] matches [wildcard (% is the wildcard character) + [whatever the $searchstring is] + wildcard] and then ORDER the results by lastname in ASCending order.

You'll need to fill in the variables with applicable data.

[SirCastor]

I'd like to look at those two includes up top, to see how the original writer defined the variables.
selecting the "State" radio button should set $searchtype to 'address' which would be a valid column in the table, and it should search just fine. Did you make that change, or was that like that?

[surferboy]

I tinkered little by little and got it to work. I don't want to clog up the airwaves with it, but I'd be happy to post the end result. I'd also be happy to share the entire script- it's a useful little online address utility. I was able to customize it to my needs with minimal php/mysql knowledge (I know enough to be dangerous). The issue was somewhere towards the bottom of the script.

Thanks again for taking the time- again, let me know, I'd be happy to share it all.

[Gavin]

Do you have the whole address in a single address field?
When searching and sorting it is way more useful to have separate fields for all the pieces. This also will make it easier to do things like export your data as a vcard, etc.

street_address
street_address_2 (many addrs have a second or even third line)
city
state
zip
country

My searches usually allow for any of the fields to be searched on all at the same time, and I allow the user the decide the sort field and direction.
The more fields you fill in the more it restrics the found set by adding AND clauses to the query. So you can get all the Smiths in Texas with a D in their first name ordered by zip code.

$sql = "SELECT *
FROM $table
WHERE id > 0 "; // default to get everything here because we might not have anything below.

if($name_first) $sql .= " AND name_first LIKE '%$name_first%' ";
if($name_last) $sql .= " AND name_last LIKE '%$name_last%' ";
if($email) $sql .= " AND email LIKE '%$email%' ";
if($street_address) $sql .= " AND street_address LIKE '%$street_address%' ";
if($street_address_2) $sql .= " AND street_address_2 LIKE '%$street_address_2%' ";
if($city) $sql .= " AND city LIKE '%$city%' ";
if($state) $sql .= " AND state = '$state' ";
if($zip) $sql .= " AND zip = '$zip' ";

$sql .= "ORDER BY $sort_field $sort_direction";

You can reuse the edit form as the search form.

Also, I usually put the search string back into the search box so they can easily change the search without retyping everything.

<input type="text" name="searchstring" size="35" value="<? echo $searchstring; ?>">

have fun