Help With Search Code

[cavey5]

I have a MySQL database with 200,000 records. The records are filled with customer information like Company Name, First Name, Last Name, Address, City, State, Zip etc. etc... I wrote add record, delete record, edit record, display record etc. pages but I am having trouble with Search. Sometimes we have to get creative with Search to find a user, maybe their name is spelled wrong or they misspelled their city, so we search for First Name and Zip Code to see if we get a hit... so I have a few questions:

 

1. If I have ten fields, and any given search may use only a few of the search options (text boxes), like First Name and Zip Code, and the others are left blank... how would I write a query that would find results where the First Name and Zip Code match, and ignore any text boxes left blank? I want to avoid a huge long if statement... is there a way to check for if $textbox1 == "" then ignore?

 

2. Also, on my display record page, if a record has more than one word, it only displays the first word, how can i fix that? Here's the code:

 

$id = $_GET['id'];

    // Makes initial conection to database
define ('DB_USER', 'xxxxx');
define ('DB_PASSWORD', 'xxxxx');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'xxxxx');

$connect = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die(mysql_error()); 

$db = @mysql_select_db(DB_NAME, $connect)
or die(mysql_error()); 

// Selects customer data from the database
$findcustomer = "(SELECT company, phone, email, firstname, lastname, address1, address2, 
city, state, zipcode, country, status FROM subscriber_data WHERE id = '$id')";

$findcustomer_result= mysql_query($findcustomer) 
OR die('QUERY ERROR:<br />' .$findcustomer. '<br />' .mysql_error());  

 

<?
						  while ($row = mysql_fetch_array($findcustomer_result)) {
						  $id = $_GET["id"];
						  $company = $row["company"];
						  $phone = $row["phone"];
						  $email = $row["email"];
						  $firstname = $row["firstname"];
						  $lastname = $row["lastname"];
						  $address1 = $row["address1"];
						  $address2 = $row["address2"];
						  $city = $row["city"];
						  $state = $row["state"];
						  $zipcode = $row["zipcode"];
						  $country = $row["country"];
						  $status = $row["status"];
						  
					  	  echo" blah blah ";
}

[Illusion]

what difference it makes if u have a nested if loop or a while loop to eliminate other fields?

[fenway]

1. Just run though all of the input fields, make sure it's not blank, otherwise, push the field and value onto a array.

2. Huh?

[cavey5]

Not sure how to do that with an array but...

 

I need to check each field for data, and then if it is populated, at that string to the query, and because it will be created dynamically, how do I insert the AND between each one?

 

 

So out of ten fields, say numbers 1, 3 and 5 are populated, I need a query that says SELECT * from table WHERE 1 = '$searchterm1' AND 3 = '$searchterm3' AND 5 = '$searchterm5';

 

 

how do i dynamically create that query based on user input?

[fenway]

That's what I said.... check, then push onto an array, then join with AND.

[anatak]

well the array would be the most elegant solution.

here is a not so elegant suggestion

 

say the user filled in Name and State and left open Firstname

$Query = SELECT company, phone, email, firstname, lastname, address1, address2,

city, state, zipcode, country, status FROM subscriber_data WHERE Id <>null';

//Id<>null is needed so you can do all the AND stuff without problems any clause that will always //be true is ok

//if you have a field to hide records maybe you can do WHERE hide = 1 (not true)

 

If ($_POST(Name)!=null){

$Query= $Query. "AND lastname = $_POST(Name)";

}

If ($_POST(State)!=null){

$Query=$Query. "AND state = $_POST(State)";

}

 

And do this for all the fields

not an elegant solution but once you understand this you can do this with arrays and a loop

 

anatak

[Illusion]

AND operatot is also should be added dynamically.