www.webdeveloper.com
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 50

Thread: Search Function with multiple variables in SQL

  1. #1
    Join Date
    Apr 2009
    Posts
    107

    Search Function with multiple variables in SQL

    Hello All,

    I am currently making a product selection guide that with multiple variables. One the first page, there are 4 drop downs that let the user select various options. Here is the code:

    PHP Code:
        <form name="form" action="product_view.php" method="get">Product Search 
                    
    <select  name="tech" />
                    <
    option value=""></option>
                    <
    option value="1">Non-Contact</option>
                    <
    option value="2">Contact</option>
                    <
    option value="3">Prressure</option>
                    </
    select>
                    
                    <
    select  name="range" />
                    <
    option value=""></option>
                    <
    option value="1">2" - 49.2"</option>
                    <
    option value="2">4"-32.8'</option>
                    <option value="
    3">8"-32.8'</option>
                    </select>
                    
                    <select  name="output" />
                    <option value=""></option>
                    <option value="1">4-20 mA</option>
                    <option value="2">Voltage</option>
                    <option value="3">Frequency'
    </option>
                    </
    select>
                    
                    <
    select  name="config" />
                    <
    option value=""></option>
                    <
    option value="1">LCD</option>
                    <
    option value="2">WebCal</option>
                    </
    select>
                  <
    input type="submit" name="Submit" value="Search" /></form
    The results then show on the product_view.php. Here is that code:

    PHP Code:
    <html>
    <body>

    <?php 
    include "db.php";
    $tbl_name="product";

    $tech $_GET['tech'];
    $range $_GET['range'];
    $output $_GET['output'];
    $config $_GET['config'];

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$database")or die("cannot select DB");

    $total_result mysql_query"SELECT* FROM $tbl_name)  
        or die(
    "SELECT Error: ".mysql_error());  

    $num_rows mysql_num_rows($total_result);  

    // Retrieve data from database 
    $sql="SELECT * FROM $tbl_name WHERE technology = '$tech' AND range = '$range' AND output = '$output' AND configuration='$config'";

    $result=mysql_query($sql); 

    // Start looping rows in mysql database. 
    while($rows=mysql_fetch_array($result)){ 
    ?> 

    <p><?php echo $rows['product'];  ?></p>

    <?php
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>

    </body>
    </html>
    I believe I am coding my $sql variable. Can you code it like this?
    $sql="SELECT * FROM $tbl_name WHERE technology = '$tech' AND range = '$range' AND output = '$output' AND configuration='$config'";
    to have it include all the variables from the previous page? Any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2008
    Posts
    2,477
    I'm not sure I follow what your problem is? What is not working?

    NB your current code leaves you open to SQL injection, you're not doing any validation, cleaning or defensive coding at all. Also there is also no need at all for this:

    PHP Code:
    $tech $_GET['tech'];
    $range $_GET['range'];
    $output $_GET['output'];
    $config $_GET['config']; 

  3. #3
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by Mindzai View Post
    I'm not sure I follow what your problem is? What is not working?

    NB your current code leaves you open to SQL injection, you're not doing any validation, cleaning or defensive coding at all. Also there is also no need at all for this:

    PHP Code:
    $tech $_GET['tech'];
    $range $_GET['range'];
    $output $_GET['output'];
    $config $_GET['config']; 
    i'm not sure too, but the code up there is needet 4 the $sql =)

    for a better errorhandling rewrite

    PHP Code:
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$database")or die("cannot select DB"); 
    to

    PHP Code:
    mysql_connect($host$username$password)or die("cannot connect"); 
    mysql_select_db($database)or die("cannot select DB"); 
    and let the quotes left behind =)

    so, tell us, what is not working =)

  4. #4
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by STREETRULEZ View Post
    ...but the code up there is needet 4 the $sql
    Why is it needed for the SQL? It's just assigning one variable to another. There is no reason at all to do this. The code could just be written as:

    PHP Code:
    $sql="SELECT * FROM $tbl_name WHERE technology = '{$_GET['tech']}' AND range = '{$_GET['range']}' AND output = '{$_GET['output']}' AND configuration='{$_GET['config']}'"
    This saves 4 pointless variable re-assignments. I don't understand why so many people write code which re-assigns $_GET and $_POST values without performing any operation on them.

  5. #5
    Join Date
    Apr 2009
    Posts
    107
    The problem that I am having is I don't ever display results. The select should yield a result, but always displays a blank screen.

    What do you mean by
    NB your current code leaves you open to SQL injection, you're not doing any validation, cleaning or defensive coding at all.
    What things would need to be added?

  6. #6
    Join Date
    Apr 2009
    Posts
    107
    When I submit the form on config.php, here is the URL: http://10.0.0.123/selection/product_...&Submit=Search. So I know the variables are being passed. When I look at the sql, it matches those variables (i have them set up as INT columns). The product result does not display though. It is just a blank screen

  7. #7
    Join Date
    Feb 2008
    Location
    Boondocks, Ohio
    Posts
    127
    Add this to the top of your page:

    PHP Code:
     <?php error_reporting(E_ALL);
    ini_set('display_errors''1'); ?>
    and let us know if it displays an error instead of blank screen

  8. #8
    Join Date
    Apr 2009
    Posts
    107
    Here is the error that it reported instead of a blank screen
    Notice: Undefined variable: host in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 10

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 23

  9. #9
    Join Date
    Apr 2009
    Posts
    107
    I just fixed the line 10 error.

  10. #10
    Join Date
    Apr 2009
    Posts
    107
    <?php error_reporting(E_ALL);
    ini_set('display_errors', '1'); ?>
    Is this piece of code something I can use on all my php pages for error reporting and debugging?

  11. #11
    Join Date
    Feb 2008
    Location
    Boondocks, Ohio
    Posts
    127
    try switching this:
    PHP Code:
    while($rows=mysql_fetch_array($result)){ 
    with this:
    PHP Code:
    while ($rows=mysql_fetch_assoc($result)) { 

  12. #12
    Join Date
    Feb 2008
    Location
    Boondocks, Ohio
    Posts
    127
    yes i use that all the time sometimes hosts don't setup their servers to display all php errors, this is the workaround for that doomed blank white screen! !

  13. #13
    Join Date
    Apr 2009
    Posts
    107
    Ok switch the line of code with the one you suggested, now I get this error (I think the same as above, just spaced different).
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 23

  14. #14
    Join Date
    Apr 2009
    Posts
    107
    Here is all the code from that page:

    PHP Code:
    <html>
    <body>
    <?php error_reporting(E_ALL);
    ini_set('display_errors''1'); ?>
    <?php 
    include "db.php";
    $tbl_name="product";

    // Connect to server and select database.
    mysql_connect($hostname$username$password)or die("cannot connect"); 
    mysql_select_db($database)or die("cannot select DB"); 

    $total_result mysql_query"SELECT* FROM $tbl_name)  
        or die(
    "SELECT Error: ".mysql_error());  

    $num_rows mysql_num_rows($total_result);  

    // Retrieve data from database 
    $sql="SELECT * FROM $tbl_name WHERE technology = '{$_GET['technology']}' AND range = '{$_GET['range']}' AND output = '{$_GET['output']}' AND configuration='{$_GET['configuration']}'";
    $result=mysql_query($sql); 

    // Start looping rows in mysql database. 
    while ($rows=mysql_fetch_assoc($result)) { 
    ?> 

    <p><?php echo $rows['product'];  ?></p>

    <?php
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>

    </body>
    </html>

  15. #15
    Join Date
    Feb 2008
    Location
    Boondocks, Ohio
    Posts
    127
    Also - since it appears that your values are always numbers, there's a quick sql injection fix that you can use:

    PHP Code:
    <?php // Protect Against SQL Injection
    if (($_GET['SOMEVARIABLE'] > '') && (!is_numeric($_GET['SOMEVARIABLE']))) {
        die(
    "System Detected SQL Injection Attack");
    }
    ?>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles