simple filter of database with $_GET and select SQL

[cedtech31]

I need help with my logic statement with a simple search engine on a MySQL table.

I have three form text input fields named f_name, l_name and dept. I am using the GET method to gather data the user enters in each input field.

 

So far I have been able to build a check to see that the submit has been clicked but I am having trouble with assembling the SQL statement because I need for the search engine to be able to filter the results based on the information provided in 1-3 of the fields.

 

I could use if statements with some and clauses but that would mean that I would have to account for each combination. Something like

 

If(($_GET[‘f_name’] != “”) || ($_GET[‘l_name’] !=) || ($_GET[‘dept] !=)){

$sql = "SELECT * FROM facform WHERE f_name LIKE '$_GET[f_name]',
l_name LIKE '$_GET[‘l_name]', dept LIKE '$_GET[dept]' ”;
}

 

The problem with this is that I would have to get every combination and it’s not very scalable. How can I create this SQL statement dynamically?

 

 

<?php
if (array_key_exists('submit', $_GET)) {

//SQL statement needed

$db_name = db_test;

$conn = mysql_connect('localhost', 'username', password'') or die(mysql_error());
$db = mysql_select_db($db_name, $conn);
$result = mysql_query($sql, $conn) or die(mysql_error());
}
?>

 

Can someone help be with my logic and solving this issue with limited steps? I have been using PHP for 2 weeks so please can you keep it simple

 

Thanks

 

[cedtech31]

Thanks I figured it out

 

$query = "SELECT * FROM table1 WHERE 1";

 

foreach ($_GET as $indexName => $arrayValue) {

    $query .= "AND $indexName LIKE '$arrayValue%'";

} 

[cedtech31]

<?php

if (array_key_exists('submit', $_GET)){
include('includes/connection.inc.php');
$conn = dbConnect();

$sql = "SELECT * FROM facform WHERE 1";

   	foreach ($_GET as $indexName => $arrayValue) {
    	if ($indexName != 'submit' && ($arrayValue != '') ) {
    		$sql .= " AND $indexName LIKE '$arrayValue%'";
    		}
    }

    $result = mysql_query($sql, $conn) or die(mysql_error());
}
?>

 

 

then in my the body of the PHP I have some XHTML for a table

 

<table>
	<tr>
		<th>First Name</th>
		<th>Last Name</th>
		<th>Department</th>
	<tr>
	<?php
	while($row = @mysql_fetch_assoc($result)) {
	?>
	<tr>
		<td><?php echo $row['f_name']; ?></td>
		<td><?php echo $row['l_name']; ?></td>
		<td><?php echo $row['dept']; ?></td>
		<td><?php echo '<a href="#">EDIT</a>'; ?></td>
	</tr>
	<?php } ?>
</table>

 

The issue I am having is if I leave off @ infront of

 

@mysql_fetch_assoc($result))

 

I get Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

 

is there a better way to correct this issue?

also how can I hide the TH tag and their connects until the $_GET submit button has been press?