Search Function with multiple variables in SQL

**themonkey40** · 06-15-2009 05:41 PM

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.

**Mindzai** · 06-16-2009 11:32 AM

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'];

**STREETRULEZ** · 06-16-2009 11:54 AM

Originally Posted by Mindzai

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 =)

**Mindzai** · 06-16-2009 12:17 PM

Originally Posted by STREETRULEZ

...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.

**themonkey40** · 06-16-2009 12:58 PM

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?

**themonkey40** · 06-16-2009 01:07 PM

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

**cinematic_jesi** · 06-16-2009 01:59 PM

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

**themonkey40** · 06-16-2009 02:03 PM

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

**themonkey40** · 06-16-2009 02:04 PM

I just fixed the line 10 error.

**themonkey40** · 06-16-2009 02:06 PM

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

**cinematic_jesi** · 06-16-2009 02:07 PM

try switching this:

PHP Code:


while($rows=mysql_fetch_array($result)){

with this:

PHP Code:


while ($rows=mysql_fetch_assoc($result)) {

**cinematic_jesi** · 06-16-2009 02:08 PM

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!

!

**themonkey40** · 06-16-2009 02:12 PM

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

**themonkey40** · 06-16-2009 02:13 PM

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>

**cinematic_jesi** · 06-16-2009 02:15 PM

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: Search Function with multiple variables in SQL

Thread Tools

Display

Search Function with multiple variables in SQL

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions