...

View Full Version : simple filter of database with $_GET and select SQL



cedtech23
03-22-2007, 07:44 PM
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

Fumigator
03-22-2007, 08:05 PM
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.

cedtech23
03-22-2007, 08:34 PM
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?

aedrin
03-22-2007, 09:15 PM
WHERE 1 means always. It's not really necessary as the WHERE part is optional.

Fumigator
03-22-2007, 11:24 PM
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")

Erindesign
03-23-2007, 01:21 AM
Your quotes, single quotes, and apostrophies are a little messed up. Sort them out.

cedtech23
03-23-2007, 07:11 AM
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";

iLLin
03-23-2007, 08:32 AM
$sql = "UPDATE facform SET ";
$sep = "";
foreach ($_POST as $key => $value) {

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

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


:)

Fumigator
03-23-2007, 08:39 PM
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";

aedrin
03-23-2007, 09:20 PM
$sets = array();

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

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum