Selectively building an SQL query

centos

I have a form that has 4 select boxes out of which the first one is mandatory and other 3 may or may not be selected by the user. If one or more of them is selected the searching (or rather viewing) gets more specific according to that criterea.

My problem is how you build the SQL for this kind of thing ? My problem is further complicated by the fact that if the user selects one or more selectives at most two tables have to be joined to get the results. So I have to get these tables to the from clause as well as in the where clause and for joining.

Any pointers or ideas would be very much appreciated,

Thanks all.

Roger_Ramjet

generalised

if (isset($_POST['select1'])) {
$base = "SELECT tbl1.col1, tbl1.col2 ";
$from = " FROM tbl1 ";
$where = " WHERE tbl1.col = '" . $_POST['select1'] . "'";

if (isset($_POST['select2'])) {
$base .= " , tbl2.col1, tbl2.col2 ";
$from .= " INNER JOIN tbl2 ON tbl1.id=tbl2.id ";
$where .= " AND tbl2.col1 = '" . $_POST['select2'] . "'";
}

if (isset($_POST['select3'])) {
$base .= " , tbl3.col1, tbl3.col2 ";
$from .= " INNER JOIN tbl3 ON tbl1.id = tbl3.id ";
$where .= " AND tbl3.col1 = '" . $_POST['select3'] . "'";
}

$sql = $base . $from . $where;
}

centos

Looks like this is EXACTLY what I was looking for. Thanks heaps Roger, I'll try this out. This just gotta work. Neato.

Roger_Ramjet

It's just the principle: build your query in blocks or pieces and then construct the full query string out of them. It's how phpmyadmin does it, except that myadmin uses classes that can handle any case.