Help building query for search function

[chadwick37]

I'm trying to build a search function that will search all columns in a table. Here is what I've got:

 

if (isset($_POST['submitted'])) {
$q = "SELECT * FROM children ";

if(isset($_POST['query'])) {
$q .= "WHERE ";

$all = "SELECT * FROM children WHERE 1";
$r_all = mysqli_query($dbc, $all);

while ($field = $r_all->fetch_field()) { // Get field headers for building query
$f_name = "{$field->name} LIKE {$_POST['query']},:"; // Build query with search string
$fn = explode(":",$f_name); // Need to convert $f_name to an array but this does not convert it to a single array but multiple arrays

$q .= implode("OR", $fn); // Need to insert OR for the db query

} // End while ($field = $r_all->fetch_field()) 

} // End if(isset($_POST['query'])) 

} // End if (isset($_POST['submitted']))

 

The explode function is what is not working for me. It gives me this (sam is the search string I entered into the form):

 

Array ( [0] => child_id LIKE sam, [1] => ) Array ( [0] => first_name LIKE sam, [1] => ) Array ( [0] => second_name LIKE sam, [1] => ) Array ( [0] => last_name1 LIKE sam, [1] => ) Array ( [0] => last_name2 LIKE sam, [1] => ) Array ( [0] => gender LIKE sam, [1] => ) Array ( [0] => dob LIKE sam, [1] => )

 

so instead of getting one array with each string being a value I get multiple arrays. Any ideas?

[chadwick37]

I figured out my problem was doing the explode in the while statement which put everything into separate arrays. Now I have a new problem. The following code builds a db query and the query that it outputs works perfectly when I run it in phpMyAdmin. However, when I run a search that should output more than one result, and I've tested it in phpMyAdmin, I only end up with one result in output array. I can't figure out why it isn't getting all results, only the first one.

 

if (isset($_POST['search'])) { // Build the search query
$sq = "SELECT child_id FROM children ";

if ($_POST['query'] == "" && $_POST['loc'] == "any" && $_POST['status'] == "any") {
	echo' 
	<p class="error">You did not enter any search criteria.</p>
	';
} else {


	if(isset($_POST['query']) && $_POST['query'] != "") {
		$query = htmlspecialchars(trim($_POST['query']));
		$sq .= "WHERE (";

		$all = "SELECT * FROM children WHERE 1";
		$r_all = mysqli_query($dbc, $all);

		while ($field = $r_all->fetch_field()) {
		$fields[] = $field->name;
		} // End 	while ($field = $r_all->fetch_field())

		$sq .= implode(" LIKE '%{$query}%' OR ", $fields);
		$sq .= " LIKE '%{$query}%' ";
	} // End if(isset($_POST['query'])) 

	if(isset($_POST['loc']) && $_POST['loc'] != "any") {
		$loc = $_POST['loc'];
		if ($_POST['query'] == "") {
		$sq .= " WHERE (location_id = {$loc} ";
		} else {
		$sq .= " AND location_id = {$loc} ";
		}
	}
	if(isset($_POST['status']) && $_POST['status'] != "any") {
		$status = $_POST['status'];
		if ($_POST['query'] == "" && $_POST['loc'] == "any") {
		$sq .= " WHERE (status = {$status} ";
		} else {
		$sq .= " AND status = {$status} ";
		}
	}

} //End else

$sq .= ")";

if (isset($sq)) {
		$sr = mysqli_query($dbc, $sq);
		$count = count($sr);
		echo $count; // to check how many results are returned, only ever returns one result
		echo $sq; // to check the query used so I can run it in phpMyAdmin and it always checks out perfectly and will return multiple results.
	$sa = mysqli_fetch_array($sr, MYSQLI_ASSOC);

}
} // End if (isset($_POST['search']))

[litebearer]

this will only get 1 row...

$sa = mysqli_fetch_array($sr, MYSQLI_ASSOC);

 

You need to use a WHILE loop to get multiple rows

ie.

 

WHILE($sa = mysqli_fetch_array($sr, MYSQLI_ASSOC){
  echo $sa['fieldnamehere] . "</br /";
}

 

[chadwick37]

Yep, that was it. Thank you!