multiple variable in mysql search

marlajm

I have read some of the posts my search came up with but some were beyond my knowledge and others didn't seem to help. I want to search a table in my database using five variable that I get from a form. It works when I enter one but it I enter two the search fails. They are drop down boxes that match the database perfectly so I am not sure where I went wrong.

mysql_connect($host, $user, $password);

@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM `VictimEmail` WHERE "; 

if($id)
	$query.="ID ='$id'";
if($name)
	$query.="Name LIKE '$name'";
if($state)
	$query.="State LIKE '$state'";
if($treatment)
	$query.="Treatment LIKE '%$treatment%'";
if($subject)
	$query.="Subject LIKE '$subject'";

$result=mysql_query($query)
or die ("Query Failed");

$num=mysql_num_rows($result);
mysql_close();

Any advise would be greatly appreciated and thanks in advance for your help.

Mordecai

You need some spaces in a few areas.

This should be correct:

mysql_connect($host, $user, $password);

@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM `VictimEmail` WHERE"; 

if($id)
    $query.=" ID ='$id'";
if($name)
    $query.=" Name LIKE '$name'"; // added a space before here
if($state)
    $query.=" State LIKE '$state'"; // here
if($treatment)
    $query.=" Treatment LIKE '%$treatment%'"; // here
if($subject)
    $query.=" Subject LIKE '$subject'"; // and here

$result=mysql_query($query)
or die ("Query Failed");

$num=mysql_num_rows($result);
mysql_close();

The spaces are needed because you only had a space after the WHERE, but not between each of the query additions. Each of them were essentially "sticking together." (No spaces between.)

marlajm

Thank you for your help. I added the spaces but it still is failing when I use two variables that I know exist together.

Mordecai

Most likely, you won't even have to redefine those by their $POST equivalent. They can also be retrieved by their name as a variable. (E.g. $name for $POST["name"]). However, I doubt this is where your problem lies (seeing as it shouldn't care which method you choose).
In other words, those lines are correct. Check the line above 45 for an error, it may just have carried over to line 45 (something like a missing semicolon (😉, perhaps).

Edit:
I don't understand what you've edited that to... perhaps you can reword that?

marlajm

Sorry. I fixed the line 45 problem, it was the line before, I just wasn't looking there and didn't realize it. The edited post refers back to my first problem. I added the spaces where you suggested but the search still fails when using two variables. I know that they exist. Should I be using wildcards differently? I put the wildcards in where I had multiple select (in the treatment column) so that it would pick those up but the others should match without them. Thank you again for your help, sorry I am so lost😃

xblue

you need to combine the conditions in your where clause either with AND or OR.

Also, replace this

$result=mysql_query($query)
or die ("Query Failed");

with

$result=mysql_query($query)
or die ("Query: ".$query." failed: ".mysql_error());

to get a more meaningful error message.