Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jul 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Code:
    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?


    Code:
    <?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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    It is simple, and it's one of those things where once you know how it's done you say "of course"!

    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:

    PHP Code:
    $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:

    PHP Code:
    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.

  • #3
    New Coder
    Join Date
    Jul 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    WHERE 1 means always. It's not really necessary as the WHERE part is optional.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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. You may want to look into it-- very handy. (Handles google-style search strings such as "+flower -daisy")

  • #6
    Regular Coder
    Join Date
    Apr 2006
    Posts
    231
    Thanks
    9
    Thanked 1 Time in 1 Post
    Your quotes, single quotes, and apostrophies are a little messed up. Sort them out.

  • #7
    New Coder
    Join Date
    Jul 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?


    Code:
            $sql = "UPDATE facform SET ";
    
    	foreach ($_POST as $key => $value) {
    
    		if($key != 'submit' && $value != ''){
    		$sql .= "$key = $value, ";
    		}
    	}
    
    	$sql .= "WHERE id = $id";

  • #8
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHP Code:
    $sql "UPDATE facform SET ";
    $sep "";
    foreach (
    $_POST as $key => $value) {

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

    $sql .= "WHERE id = $id"

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That is a clever way Illin!

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

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

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

  • #10
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    PHP Code:
    $sets = array();

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

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •