[RESOLVED] Writing a query with optional fields

[mithegooie]

What is the best way to write a query with optional fields. I currently have like 6 embedded if statements, but the code doesn't seem to be working. The form has a full text search box, and several drop down boxes. Any help is surely appreciated.

[thepeccavi]

To insert a line into a MySQL db with optional fields:

INSERT (fieldname, fieldname2, fieldname3) INTO tablename VALUES ('value for fielename', NULL, 'value for fieldname3')

PS. I am not massively experienced with INSERT...

Hope this helps!

[Vbabiy]

generate the SQL on the fly, do something like this:

 
<?php
$array = array('fieldname' => 'value', 'fielnamd2' => 'value2');

$sql = 'INSERT (';
foreach($array as $field => $value)
{
$sql .= "'$field',";
}

$sql .= ') VALUES (';

foreach ($array as $value)
{
$sql .= "'$value,'";
}
$sql .= ');';

echo $sql;
?>

[mithegooie]

This is how I've coded it; however, when I try to run the query, I get no results.

$sql = <<<EOS
SELECT pro.prg_id as id, pro.name as name, pro.clickbank as clickbank, 
	pro.cost as cost, pro.summary as summary, cat.cat_name as category, 
	type.type_name as type
FROM legit_programs pro
LEFT JOIN legit_type type
ON pro.type = type.type_id
LEFT JOIN legit_cat cat
ON pro.category = cat.cat_id 
WHERE 
EOS;

if(isset($_GET['name'])) {
		$sql .= "MATCH (name) AGAINST ('" . htmlspecialchars($_GET['name']) . "' IN BOOLEAN MODE)";
	if(isset($_GET['type'])) {
		$sql.= " AND ";
	} else if(isset($_GET['cat'])) {
		$sql.= " AND ";
	} else if(isset($_GET['cost'])) {
		$sql.= " AND ";
	} 
} else {}
if(isset($_GET['type'])) {
		$sql .= "type='" . $_GET['type'] . "'";
		if(isset($_GET['cat'])) {
			$sql .= " AND ";
		} else if(isset($_GET['cost'])) {
			$sql .= " AND ";
		}
} else {}
if(isset($_GET['cat'])) {
		$sql .= "category='" . $_GET['cat'] . "'";
		if(isset($_GET['cost'])) {
			$sql .= " AND ";
		}
} else {}
if(isset($_GET['cost'])) {
	$sql .= "cost " . $_GET['cost'];
} else {}

$sql .= "AND pro.is_published=1;";

$result = mysql_query($sql, $conn)
	or die('Could not retrieve review data;' . mysql_error());
?>
<h1>Search Results</h1>
<table id="revresults">
	<tr>
		<th>Name</th>
		<th>Type</th>
		<th>Category</th>
		<th>Summary</th>
		<th>Cost</th>
	</tr>
<?php	
	if(mysql_num_rows($result) == 0) {
		echo "<tr><td colspan=5><em>No reviews available</em></td></tr>";
	} else {
		while ($row = mysql_fetch_array($result)) {
	echo "<tr>\n";
	echo '<td><a href="reviews.php?id=' . $row['id'] . '">' . $row['name'] . "</a></td>\n";
	echo "<td>" . $row['type'] . "</td>\n";
	echo "<td>" . $row['category'] . "</td>\n";
	echo "<td>" . $row['summary'] . "</td>\n";
	echo "<td>" . $row['cost'] . "</td>\n";
	echo "</tr>";

}
}
echo "</table>"

?>

[thepeccavi]

hmmm... in Vbabiy's sql there he does not specify a table... you will need "INTO tableName" on the end of that...

[Installer]

If you echo your query, you'll find there's no space before the final "AND", and there's a missing "=" sign in the "cost" part.

Also make sure there's no space after "<<<EOS" or "EOS;" They show up when I copy and paste your code, but maybe they're a bb artifact.

[mithegooie]

Is the logic reasonable? Or is there a better way? I'm just trying to have a set of search criteria where each field is optional and the user can specify the parameters for one or many of the fields. I'm not sure the best way to write the query. Am I on the right track?

[mithegooie]

I just figured out my real question. The select box has a generic "select a category..." option with value="". What I want the code to do is if no actual option is selected, then I want that critierion to be omitted from the query. As it's written now, the full query is written including "AND category=''...etc". The query works if I select an option from every box, but how do I make the code omit the unselected criteria from the query??? Thanks for the help

[Vbabiy]

SOrry i did make some mistakes but i was just try to show a idea 🙂