User Profile Search

Madwacker

Ok... I've searched these forums, Google, and talked to one friend who only knows Oracle..and I can sorta make this work, but not. Basically I have my user database and I'm trying to make something like a personal's search. I then spit the data into a formatted table that you would get on a personals page with 15 entires, next 15> etc.

Username (txtBox)
First / Last (txtBox) / (txtBox)
Age Group (selBox)
City (selBox)
State (selBox)

The user can fill in 'full' or 'partial' on the text boxes, or only one and none of the others. Same for the select boxes.

I can get the username partial to come up full after submit, but the minute I add more into the mySQL statement it craps out on me. Here is a snippet of my code (it keeps going after the last comma)

$sql=mysql_db_query ("$db","SELECT * FROM myusers WHERE username LIKE '$username%', first_name LIKE '$first_name%', last_name LIKE '$last_name%',

How do I setup the SQL statement so that I can do an AND/OR type of thing w/o writing out a ton of different statement combos.

Thanks in Advance!

Madwacker

bump anyone? Please...

drag0n

So you are trying to build a search engine which searches ALL fields in the DBTable?

Madwacker

I'm trying to do exactly what I said above..... I want to make a User Profile search. Go to something like match.com ...

You want Username, but you're not sure...so you enter "Madw" and you know my first name is "Jeff" ...so you fill that in. Then you hit 'submit' and it finds all results possible that contain

Username: "Madw"
First Name: "Jeff"

Oh, there's only one match for

Username: "Madwacker"
First / Last Name: Jeff Yablonski
Gender: Male
City: San Jose
State: CA

etc etc... make a little more sense?

p0pb0b

u need OR
WHERE username LIKE '$username%' OR first_name LIKE '$first_name%'

Madwacker

that didn't work. Now, no matter what I enter OR if I just leave everything blank, it spits out the last row in my users table.

bad76

Hi,

try this

mysql_query("SELECT ... WHERE username LIKE '$username%' AND first_name LIKE '$first_name%'") or die(mysql_error());

Or better:
$myquery="SELECT ... WHERE username LIKE '$username%' AND first_name LIKE '$first_name%'";
mysql_query($myquery) or die(mysql_error()."<br><i>".$myquery."</i>");

I hope this help.

Madwacker

Nope. That didn't work either. AND and/or OR in the SQL statement aren't working.

I guess I'm gonna have to dynamically build the WHERE="" statement with a bunch of IFs...

unless someone has a better idea.

Madwacker

I got it with the help of a dBA guy inhouse.

$sql = "SELECT * FROM users WHERE id > 0 ";

if ($username !="") {
    $sql.= "AND username LIKE '%$username%'";
}
// etc etc

$result=mysql_db_query ("$db","$sql");

The 'id > 0' is a constant value that is always there, therefore I can just make everything an AND.