[SOLVED] Filter

danjoe_15 · January 27, 2009

Here is an example of what I am trying to do. With this code I could find say anyone from the state of Califorina with the last name of smith.

$c = dbQuery("SELECT * FROM customers WHERE last_name= %1 AND states=%2", $_POST['LastName'], $_POST['State']);
$customer = mysql_fetch_assoc($results[$c]);

However I am lost as to how to rewrite this to search for say anyone with the last name of Smith, or anyone from the state of California.

I'm nearly certain that this has somthing to do with checking to see if a field is null but I do not understand how to do this either.

premiso · January 27, 2009

$c = dbQuery("SELECT * FROM customers WHERE last_name= %1 OR states=%2", $_POST['LastName'], $_POST['State']);

You said it, OR.

Oh and the <code> replace the < with [ and the > with ]

danjoe_15 · January 27, 2009

however what if they enter both state and last_name. I want to output all people with that last name AND from that state

premiso · January 27, 2009

Then you need to do some query creating:

<?php
$where = array();
$where[] = isset($_POST['LastName']) ? " `last_name` = '". mysql_real_escape_string($_POST['LastName']) . "' ":null;
$where[] = isset($_POST['State']) ? " `states` = '" . mysql_real_escape_string($_POST['State']) . "' ":null;

foreach ($where as $index => $val) {
if (is_null($where[$index]))
	unset($where[$index]);
}

$where = implode(' AND ', $where);
$c = dbQuery("SELECT * FROM customers WHERE %1", $where);
?>

That will generate a dynamic where and you can easily add more conditions like shown above.

danjoe_15 · January 27, 2009

That does not appear to have worked. Of course I may have done something incorrectly.

premiso · January 27, 2009

<?php
$where = array();
$_POST['LastName'] = "bob";
$where[] = isset($_POST['LastName']) ? " `last_name` = '". mysql_real_escape_string($_POST['LastName']) . "' ":null;
$where[] = isset($_POST['State']) ? " `states` = '" . mysql_real_escape_string($_POST['State']) . "' ":null;

foreach ($where as $index => $val) {
if (is_null($where[$index]))
	unset($where[$index]);
}

$where = implode(' AND ', $where);
echo $where . "<br />";
$c = "SELECT * FROM customers WHERE $where";

echo $c;
die();
?>

When I run that this is what is returned:

`last_name` = 'bob'
SELECT * FROM customers WHERE `last_name` = 'bob'

So I am not sure why yours would not work. Can you elaborate on how it did not work?

danjoe_15 · January 27, 2009

This is what the beginning of my code looks like after the additions which you recommended:

<?php
include 'common.php';
dbConnect();
<?php
$where = array();
$where[] = isset($_POST['LastName']) ? " `last_name` = '". mysql_real_escape_string($_POST['LastName']) . "' ":null;
$where[] = isset($_POST['State']) ? " `states` = '" . mysql_real_escape_string($_POST['State']) . "' ":null;

foreach ($where as $index => $val) 
{
if (is_null($where[$index]))
	unset($where[$index]);
}

$where = implode(' AND ', $where);
$c = dbQuery("SELECT * FROM customers WHERE %1", $where);
$customer = mysql_fetch_assoc($results[$c]);
//print_r($customer)
?>

This is where one of the many places I am trying to output a value

<td colspan=3><input type="text" readonly=true style="width:200px" name="FirstName" value="<? echo $customer['first_name']; ?>"/>

currently the page is not loading properly and nothing is being displayed.

premiso · January 27, 2009

You have a duplicate <?php in that code.

<?php
include 'common.php';
dbConnect();
// <?php remove this php here and it should work.

danjoe_15 · January 27, 2009

The form is now displaying but with no data in the text boxes however.

premiso · January 27, 2009

Do some debugging.

$where = implode(' AND ', $where);
echo $where; // echo where here.
$c = dbQuery("SELECT * FROM customers WHERE %1", $where);
$customer = mysql_fetch_assoc($results[$c]);

See that the WHERE is generating like it should. Also check to see if there is an error like this:

$where = implode(' AND ', $where);
$c = dbQuery("SELECT * FROM customers WHERE %1", $where);
if (!empty(mysql_error())) {
    die('SQL Error: ' . mysql_error() . ' USING WHERE CLAUSE: ' . $where); 
}
$customer = mysql_fetch_assoc($results[$c]);

