Page 1 of 1

Query Strings and the isset command

Posted: Fri Feb 25, 2011 8:38 pm
by Joxer
Hello All,

I have a question concerning how to write a query string that will allow a search on several form input boxes where any of the input boxes may be empty or null except one. (There always has to be at least a single search term.)

here is some of the code I have thus far:

Code: Select all

// variable string list

$lastname = $_POST['lastname'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$specialty = $_POST['specialty'];

// this is the query string section

$sql = mysql_query("select * from doctors where Specialty like '%$specialty%' and State like '%$state%' and City like '%$city%' ");

while ($row = mysql_fetch_array($sql)){
	echo 'ID: '.$row['ID'];
	echo '| Dr. '.$row['FirstName'];
	echo '  '.$row['LastName'];
	echo ', '.$row['City'];
	echo '  '.$row['State'];
	echo '. '.$row['Zip'];
	echo ', '.$row['Specialty'];
	echo ' ';
	print( '<a href="http://www.cybersoapbox.net/bernard/sunday/new2/results.php">   Click Here For More Information</a>' );
                  echo '<br/><br/>';
	}
You can view the current page Here

To recap: I want to be able to search on any single or a combination of the fields. As it is right now I can do a search on only Specialty and return results. I can search on Specialty and State and return results, but i can't search on only state, or only city, or only any of the other fields except Specialty, and return results.

I have been trying to do If ... Else statements and I have even tried isset statements but dont know how to use them very well so i failed. I think isset or if..else statments are the way to go though. So could someone assist me with a little idea on how to do this?

Thanks in advance to any and all support.

Re: Query Strings and the isset command

Posted: Sat Feb 26, 2011 11:37 am
by s.dot
You will have to build your SQL query before executing it using ifs

Psuedo-code:

Code: Select all

If (all fields are empty)
{
    error('fill in at least one field');
    exit;
}

$sql = "SELECT * FROM doctors WHERE 1 ";

if (!empty($_POST['specialty']))
{
    $sql .= " AND specialty LIKE '%$specialty%'  ";
}

if (!empty($_POST['state']))
{
    $sql .= "AND state LIKE '%$state%' ";
}

//etc
//echo $sql;
//$res = mysql_query($sql)

Re: Query Strings and the isset command

Posted: Sat Feb 26, 2011 2:13 pm
by Joxer
Thank you s.dot for your reply.
I am still not getting the results though.

Now after I click the submit button the sql search string is echoed under the table.

Example when I search on Specialty: Surgery and State: Florida the results I get are -
SELECT * FROM doctors WHERE 1 AND specialty LIKE '%Surgery%' AND state LIKE '%FL%'

It should return these results:
ID: 30| Dr. Michael Dogali, Clearwater FL. 33756, Neurological Surgery Click Here For More Information
ID: 34| Dr. George Pope, Winter Park FL. 32789, Plastic Surgery Click Here For More Information
ID: 35| Dr. Kim Koger, Jupiter FL. 33458, Plastic Surgery Click Here For More Information
ID: 36| Dr. Vivian Hernandez, Boca Raton FL. 33431, Plastic Surgery Click Here For More Information

example #2 - a search on just State: Kansas it should return these results:
ID: 57| Dr. Ronald Holweger, Hays KS. 67601, Ophthalmology Click Here For More Information
ID: 58| Dr. William Clifford, Garden City KS. 67846, Ophthalmology Click Here For More Information
ID: 59| Dr. Howard Rosenthal, Leawood KS. 66209, Orthopedics Click Here For More Information

and lastly a search on City: Chicago and Zip Code: 60614 it should return these results:
ID: 52| Dr. Betsy Pepper, Chicago IL. 60614, Emergency Medicine Click Here For More Information
ID: 53| Dr. Frederick Freitag, Chicago IL. 60614, Other Click Here For More Information


So what am I missing here?

Click here to see revised code in action or here to see original code in action


here is the revised code.

Code: Select all

// variable string list

$lastname = $_POST['lastname'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$specialty = $_POST['specialty'];
$res = mysql_query($sql); // defined the new string $res

// this is the query string section
$sql = ("SELECT * FROM doctors WHERE 1 ");

//this is specialty
if (!empty($_POST['specialty']))
{
    $sql .= " AND specialty LIKE '%$specialty%' ";
}
//this is state
if (!empty($_POST['state']))
{
    $sql .= "AND state LIKE '%$state%' ";
}
//this is city
if (!empty($_POST['city']))
{
    $sql .= "AND city LIKE '%$city%' ";
}
//this is last name
if (!empty($_POST['lastname']))
{
    $sql .= "AND lastname LIKE '%$lastname%' ";
}
//this is zipcode
if (!empty($_POST['zipcode']))
{
    $sql .= "AND zipcode LIKE '%$zipcode%' ";
}

//$sql = mysql_query("select * from doctors where Specialty like '%$specialty%' and State like '%$state%' and City like '%$city%' and lastname like '%$lastname' and zipcode like '%$zipcode' ");

echo $sql;
$res = mysql_query($sql);


Re: Query Strings and the isset command

Posted: Sat Feb 26, 2011 2:27 pm
by Joxer
would an AND OR statement work better for what I am trying to accomplish? If so how would I do that?

Re: Query Strings and the isset command

Posted: Sat Feb 26, 2011 4:05 pm
by AbraCadaver
I would do something lke this (not tested):

Isolate the fields to be searched in your form using an array like:

[text]<input type ="text" name="search[lastname]">
<input type ="text" name="search[firstname]">[/text]
Then:

Code: Select all

foreach(array_filter($_POST['search']) as $key => $val) {
    $condition[] = "`" . mysql_real_escape_string($key) . "` LIKE '%" .mysql_real_escape_string($val) . "%'";
}
if(!empty($condition)) {
    $condition = implode(" AND ", $condition);
} else {
    die('No search criteria was provided');
}
$sql = mysql_query("SELECT * FROM doctors WHERE $condition");
Just a rough example, needs more error checking etc...

Re: Query Strings and the isset command

Posted: Sat Feb 26, 2011 4:15 pm
by Joxer
Hello AbraCadaver

I don't quite follow your example. Can you please elaborate?
Thanks.

Re: Query Strings and the isset command

Posted: Sun Feb 27, 2011 12:25 pm
by Joxer
Can this be done with the isset satement?

Re: Query Strings and the isset command

Posted: Sun Feb 27, 2011 5:17 pm
by AbraCadaver
Joxer wrote:Hello AbraCadaver

I don't quite follow your example. Can you please elaborate?
Thanks.
1. In the form build an array of search inputs so that they are separate from other inputs, submit button etc.
2. array_filter() will get rid of empty values in the search array
3. Loop through the array and for each input build a like condition ( `lastname` LIKE '%something%' )
4. If the condition array is not empty ( one or more inputs had a value) join the array elements with AND in between (`lastname` LIKE '%something%' AND `firstname` LIKE '%somethingelse%' )
5. Use the condition that was built as the where clause in the query

Re: Query Strings and the isset command

Posted: Sun Feb 27, 2011 6:13 pm
by Joxer
AbraCadaver wrote:
Joxer wrote:Hello AbraCadaver

I don't quite follow your example. Can you please elaborate?
Thanks.
1. In the form build an array of search inputs so that they are separate from other inputs, submit button etc.
2. array_filter() will get rid of empty values in the search array
3. Loop through the array and for each input build a like condition ( `lastname` LIKE '%something%' )
4. If the condition array is not empty ( one or more inputs had a value) join the array elements with AND in between (`lastname` LIKE '%something%' AND `firstname` LIKE '%somethingelse%' )
5. Use the condition that was built as the where clause in the query
Ok here is my form (it's in a table right now but you should be able to see how it is laid out.) I also shortened the 'option values since it is no sense in adding the entire list for this example.

Code: Select all

 <table bgcolor="#FFCACA" border="1" bordercolor="#0000FF">
 <tr>
 <td>
<form id='myform' action='search.php' method='POST'>

<label for='specialty' >
             <font color="#0000FF" size="+1" face="Verdana, Geneva, sans-serif">Specialty:.........</font></label>   
	<select name="specialty">
		<option value="Select">Select</option>
		<option value="Allergy & Immunology">Allergy & Immunology</option>
		<option value="Anesthesiology">Anesthesiology</option>
		<option value="Cardiology">Cardiology</option>
	</select><br/><p> 
	<input type='text' name='specialty' id='specialty' maxlength="75" /> -->

<label for='lastname' >
               <font color="#0000FF" size="+1" face="Verdana, Geneva, sans-serif">Last Name:.......</font></label>
	<input type='text' name='lastname' id='lastname' maxlength="75" /><br/><p>

<label for='city' >
            <font color="#0000FF" size="+1" face="Verdana, Geneva, sans-serif">City:................</font></label>
	<input type='text' name='city' id='city' maxlength="75" /><br/><p>

<label for='state' >
            <font color="#0000FF" size="+1" face="Verdana, Geneva, sans-serif">State:..............</font></label>
	<select name="state">
		<option value="">Select...</option>
    		<option value="AK">Alaska</option>
		<option value="AL">Alabamma</option>
		<option value="AR">Arkansas</option>
		<option value="AZ">Arizona</option>
		<option value="CA">California</option>
		<option value="CO">Colorado</option>
		<option value="CT">Connecticut</option>		
   	</select><br/><p>

<label for='zipcode' >
            <font color="#0000FF" size="+1" face="Verdana, Geneva, sans-serif">Zip Code:.........</font></label>
	<input type='text' name='zipcode' id='zipcode' maxlength="15" /><br/><p></p>
</td>
</tr>
<tr>
<td>

<input type='submit' name='submit' value='Submit' />
<input type="button" value="New Search" onClick="document.location.href('http://www.cybersoapbox.net/bernard/sunday/new2/search.php');">
</td>
</tr>
</table>
after the end of the table comes the actual PHP code here below.

Code: Select all

<?php
//conection strings here
/*

*/
// variable string list

$lastname = $_POST['lastname'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$specialty = $_POST['specialty'];
$res = mysql_query($sql); // defined the new string $res

// this is the query string section
$sql = ("SELECT * FROM doctors WHERE 1 ");

//this is specialty
if (!empty($_POST['specialty']))
{
    $sql .= " AND specialty LIKE '%$specialty%' ";
}
//this is state
if (!empty($_POST['state']))
{
    $sql .= "AND state LIKE '%$state%' ";
}
//this is city
if (!empty($_POST['city']))
{
    $sql .= "AND city LIKE '%$city%' ";
}
//this is last name
if (!empty($_POST['lastname']))
{
    $sql .= "AND lastname LIKE '%$lastname%' ";
}
//this is zipcode
if (!empty($_POST['zipcode']))
{
    $sql .= "AND zipcode LIKE '%$zipcode%' ";
}

//$sql = mysql_query("select * from doctors where Specialty like '%$specialty%' and State like '%$state%' and City like '%$city%' and lastname like '%$lastname' and zipcode like '%$zipcode' ");

echo $sql;
$res = mysql_query($sql);


?>

As you can see I have something wrong because the code as I have it written is broken. Where am I messing this up?

Re: Query Strings and the isset command

Posted: Mon Feb 28, 2011 1:58 am
by s.dot
You have to loop through your result $res.

Code: Select all

while ($arr = mysql_fetch_assoc($res))
{
    print_r($arr);
}