simple filter of database with $_GET and select SQL

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

It is simple, and it's one of those things where once you know how it's done you say "of course"! :D

For each additional search criteria you want to add to the query, just append a bit of string to the end of the query using the string concantenator syntax ".", like this:


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

if (isset($_GET['f_name'])) {
$query . = "AND f_name LIKE '{$_GET['f_name']}%'";
}


If the names of your indices in the $_GET array are the same as your field names in the table, you can even put it all in a loop:


foreach ($_GET as $indexName => $arrayValue) {
$query .= "AND $indexName LIKE '$arrayValue%'";
}


But of course the problem with that is possible SQL injection from the query string, so you'd probably want to scrub the $_GET array first and eliminate index names that aren't valid.

That looks simple. I have couple of questions; what does WHERE 1 mean?
the % is that a wild card and what does it stand for?

WHERE 1 means always. It's not really necessary as the WHERE part is optional.

The "WHERE 1" is just to initiate the list of conditions for your query. If each condition is optional, then it's nice to be able to have a starting point such as WHERE 1 for all the optional conditions to follow.

The "%" in a LIKE clause is a wildcard, so it doesn't require a full match on the field. A search string "jone" will match "jones" and "joneston", for example.

There are actually better ways to do field searching-- with a fulltext index (http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html). You may want to look into it-- very handy. (Handles google-style search strings such as "+flower -daisy")

Your quotes, single quotes, and apostrophies are a little messed up. Sort them out.

I didn't want to start another thread since this is along the same topic

I am building an sql statement and I am using a foreach loop to create the statement. Everything looks good with the sql statement when I echo it; but the last update field has a comma , that is not needed. How to I account for the last $key => $value set in the array that match my criteria and not have a comma?



$sql = "UPDATE facform SET ";

foreach ($_POST as $key => $value) {

if($key != 'submit' && $value != ''){
$sql .= "$key = $value, ";
}
}

$sql .= "WHERE id = $id";

$sql = "UPDATE facform SET ";
$sep = "";
foreach ($_POST as $key => $value) {

if($key != 'submit' && $value != ''){
$sql .= $sep."$key = $value";
$sep = ",";
}
}

$sql .= "WHERE id = $id";


:)

That is a clever way Illin!

I have also seen people chop off that last comma after the loop is done with substr():


$sql = "UPDATE facform SET ";
foreach ($_POST as $key => $value) {

if($key != 'submit' && $value != ''){
$sql .= "$key = $value,";
}
}
$sql = substr($sql, 0, -1); //return everything but the last byte (which will be a comma)
$sql .= " WHERE id = $id";

$sets = array();

while ($row = mysql_fetch_assoc($result)) {
$sets[] = $row['key'] . " = " . $row['value'];
}

$sql = "UPDATE table SET " . implode(",", $sets) . " WHERE 1";