And see where that gets you. Also verify that using the WHERE clause that is printed that there is data in the DB with those parameters set. Also change the <? to <?php as short tags may be the problem:

<td colspan=3><input type="text" readonly=true style="width:200px" name="FirstName" value="<?php echo $customer['first_name']; ?>"/>

danjoe_15 · January 27, 2009

echo $where outputs the values entered on the previous page as expected.

when i add

if (!empty(mysql_error())) {
    die('SQL Error: ' . mysql_error() . ' USING WHERE CLAUSE: ' . $where); 
}

the form does not load properly.

I am certain that there is data matching the given criteria, and I have used short tags many times throughout the development of these web pages.

premiso · January 27, 2009

I bet the issue lies within the dbQuery function and how it is handling the passed in data.

I do not know how it works, but try this and see if this works:

$c = mysql_query("SELECT * FROM customers WHERE $where");

My spider senses says it will because I bet the %1 assumes that there is a colname = %1 which it then adds single quotes which would cause a mysql error.

danjoe_15 · January 27, 2009

Blank text boxes again.

premiso · January 27, 2009

Post the full script and I will take a look. Other than that I do not know. The query works with my data on my server, why it does not work on yours I have no clue.

danjoe_15 · January 27, 2009

Filter.php:

<?php
include 'common.php';
?>

<?dbConnect();?>
<head>
<title>Filter</title>
</head>
<html style="Background-color:BBBBBB">
	<body>
		<td><form action="Filtered.php" method="POST">
		<table>
			<tr>
				<td>Customer ID
				<input type="text" name="Customer_id" value=""/></td>
			</tr>
			<tr>
				<td style="width:200px">First Name</td>
				<td>Last Name</td>
				<td>&nbsp</td>
				<td>Status</td>
			</tr>
			<tr>
				<td colspan=3><input type="text" style="width:200px" name="FirstName" value=""/>
				<input type="text" style="width:280px" name="LastName" value=""/></td>
					<td colspan=2><select name="StatusID">
						<?
							$select_print = "<option> </option>";
							$result=dbQuery("SELECT * FROM customer_status ORDER BY status_name");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['status_id'].'"/>'.$row['status_name'].'</option>';
							}

							echo selectOption($select_print, $data['status_id']);
						?>
					</select></td>
			</tr>
			<tr>
				<td>Care of </td>
				<td colspan=2>&nbsp</td>
				<td>Group</td>
				<td>Filter</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" style="width:325px" name="Care_of" value=""/></td>
				<td>business
				<input type="checkbox" name="Business" value="<? if ($data['business'] == 1) { echo 'checked="checked"'; } ?>"/></td>
					<td><select name="Group">
						<?
							$select_print = "<option> </option>";
							$result=dbQuery("SELECT * FROM groups ORDER BY group_description");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['group_id'].'"/>'.$row['group_description'].'</option>';
							}

							echo selectOption($select_print, $data['group_id']);
						?>
					</select></td>
					<td><select name="Filter">
						<?
							$select_print = "<option> </option>";
							$result=dbQuery("SELECT * FROM filter ORDER BY filter");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['filter'].'"/>'.$row['filter'].'</option>';
							}

							echo selectOption($select_print, $data['sort_id']);
						?>
					</select></td>
			</tr>
			<tr>
				<td>UPS Address (No PO Box)</td>
				<td colspan=2>&nbsp</td>
					<td><input type="checkbox" name="Dealer" value="<? if ($data['reseller'] == 1) { echo 'checked="checked"'; } ?>"/>
					Dealer</td>
			</tr>
			<tr>
				<td colspan=2><input type"text" style="width:325px" name="UPS_Address" value=""/></td>
				<td>&nbsp</td>
					<td><input type="checkbox" name="Consultant" value="<? if ($data['consultnat'] == 1) { echo 'checked="checked"'; } ?>"/>
					Consultant</td>
			</tr>
			<tr>
				<td>Address 2 </td>
				<td colspan=2>&nbsp</td>
				<td>Support Type</td>
				<td>Expiry</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" style="width:325px" name="Address2" value=""/></td>
				<td>&nbsp</td>
					<td><select name="Support">
						<?
							$select_print = "<option> </option>";
							$result=dbQuery("SELECT * FROM support_types ORDER BY support_type");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['support_id'].'"/>'.$row['support_type'].'</option>';
							}

							echo selectOption($select_print, $data['support_id']);
						?>
					</select></td>
				<td><input type="post" name="Expiry" value="<? echo $data['support_exp']; ?>"/></td>
			</tr>
			<tr>
				<td>Zip</td>
				<td>City</td>
				<td>State</td>
				<td>First Contact</td>
			</tr>
			<tr>
				<td><input type="text" name="Zip" value=""/></td>
				<td><input type="text" name="City" value=""/></td>
					<td><select style="width:150px" name="State">
						<?
							$select_print = "<option> </option>";
							$result=dbQuery("SELECT * FROM states ORDER BY full_name");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['states'].'"/>'.$row['full_name'].'</option>';
							}

							echo selectOption($select_print, $data['states']);
						?>
					</select></td>
				<td><select name="First_Contact">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM first_contact ORDER BY first_contact");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['first_contact'].'"/>'.$row['first_contact'].'</option>';
						}

						echo selectOption($select_print, $data['first_contact']);
					?>
				</select></td>
			</tr>
			<tr>
				<td> Phone </td>
				<td> Fax </td>
				<td> Tax Number </td>
				<td> Literature </td>
				<td> Lit Date </td>
			</tr>
			<tr>
				<td><input type="text" name="Phone" value=""/></td>
				<td><input type="text" name="Fax" value=""/></td>
				<td><input type="text" style="width:150px" name="Tax Number" value=""/> </td>
					<td><select name="Literature">
						<?
							$select_print = "";
							$result=dbQuery("SELECT * FROM literature ORDER BY lit_status");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['lit_id'].'"/>'.$row['lit_status'].'</option>';
							}

							echo selectOption($select_print, $data['lit_id']);
						?>
					</select></td>
				<td><input type="text" name="Lit_Date" value=""/></td>
			</tr>
			<tr>
				<td>E-mail</td>
				<td>&nbsp</td>
				<td>Country</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" style="width:325px" name="email" value=""></td>
					<td><select style:"width=150px" name="Country">
						<?
							$select_print = "";
							$result=dbQuery("SELECT * FROM country ORDER BY name");
							while($row=dbNext($result))
							{
								$select_print .= '<option value="'.$row['country_id'].'"/>'.$row['name'].'</option>';
							}

							echo selectOption($select_print, $data['country_id']);
						?>
					</select></td>
				<td>&nbsp</td>
			</tr>
		<table>
	</body>
					<input type="submit"style="width:150px" name="Show Reocrds" value="Show Records"/></form></td>
