Query Strings and the isset command

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
Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Query Strings and the isset command

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Query Strings and the isset command

Post 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)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Re: Query Strings and the isset command

Post 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);

Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Re: Query Strings and the isset command

Post by Joxer »

would an AND OR statement work better for what I am trying to accomplish? If so how would I do that?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Query Strings and the isset command

Post 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...
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Re: Query Strings and the isset command

Post by Joxer »

Hello AbraCadaver

I don't quite follow your example. Can you please elaborate?
Thanks.
Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Re: Query Strings and the isset command

Post by Joxer »

Can this be done with the isset satement?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Query Strings and the isset command

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Joxer
Forum Newbie
Posts: 7
Joined: Wed Feb 23, 2011 11:12 pm

Re: Query Strings and the isset command

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Query Strings and the isset command

Post by s.dot »

You have to loop through your result $res.

Code: Select all

while ($arr = mysql_fetch_assoc($res))
{
    print_r($arr);
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply