select on three tables

[eduac]

Hi all,

I try to make a search engine that will search on three tables and with multiple fields. Something like this:

<form method="POST" action="<?=$PHP_SELF?>">
Name: <input type="text" name="name"><br>
Position: <input type="text" name="position"><br>
Country: <input type="text" name="country"><br>
<input type="submit" value="Search!">
</form>

And this is the three tables that form will search:

---

NAME
id int (primary key)
name varchar
id_position int

id_country int

-----------------------------------
POSITION
id int (primary key)

position_name varchar

-----------------------------------
COUNTRY
id int (primary key)

country_name varchar

I dont know how can make this select 🙁 Anyone can help me?

[nnichols]

Give something like this a try -

$query_array = array();

if($_POST['name']) {
	$query_array[] = "name = '" . mysql_real_escape_string($_POST['name']) . "'";
}
if($_POST['position']) {
	$query_array[] = "postion_name = '" . mysql_real_escape_string($_POST['position']) . "'";
}
if($_POST['country']) {
	$query_array[] = "country_name = '" . mysql_real_escape_string($_POST['country']) . "'";
}

$sql = "SELECT *
FROM NAME n
INNER JOIN POSITION p
	ON n.id_position = p.id
INNER JOIN COUNTRY c
	ON n.id_country = c.id";

if (count($query_array) > 0) {
	$sql .= 'WHERE ' . implode(' AND ', $query_array);
}

[eduac]

thx a lot! works fine!