sql query problems

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
adult.swim
Forum Newbie
Posts: 9
Joined: Sun Jul 09, 2006 8:00 pm

sql query problems

Post by adult.swim »

I have created a html form page, and a corresponding process page.

My form gets input from the user, and compares it to the fields in my database. If the input in the form matches a field in the dB, the process page returns the corresponding record.

The problem is only the address input for the form works correctly. All of the other fields return all the records in the dB. I've tried varying the SQL query code many times.

My question is why does only the address search work as opposed to the other input fields?

search.php: form

Code: Select all

<form id=\"search\" method=\"post\" action=\"process.php\">

<table class=\"search1\">

<tr>
<th><label for=\"pad\">Pad Number</label></th>
<td><input id=\"pad\" name=\"pad\" type=\"text\" class=\"inputbox\" size=\"50\" /><br /></td>
</tr>

<tr>
<th><label for=\"mls\">MLS Number</label></th>
<td><input id=\"mls\" name=\"mls\" type=\"text\" class=\"inputbox\" size=\"50\" /><br /></td>
</tr>

<tr>
<th><label for=\"address1\">Address</label></th>
<td><input id=\"address1\" name=\"address1\" type=\"text\" class=\"inputbox\" size=\"100\" /><br /></td>
</tr>

<tr>
<th><label for=\"city\">City</label></th>
<td><input id=\"city\" name=\"city\" type=\"text\" class=\"inputbox\" size=\"100\" /><br /></td>
</tr>

<tr>
<th><label for=\"zip\">Zip Code</label></th>
<td><input id=\"zip\" name=\"zip\" type=\"text\" class=\"inputbox\" size=\"15\" /><br /></td>
</tr>


</table>

<br />

<table class=\"search2\">

<tr>
<td>

<input type=\"Submit\" value=\"Submit\" style=\"width: 60px; font-size: 1.0em; padding: 2px 10px 2px 10px; margin: 5px 3px -3px 3px; background-color: #115492; float: left; border: solid 1px #023666; color: #ffffff; text-decoration: none;\" />

<input type=\"Reset\" value=\"Reset\" style=\"width: 60px; font-size: 1.0em; padding: 2px 10px 2px 10px; margin: 5px 3px -3px 3px; background-color: #115492; float: left; border: solid 1px #023666; color: #ffffff; text-decoration: none;\" />

<input type=\"hidden\" name=\"op\" value=\"do\" /><p>&nbsp;</p>
</td>
</tr>
</form>

</table>
---------------------------------------------
process.php:

Code: Select all

<? 
# connect to a DSN "mydb" with a user and password "marin" 
$connect = odbc_connect("**", "**", "**");

# query the users table for name and surname
$query = "SELECT * FROM props WHERE props.MLSnumber='$mls' 
OR props.Pads LIKE '%$pad%'
OR props.Address LIKE '%$address1%'
OR props.City='$city'
OR props.Zip='$zip'";

# perform the query
$result = odbc_exec($connect, $query);

//output results to standard output 
//odbc_result_all($result, "BORDER=1"); 

# fetch the data from the database
while(odbc_fetch_row($result)){
  $mls = odbc_result($result, 2);
  $address = odbc_result($result, 5);
  $pad = odbc_result($result, 16);
  $city = odbc_result($result, 7);
  $zip = odbc_result($result, 9);
  $check = odbc_result($result, 17);
//  print("$mls  $address  $pad  $city  $zip");
  
  //format results 
    print ("<tr>"); 
    print ("<td>$pad</td>"); 
    print ("<td>$mls</td>"); 
    print ("<td>$address</td>"); 
	print ("<td>$city</td>");
	print ("<td>$zip</td>");
	print ("</tr>"); 
    } 

# close the connection
odbc_close($connect);
?>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I am assuming that you have register_globals on. If you echo $query before doing the odbc_exec() what does the SQL look like?
(#10850)
User avatar
churt
Forum Commoner
Posts: 39
Joined: Wed Oct 04, 2006 9:59 am

Test for variable

Post by churt »

If $pad or $address1 are empty you will get all records. You need to test for each variable before adding it to the query.



This might work for what you are trying.


$query = "SELECT * FROM props WHERE";
if ($mls) $query.="props.MLSnumber='$mls'";
if ($pad) $query.="OR props.Pads LIKE '%$pad%'";
if ($address1) $query.="OR props.Address LIKE '%$address1%'";
if ($city) $query.="OR props.City='$city'";
if ($zip) $query.="OR props.Zip='$zip'";
if ($query=="SELECT * FROM props WHERE") {
echo "No search data entered. Please enter some query information.";
echo "<script> parent.location='./search.php'</script>";
}

Or you could return all records or whatever at this point.
Post Reply