<?
dbClose();
?>

Filtered.php:

<?php
include 'common.php';
dbConnect();
$where = array();
$where[] = isset($_POST['LastName']) ? " `last_name` = '". mysql_real_escape_string($_POST['LastName']) . "' ":null;
$where[] = isset($_POST['State']) ? " `states` = '" . mysql_real_escape_string($_POST['State']) . "' ":null;

foreach ($where as $index => $val) 
{
if (is_null($where[$index]))
	unset($where[$index]);
}

$where = implode(' AND ', $where);
echo $where;
$c = mysql_query("SELECT * FROM customers WHERE $where");
$customer = mysql_fetch_assoc($results[$c]);

//print_r($customer)
?>

<head>
	<title> Filtered </title>
</head>
<html style="Background-color:BBBBBB">
	<body>
		<table>
			<tr>
				<td><form action="EditCustomer.php" method="GET">
                  		<input type="hidden" name="customer_id" value="<? echo $customer['customer_id'];?>"/>
                  		<input type="submit" style="height:50px" style="width:100px" name="Edit Record" value="Edit Record"/></form></td>
				<td>&nbsp</td>
				<td>Customer ID
				<input type="text" readonly=true style="text-align:right" style="width:80px" name="customer_id" value="<?echo $customer['customer_id'];?>"/></td>
			</tr>
			<tr>	
				<td style="width:200px">First Name</td>
				<td>Last Name</td>
				<td>&nbsp</td>
				<td>Status</td>
			</tr>
			<tr>
				<td colspan=3><input type="text" readonly=true style="width:200px" name="FirstName" value="<? echo $customer['first_name']; ?>"/>
				<input type="text" readonly=true style="width:280px" name="LastName" value="<? echo $customer['last_name']; ?>"/></td>
				<td colspan=2><select disabled=true name="StatusID">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM customer_status ORDER BY status_name");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['status_id'].'"/>'.$row['status_name'].'</option>';
						}

						echo selectOption($select_print, $customer['status_id']);
					?>
				</select></td>
					<td style="text-align:center">Serial</td>
					<td style="text-align:center">Product</td>
					<td style="text-align:center">Ver</td>
			</tr>
			<tr>
				<td>Care of </td>
				<td colspan=2>&nbsp</td>
				<td>Group</td>
				<td>Filter</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" readonly=true style="width:325px" name="Care of" value="<? echo $customer['care_of']; ?>"/></td>
				<td>business
				<input type="checkbox" disabled=true name="Business" value="<? if ($customer['business'] == 1) { echo 'checked="checked"'; } ?>"/></td>
				<td><select disabled=true name="Group">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM groups ORDER BY group_description");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['group_id'].'"/>'.$row['group_description'].'</option>';
						}

						echo selectOption($select_print, $customer['group_id']);
					?>
				</select></td>
				<td><select disabled=true name="Filter">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM filter ORDER BY filter");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['filter'].'"/>'.$row['filter'].'</option>';
						}

						echo selectOption($select_print, $customer['sort_id']);
					?>
				</select></td>
					<?/*
					$result=dbQuery("SELECT * FROM shipping s LEFT JOIN orders o ON s.order_number=o.order_id LIMIT " . $_GET['recordno'] . ", 1");

					while($row=dbNext($result))
					{
						echo'<tr>';
						echo'<td style="text-align:right">'.$row['serial_no'].'</td>';
						echo'<td>'.$row['product_description'].'</td>';
						echo'<td>'.$row['version'].'</td>';
						echo'</tr>';
					}
					*/?>
			</tr>
			<tr>
				<td>UPS Address (No PO Box)</td>
				<td colspan=2>&nbsp</td>
				<td><input type="checkbox" disabled=true name="Dealer" value="<? if ($customer['reseller'] == 1) { echo 'checked="checked"'; } ?>"/>
				Dealer</td>
			</tr>
			<tr>
				<td colspan=2><input type"text" readonly=true style="width:325px" name="UPS Address" value="<? echo $customer['ups_address']; ?>"/></td>
				<td>&nbsp</td>
				<td><input type="checkbox" disabled=true name="Consultant" value="<? if ($customer['consultant'] == 1) { echo 'checked="checked"'; } ?>"/>
				Consultant</td>
			</tr>
			<tr>
				<td>Address 2 </td>
				<td colspan=2>&nbsp</td>
				<td>Support Type</td>
				<td>Expiry</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" readonly=true style="width:325px" name="Address2" value="<? echo $customer['address'];?>"/></td>
				<td>&nbsp</td>
				<td><select disabled=true name="Support Type">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM support_types ORDER BY support_type");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['support_ID'].'"/>'.$row['support_type'].'</option>';
						}

						echo selectOption($select_print, $customer['support_ID']);
					?>
				</select></td>
				<td><input type="text" readonly=true name="Expiry" value="<? echo $customer['support_exp']; ?>"/></td>
			</tr>
			<tr>
				<td>Zip</td>
				<td>City</td>
				<td>State</td>
				<td>First Contact</td>
				<td>Pur Date </td>
			</tr>
			<tr>
				<td><input type="text" readonly=true name="Zip" value="<? echo $customer['zip']; ?>"/></td>
				<td><input type="text" readonly=true name="City" value="<? echo $customer['city']; ?>"/></td>
				<td><select style="width:150px" disabled=true name="State">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM states ORDER BY full_name");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['states'].'"/>'.$row['full_name'].'</option>';
						}

						echo selectOption($select_print, $customer['states']);
					?>
				</select></td>
				<td><select disabled=true name="First Contact">
					<?
						$select_print = "<option> </option>";
						$result=dbQuery("SELECT * FROM first_contact ORDER BY first_contact");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['first_contact'].'"/>'.$row['first_contact'].'</option>';
						}

						echo selectOption($select_print, $customer['first_contact']);
					?>
				</select></td>
				<td><input type="text" readonly=true name="Run Date" value="<? echo $customer['last_pur_date']; ?>"/></td>
			</tr>
			<tr>
				<td> Phone </td>
				<td> Fax </td>
				<td> Tax Number </td>
				<td> Literature </td>
				<td> Lit Date </td>
			</tr>
			<tr>
				<td><input type="text" readonly=true name="Phone" value="<? echo $customer['phone_number']; ?>"/></td>
				<td><input type="text" readonly=true name="Fax" value="<? echo $customer['fax_number']; ?>"/></td>
				<td><input type="text" readonly=true style="width:150px" name="Tax Number" value="<? echo $customer['tax_id']; ?>"</td>
				<td><select disabled=true name="Literature">
					<?
						$select_print = "";
						$result=dbQuery("SELECT * FROM literature ORDER BY lit_status");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['lit_id'].'"/>'.$row['lit_status'].'</option>';
						}

						echo selectOption($select_print, $customer['lit_id']);
					?>
				</select></td>
				<td><input type="text" readonly=true name="Lit Date" value="<? echo $customer['lit_date']; ?>"/></td>
			</tr>
			<tr>
				<td>E-mail</td>
				<td>&nbsp</td>
				<td>Country</td>
			</tr>
			<tr>
				<td colspan=2><input type="text" readonly=true style="width:325px" name="E-mail" value="<? echo $customer['email_address']; ?>"></td>
				<td><select style:"width=150px" disabled=true name="Country">
					<?
						$select_print = "";
						$result=dbQuery("SELECT * FROM country ORDER BY name");
						while($row=dbNext($result))
						{
							$select_print .= '<option value="'.$row['country_id'].'"/>'.$row['name'].'</option>';
						}

						echo selectOption($select_print, $customer['country_id']);
					?>
				</select></td>
			</tr>
			<tr>
				<td style="text-align:center">Date</td>
				<td style="text-align:center">Employee</td>
				<td colspan=2 style="text-align:center">Text</td>
				<td>&nbsp</td>
				<td><form action="OrderForm.php" method="GET">
					<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
					<input type="submit"style="width:150px" name="Order Form" value="Order Form"/></form></td>
				<td><form action="OrderHistory.php" method="GET">
							<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
							<input type="submit"style="width:150px" name="Order History" value="Order History"/></form></td>
			</tr>
			<tr>
				<?/*
					$result=dbQuery("SELECT * FROM notes LIMIT " . $_GET['recordno'] . ", 1");
					while($row=dbNext($result))
					{
						echo'<tr>';
							echo'<td style="text-align:right">'.$row['date_entered'].'</td>';
							echo'<td>'.selectOption($select_print, $customer['employee_id']).'</td>';
							echo'<td>'.$row['text'].'</td>';
						echo'</tr>';
					}
				*/?>
				<td colspan=5>&nbsp</td>
				<td><form action="Inventory.php" method="GET">
							<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
							<input type="submit"style="width:150px" name="Reg. & Inventory" value="Reg. & Inventory"/></form></td>
				<td><form action="Remove.php" method="GET">
							<input type="hidden" name="customer_id" value="<? echo ($customer['customer_id']); ?>"/>
							<input type="submit"style="width:150px" name="Delete Customer" value="Delete Customer"/></form></td>

			</tr>
		</table>
		<table>
			<tr>
				<td><form action="Filtered.php" method="GET">
						<input type="hidden" name="recordno" value="0"/>
						<input type="submit" <? if ($_GET['recordno']==0) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="First" value="First"/></form></td>
				<td><form action="Filtered.php" method="GET">
						<input type="hidden" name="recordno" value="<? echo ($_GET['recordno']-1); ?>"/>
						<input type="submit" <? if ($_GET['recordno']==0) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Previous" value="Previous"/></form></td>

				<?
					//$result=dbQuery("SELECT count(*) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id>1");
					//if($subrow=dbNext($result))
					//{
						?>
						<td><form action="Filtered.php" method="GET">
							<input type="hidden" name="recordno" value="<? echo ($_GET['recordno']+1); ?>"/>
							<input type="submit" <? if ($_GET['recordno']==($subrow['count(*)']-1)) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Next" value="Next"/></form></td>
						<td><form action="Filtered.php" method="GET">
							<input type="hidden" name="recordno" value="<? echo ($subrow['count(*)'] - 1); ?>"/>
							<input type="submit" <? if ($_GET['recordno']==($subrow['count(*)']-1)) {echo " disabled=\"disabled\" "; } ?> style="width:100px" style="height:50px" name="Last" value="Last"/></form></td>
				<?
					//}
				?>
			</tr>
		</table>
	</body>
</html>

danjoe_15 · January 28, 2009

I decided to go about it a different way and it is now working as it should.

Sign In

[SOLVED] Filter

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information