Need Someone's Brain!

[jamesjmann]

I think this problem requires LOTS of brainpower, and as I am absolutely convinced at this point that I have none, I was kind of hoping someone could steer me in the right direction at the very least.

 

What I'm scripting right now is a search form that allows users to browse members based on what they type in the forms. There's about 8-10 forms of various criteria, such as age, country, birthdate, email, etc.

 

And once they hit submit, all the information they typed gets sent to the url ($_GET, in other words).

 

Now, the way I structured my script is like this:

<?php

//If nothing was submitted (or if the url is completely blank)
if ($_GET == array ()) {
//Define a variable that will be used to query the members; in this case, it would select all members
$query = "SELECT * FROM fans";
} else {
//If the user typed at least one thing (in the form OR the url)
if (count ($_GET) == 1) {
//If what they typed is only for one criteria, define a variable that creates a query for only ONE criteria to search for
	$query = "SELECT * FROM fans WHERE";
	foreach ($_GET as $field => $value) {
		$query .= " $field = '$value'";
	}
//If the user has typed in more than one field and hits search
} else {
//Define a variable for a query that selects members based off each criteria
	$query = "SELECT * FROM fans WHERE";
	foreach ($_GET as $field => $value) {
		$query .= " $field LIKE '%$value%' AND";
	}
}
}

?>

 

Now, the problem with the above script is the very last part...defining a query to run if more than one field is selected. Because I'm using a foreach loop to structure that query, my options seem rather limited with what I can and can't do as far as querying the database.  And the problem lies in where it says "$field LIKE '%$value%' AND". Because of the way the script is set up, the last $_GET variable will have "AND" at the very end, and when it runs through the mysql_query() function, it breaks, and does NOT work.

 

Is there a way around this? I figure it probably requires some complex mathematical equation (one which I have absolutely no idea on how to create).

 

Can anyone help?

 

[Skewled]

Can't remember off the top of my head but manipulation of the last get variable might work. I think its strtolenght or similar to that its a built in PHP function. But basically truncate the AND off the variable and pass it into your query.

[ignace]

//If nothing was submitted (or if the url is completely blank)
if ($_GET == array ()) {
//Define a variable that will be used to query the members; in this case, it would select all members
$query = "SELECT * FROM fans";
} else {
//If the user typed at least one thing (in the form OR the url)
if (count ($_GET) == 1) {
//If what they typed is only for one criteria, define a variable that creates a query for only ONE criteria to search for
	$query = "SELECT * FROM fans WHERE 1";
	foreach ($_GET as $field => $value) {
		$query .= " AND $field = '$value'";
	}
//If the user has typed in more than one field and hits search
} else {
//Define a variable for a query that selects members based off each criteria
	$query = "SELECT * FROM fans WHERE 1";
	foreach ($_GET as $field => $value) {
		$query .= " AND $field LIKE '%$value%'";
	}
}
}

[jamesjmann]

//If nothing was submitted (or if the url is completely blank)
if ($_GET == array ()) {
//Define a variable that will be used to query the members; in this case, it would select all members
$query = "SELECT * FROM fans";
} else {
//If the user typed at least one thing (in the form OR the url)
if (count ($_GET) == 1) {
//If what they typed is only for one criteria, define a variable that creates a query for only ONE criteria to search for
	$query = "SELECT * FROM fans WHERE 1";
	foreach ($_GET as $field => $value) {
		$query .= " AND $field = '$value'";
	}
//If the user has typed in more than one field and hits search
} else {
//Define a variable for a query that selects members based off each criteria
	$query = "SELECT * FROM fans WHERE 1";
	foreach ($_GET as $field => $value) {
		$query .= " AND $field LIKE '%$value%'";
	}
}
}

 

What's the "1" for? What does it do? And thanks for the quick reply, buddy!

[ignace]

What's the "1" for? What does it do? And thanks for the quick reply, buddy!

 

It makes your query valid as you are now building it like:

SELECT * FROM fans WHERE 1 AND $field = '$value' AND $field = '$value' ..

 

You could also just write TRUE instead of 1, it won't make any difference. Everything else should be self-explanatory.

[jamesjmann]

What's the "1" for? What does it do? And thanks for the quick reply, buddy!

 

It makes your query valid as you are now building it like:

SELECT * FROM fans WHERE 1 AND $field = '$value' AND $field = '$value' ..

 

You could also just write TRUE instead of 1, it won't make any difference. Everything else should be self-explanatory.

 

Okay, well it definitely worked, only now I'm having this problem: Everytime I hit the submit button, EVERYTHING gets sent to the url, which means the query is looking for empty strings if a field is blank. This is breaking my code and I don't know how to fix it. For example, if I only fill out one of the eight fields, all eight fields get sent to the url, therefore getting defined in the query, and I keep getting this error:

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Program Files\wamp\www\index.php

 

Is there a way to "strip" the empty variables from the url? Or perhaps, stop them from becoming part of the $query variable?

[PaulRyan]

Try throwing in an IF statement?

  if(!empty($field)) {
    $query .= " AND $field LIKE '%$value%'";
  }

 

Something like the above should work

 

Regards, PaulRyan.

[ignace]

$field will never be empty. $value however will.

 

if(!empty($value)) {

 

You can filter all empty values from $_GET using array_filter()

 

You also have code duplication, I suggest you wrap that inside a function.

[PaulRyan]

My bad, I did mean that Ignace...

 

Regards, PaulRyan