Searching an Array with given criteria

[Arrow]

Objective: Output a list of tickets that follows a certain criteria. The tickets are in a MySQL database.

 

Basic Use:

1. Determine a start date and an end date. The results should be no earlier than the start date and no later than the end date. (appears to work)

2. Determine searching criteria. Checkboxes determine whether the user wants to actually search for a given criteria (i.e a product).

3. If the checkbox is selected, then the results are filtered by the criteria. If not, then it isn't.

4. Repeat 2 and 3 for all four searching criteria.

5. Output results.

 

Suffice to say, this stuff isn't working. I've prodded the stuff for a while now, and this is pretty much what we have so far. I imagine there's a far more efficient way of performing the tasks I'm looking for?

 

		if ($_POST['usestatus'] === "yes")
	{
		//
		// yes, then add the condition
		//
		$conditions .= "AND ticketstatusid = '{$_POST['status']}' ";
	}

	//
	// build the SQL query from components
	//
	$sqlQuery = "SELECT $select FROM $table WHERE $conditions";
/*		print "SQL query is:<em>$sqlQuery</em><br>\n";
*/		
	//
	// send the query and establish
	// the response handle
	//
	$mySQLresult = mysql_query($sqlQuery, $mySQLconn) or die('mysql_query error: ' . mysql_error());

	//
	// each result row is an associative
	// array, pushed into the firstResults array
	//		
	$tickets = array();

	//
	// while there's a result row
	// push it into firstResults
	//
	while ($row = mysql_fetch_array($mySQLresult, MYSQL_ASSOC))
	{
		array_push($tickets, $row);
	}

	//
	// for each $tickets['ticketid'] retrieve the customfields
	// formatted 'customfieldid' => 'fieldvalue'
	// &$row construct makes it a reference, not a copy,
	// so we can change the original data
	//
	foreach ($tickets as &$row)
	{
		//
		// define the SQL query components
		// 
		$table = "swcustomfieldvalues";
		$select = "customfieldid, fieldvalue";
		$conditions = "typeid = '{$row['ticketid']}' ";

		//
		// create the SQL query and send it
		// $mySQLresult is the response handle
		//
		$sqlQuery = "SELECT $select FROM $table WHERE $conditions";
		$mySQLresult = mysql_query($sqlQuery, $mySQLconn) or die('mysql_query error: ' . mysql_error());

		//
		// while there's a result...
		// using the reference $row add the
		// result to the firstResults array
		//
		while ($row2 = mysql_fetch_assoc($mySQLresult))
		{
			$row["cfid{$row2['customfieldid']}"] = "{$row2['fieldvalue']}";
		}
	}
	//
	// done with the database
	// close the connection nicely
	//
	mysql_close($mySQLconn);

	//
	// no results, end gracefully
	//
	if (count($tickets) < 1)
	{
		print "<h2>No results for this query.</h2>\n";
		print "<a href=\"/sandbox/plantain.php\">Return</a>\n";
		print "</body>\n</html>\n";
		exit();
	}

	if ($_POST['usecallertype'] === "yes")
	{
		//
		// Caller Type filter,
		// cfid20 = $_POST['callertype']
		//
		// create filterTemp array to temporarily
		// hold the filtered results
		//
		$newTickets = array ();

		//
		// filter on $tickets['cfid20'] being equal
		// to $_POST['callertype']
		//
		$newTickets = filter_by_value ($tickets, 'cfid20', $_POST['callertype']);
	}

	if (($_POST['useprodtype'] === "yes") && (isset($newTickets)))
	{
		//
		// Product Type filter,
		// cfid16 = $_POST['prodtype']
		// newTickets exists
		//
		$filtered = array ();
		$filtered = filter_by_value ($newTickets, 'cfid16', $_POST['prodtype']);
		$newTickets = array ($filtered);
		unset ($filtered);		
	}
	else
	{
		//
		// Product Type filter,
		// cfid16 = $_POST['prodtype']
		//
		$newTickets = array ();
		$newTickets = filter_by_value ($tickets, 'cfid16', $_POST['prodtype']);
	}

	if (($_POST['useclassification'] === "yes") && (isset($newTickets)))
	{
		//
		// Classification filter,
		// cfid19 = $_POST['classification']
		// newTickets exists
		//
		$filtered = array ();
		$filtered = filter_by_value($newTickets, 'cfid19', $_POST['classification']);
		$newTickets = array ($filtered);
		unset ($filtered); 
	}
	else
	{
		//
		// Classification filter,
		// cfid19 = $_POST['classification']
		//
		$newTickets = array ();
		$newTickets = filter_by_value ($tickets, 'cfid19', $_POST['classification']);
	}

	$counter = 0;
	print "<table border=\"1\">\n";
	if (isset($newTickets))
	{
		foreach ($newTickets as $row)
		{
			print "<tr>\n";
			print "<td>$counter</td><td>{$row['ticketmaskid']}</td><td>{$row['departmentid']}</td>";
			print "<td>{$row['ticketstatusid']}</td><td>{$row['fullname']}</td><td>{$row['email']}</td>";
			print "<td>{$row['subject']}</td><td>{$row['cfid20']}</td><td>{$row['cfid16']}</td>";
			print "<td>{$row['cfid19']}</td>\n";
			print "</tr>\n";
			$counter++;
		}		
		print "</table>\n";
	}
	else
	{
		foreach ($tickets as $row)
		{
			print "<tr>\n";
			print "<td>$counter</td><td>{$row['ticketmaskid']}</td><td>{$row['departmentid']}</td>";
			print "<td>{$row['ticketstatusid']}</td><td>{$row['fullname']}</td><td>{$row['email']}</td>";
			print "<td>{$row['subject']}</td><td>{$row['cfid20']}</td><td>{$row['cfid16']}</td>";
			print "<td>{$row['cfid19']}</td>\n";
			print "</tr>\n";
			$counter++;
		}
		print "</table>\n";
	}

//
// this is the end of the "good dates"
// IF branch
//
}
/*
//
// development print
//
$count = count($firstResults);
$lastIndex = $count - 1;
print "<br>\$firstResults[$lastIndex] print_r\n";
print "<br><pre>\n";
var_dump($firstResults[$lastIndex]);
print "</pre><br>\n";
*/

function filter_by_value ($array, $index, $value)
{
if (is_array($array) && count($array) > 0)
{
	foreach (array_keys($array) as $key)
	{
		$temp[$key] = $array[$key][$index];
		if ($temp[$key] === $value)
		{
			$newarray[$key] = $array[$key];
		}
	}
}
return $newarray;
}

?>

[Psycho]

Well, there is a lot going on there. And, to be honest, I'm not going to read through all of that and try to understand it. However, I do see that you should be using JOINS in your queries.

 

At the beginning you are doing a query and dumping the results into an array. Then you do a foreach loop on the results and run a query for each result. insted you should just do a single query using a JOIN to get all the records in one query instead of many. I don't know what makes up the entire queries so I can't provide any guidance on how to create the properly joined query.