Binding parameters and using "AND"

[Square1]

Hi all:

I am at loss as to how to bind parameters in this particular case. Maybe someone can help...

I have a form with 11 fields. These fields are going to a SQL SELECT query. All fields are optional and the more fields entered, the less results will appear as I am (mostly) using AND throughout the WHERE portion of the statement.

Here is the problem. If a field is not entered (as mentioned, all fields are optional) I do not want it to be read in the SQL QUERY. I do not want it in the WHERE portion as this will cause no records to appear. For example,

WHERE
				player = ? AND
				state = ? AND
				eventDate = ? AND
				sportCode = ? AND
				prID = ? AND
				listingDate = ? AND
				discountAmount = ? AND
				mailOrder = ? AND
				big15 = ? AND
				evID = ? AND
				(team1 = ? OR
				team2 = ? OR
				team3 = ? OR
				team4 = ? OR
				team5 = ? OR
				team6 = ? OR
				team7 = ? OR
				team8 = ? OR
				team9 = ? OR
				team10 = ? OR
				team11 = ? OR
				team12 = ? OR)
				EventDate >= CURDATE() AND
				StartDate <= CURDATE() AND
				hold IS NULL

If a search is made with the above for STATE only and 'New York' is chosen, instead of having every event in New York appear, nothing will appear. What I would really need is for the WHERE to become

WHERE
				state = ? 
				EventDate >= CURDATE() AND
				StartDate <= CURDATE() AND
				hold IS NULL

I can do this. I can build a WHERE variable using ISSET and only include the fields that are set. So this brings me back to my original issue, binding the parameters. How do I change:

mysqli_stmt_bind_param($stmt, "ssssssssssssssssssssss",

to

mysqli_stmt_bind_param($stmt, "s",

on the fly?

Hope this makes sense!

Thanks

[mad_baron1]

the mysql query is a string....maby you could use it at your advantage:

now im not to sure how you want your query "like or and regexp" stuff like that. or what binding is. but maby this will help.

<?php

if($POST['this_one_thing'])
{

if($Count==1)
	{
		$search+="'column'='" . $POST['this_one_thing'] . "'";
	}
else
	{
		$search+="OR 'column'='" . $POST['this_one_thing'] . "'";
	}


$Count+=1;
}
if($POST['this_other_thing'])
{

if($Count==1)
	{
		$search+="'column2'='" . $POST['this_other_thing'] . "'";
	}
else
	{
		$search+="OR 'column'='" . $POST['this_other_thing'] . "'";
	}


$Count+=1;
}


$query= "select * from whatever where".$search;

?>

[johanafm]

That's a whole lot of if checks.... I prefer an iteration based solution approach that takes less effort and code repetition to set up

$andFields = array('player', 'state', 'evenDate', 'etc');
$orFields = array('team1', 'team2');
$where = array('andFields' => array(), 'orFields' => array());
$fieldValues = array();

$_POST['player'] = 'Glenn';
$_POST['state'] = 'New York';
$_POST['team1'] = 'You';
$_POST['team2'] = 'Me';

foreach ($where as $k => $v)
{
	foreach ($$k as $f)
	{
		if (!empty($_POST[$f]))
		{
			$where[$k][] = "$f = ?";
			$fieldValues[] = $_POST[$f];
		}
	}
}

if (count($where['andFields']))
{
	$where['andFields'] = implode(' AND ', $where['andFields']);
}
else
{
	unset($where['andFields']);
}
if (count($where['orFields']))
{
	$where['orFields'] = '(' . implode(' OR ', $where['orFields']) . ')';
}
else
{
	unset($where['orFields']);
}
if (count($where))
{
	$whereString = 'WHERE ' . implode(' AND ', $where);
}
else
{
	$whereString = '';
}
echo $whereString;
printf('<pre>%s</pre>', print_r($fieldValues, 1));

[Square1]

Thank you both. I will look at both of these.

Best regards,

Howard