Jump to content

Database query with multiple values...

philwgreen

By philwgreen
in PHP Coding Help

Recommended Posts

philwgreen Newbie

philwgreen

Posted
Posted

I know just enough PHP to be dangerous and I'm trying to build a site where the user is able to sort products by multiple criteria (i.e. shape, length, width).

 

I have a working example here:

http://server.kenwoodstudiosdallas.com/index3.php

 

However, with my limited knowledge I feel like my code is bloated and there is a much more efficient way to accomplish what I'm trying to do.

 

The index file is here:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>

<link rel="stylesheet" href="<?php echo $root?>css/global.css" type="text/css" media="screen" />


</head>



<body>

<script language="javascript" type="text/javascript">

function validate(form, shape) {  


if(document.form1.shape1.checked == true){
var shape1 = 'oval';
//alert (shape1); 
}
else{
var shape1 = '-1';
//alert (shape1); 
}

if(document.form1.shape2.checked == true){
var shape2 = 'rectangular';
//alert (shape2); 
}
else{
var shape2 = '-1';
//alert (shape2); 
}

if(document.form1.shape3.checked == true){
var shape3 = 'corner';
//alert (shape2); 
}
else{
var shape3 = '-1';
//alert (shape2); 
}

if(document.form1.shape4.checked == true){
var shape4 = 'round';
//alert (shape2); 
}
else{
var shape4 = '-1';
//alert (shape2); 
}

if(document.form1.shape5.checked == true){
var shape5 = 'square';
//alert (shape2); 
}
else{
var shape5 = '-1';
//alert (shape2); 
}


if(document.form1.length1.checked == true){
var length1 = '1';
//alert (length); 
}
else{
var length1 = '-1';
//alert (length); 
}

if(document.form1.length2.checked == true){
var length2 = '2';
//alert (length); 
}
else{
var length2 = '-1';
//alert (length); 
}

if(document.form1.length3.checked == true){
var length3 = '3';
//alert (length); 
}
else{
var length3 = '-1';
//alert (length); 
}

if(document.form1.length4.checked == true){
var length4 = '4';
//alert (length); 
}
else{
var length4 = '-1';
//alert (length); 
}

if(document.form1.width1.checked == true){
var width1 = '1';
//alert (width); 
}
else{
var width1 = '-1';
//alert (width); 
}

if(document.form1.width2.checked == true){
var width2 = '2';
//alert (width); 
}
else{
var width2 = '-1';
//alert (width); 
}

if(document.form1.width3.checked == true){
var width3 = '3';
//alert (width); 
}
else{
var width3 = '-1';
//alert (width); 
}

if(document.form1.width4.checked == true){
var width4 = '4';
//alert (width); 
}
else{
var width4 = '-1';
//alert (width); 
}




if(shape1 == "") {  
//alert("select names");  
} else {  
//alert (length1); 


var ajaxRequest;  // The variable that makes Ajax possible!

try{
	// Opera 8.0+, Firefox, Safari
	ajaxRequest = new XMLHttpRequest();
} catch (e){
	// Internet Explorer Browsers
	try{
		ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
	} catch (e) {
		try{
			ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
		} catch (e){
			// Something went wrong
			alert("Your browser broke!");
			return false;
		}
	}
}
// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
	if(ajaxRequest.readyState == 4){
		var ajaxDisplay = document.getElementById('ajaxDiv');
		ajaxDisplay.innerHTML = ajaxRequest.responseText;
	}
}
var queryString = "?shape1=" + shape1  + "&" + "shape2=" + shape2 + "&" + "shape3=" + shape3 + "&" + "shape4=" + shape4 + "&" + "shape5=" + shape5 + "&" + "length1=" + length1 + "&" + "length2=" + length2 + "&" + "length3=" + length3 + "&" + "length4=" + length4 + "&" + "width1=" + width1 + "&" + "width2=" + width2 + "&" + "width3=" + width3 + "&" + "width4=" + width4;
ajaxRequest.open("GET", "ajax-example.php" + queryString, true);
ajaxRequest.send(null); 

//alert (queryString); 
}  
return false;  
}  


</script>

<div id="sorting">

<form name="form1" id="form1" onsubmit="return validate(this)">  
Shape:
<br />
<br />
<input type="checkbox" name="shape1" value=1 onclick="validate(this, 'shape', '')">Oval 
<br />
<input type="checkbox" name="shape2" value=2 onclick="validate(this, 'shape', '')">Rectangular 
<br />
<input type="checkbox" name="shape3" value=3 onclick="validate(this, 'shape', '')">Corner 
<br />
<input type="checkbox" name="shape4" value=4 onclick="validate(this, 'shape', '')">Round 
<br />
<input type="checkbox" name="shape5" value=5 onclick="validate(this, 'shape', '')">Square  
<br />
<br />
Length:
<br />
<br />
<input type="checkbox" name="length1" value=6 onclick="validate(this, 'shape')">Under 5'
<br />
<input type="checkbox" name="length2" value=7 onclick="validate(this, 'shape')">5' - 5.5'
<br />
<input type="checkbox" name="length3" value=8 onclick="validate(this, 'shape')">5.5' - 6'
<br />
<input type="checkbox" name="length4" value=9 onclick="validate(this, 'shape')">6' +
<br />
<br />
Width:
<br />
<br />
<input type="checkbox" id="width1" name="width1" value=10 onclick="validate(this, 'shape')">32"  
<br />
<input type="checkbox" id="width2" name="width2" value=11 onclick="validate(this, 'shape')">36"  
<br />
<input type="checkbox" id="width3" name="width3" value=12 onclick="validate(this, 'shape')">42"  
<br />
<input type="checkbox" id="width4" name="width4" value=13 onclick="validate(this, 'shape')">46" +  
</form>  
</div>


<div id='ajaxDiv'>

<?php

$dbhost = "localhost";
$dbuser = "xxxx";
$dbpass = "xxxx";
$dbname = "xxxx";

//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String

$results = "SELECT * FROM products ";


//build query
$query = $results;

//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
 echo "<img src='{$row['imageURL']}' />";

}

echo "Query: " . $query . "<br />";	

?>

</div>


<br />
<br />


</body>
</html>

 

The php file (ajax-example.php) called by the javascript in index3.php is here:


//$category = $_GET['category'];
$shape1 = $_GET['shape1'];
$shape2 = $_GET['shape2'];
$shape3 = $_GET['shape3'];
$shape4 = $_GET['shape4'];
$shape5 = $_GET['shape5'];
$length1 = $_GET['length1'];
$length2 = $_GET['length2'];
$length3 = $_GET['length3'];
$length4 = $_GET['length4'];
$width1 = $_GET['width1'];
$width2 = $_GET['width2'];
$width3 = $_GET['width3'];
$width4 = $_GET['width4'];


// how many rows to show per page
$rowsPerPage = 9;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;


// IF NO LENGTH AND NO WIDTH
if($length1 == '-1' && $length2 == '-1' && $length3 == '-1' && $length4 == '-1' && $width1 == '-1' && $width2 == '-1' && $width3 == '-1' && $width4 == '-1'){
// IF NO SHAPE THEN SHOW ALL PRODUCTS
if($shape1 == '-1' && $shape2 == '-1' && $shape3 == '-1' && $shape4 == '-1' && $shape5 == '-1'){
	$where_statement = "SELECT * FROM products ";
}
// IF NO LENGTH AND WIDTH BUT THERE IS A SHAPE THEN SHOW ALL SHAPE MATCHES
else {
	$where_statement = "SELECT * FROM products WHERE shape = '$shape1' OR shape = '$shape2' OR shape = '$shape3' OR shape = '$shape4' OR shape = '$shape5'";// . "LIMIT $offset, $rowsPerPage";
}
}

//IF THERE IS A LENGTH BUT NO WIDTH
else if(($length1 != '-1' || $length2 != '-1'  || $length3 != '-1' || $length4 != '-1') && ($width1 == '-1' && $width2 == '-1' && $width3 == '-1' && $width4 == '-1')){
// IF THERE IS ALSO NO SHAPE JUST SHOW LENGTH MATCHES
if($shape1 == '-1' && $shape2 == '-1' && $shape3 == '-1' && $shape4 == '-1' && $shape5 == '-1'){
	$where_statement = "SELECT * FROM products WHERE length = '$length1' OR length = '$length2' OR length = '$length3' OR length = '$length4' ";
	echo $width1;
}
// THERE IS A LENGTH AND A SHAPE
else {
	$where_statement = "SELECT * FROM products WHERE (shape = '$shape1' OR shape = '$shape2' OR shape = '$shape3' OR shape = '$shape4' OR shape = '$shape5') AND (length = '$length1' OR length = '$length2' OR length = '$length3' OR length = '$length4') ";
}
}

//IF THERE IS NO LENGTH BUT THERE IS A WIDTH
else if(($length1 == '-1' && $length2 == '-1'  && $length3 == '-1' && $length4 == '-1') && ($width1 != '-1' || $width2 != '-1' || $width3 != '-1' || $width4 != '-1')){
// IF THERE IS NO SHAPE THEN JUST SHOW WIDTH
if($shape1 == '-1' && $shape2 == '-1' && $shape3 == '-1' && $shape4 == '-1' && $shape5 == '-1'){
	$where_statement = "SELECT * FROM products WHERE width = '$width1' OR width = '$width2' OR width = '$width3' OR width = '$width4' ";
}
else {
	$where_statement = "SELECT * FROM products WHERE (shape = '$shape1' OR shape = '$shape2' OR shape = '$shape3' OR shape = '$shape4' OR shape = '$shape5') AND (width = '$width1' OR width = '$width2' OR width = '$width3' OR width = '$width4') ";
}
}

//IF THERE IS A LENGTH AND A WIDTH
else if(($length1 != '-1' || $length2 != '-1'  || $length3 != '-1' || $length4 != '-1') && ($width1 != '-1'  || $width2 != '-1' || $width3 != '-1' || $width4 != '-1')){
// BUT THERE IS NO SHAPE
if($shape1 == '-1' && $shape2 == '-1' && $shape3 == '-1' && $shape4 == '-1' && $shape5 == '-1'){
	$where_statement = "SELECT * FROM products WHERE (length = '$length1' OR length = '$length2' OR length = '$length3' OR length = '$length4') AND (width = '$width1' OR width = '$width2' OR width = '$width3' OR width = '$width4') ";
}
// THE TRIFECTA....LENGTH, SHAPE & WIDTH
else {
	$where_statement = "SELECT * FROM products WHERE (shape = '$shape1' OR shape = '$shape2' OR shape = '$shape3' OR shape = '$shape4' OR shape = '$shape5') AND (length = '$length1' OR length = '$length2' OR length = '$length3' OR length = '$length4') AND (width = '$width1' OR width = '$width2' OR width = '$width3' OR width = '$width4') ";
}
}


//Build query
$query = $where_statement;


//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

if ( mysql_num_rows( $qry_result ) > 0 ) 
{ 


echo "Query: " . $query . "<br />";	

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
echo "<img src='{$row['imageURL']}' />";

}

} 
else 
{ 
echo 'There are no prodcuts matching that criteria. <br />'; 
echo "Query: " . $query . "<br />";	
}

 

The database has one table 'products' with the following columns (ID, shape, length, width & imageURL). So, with that information could anyone take a look at this and let me know if I'm way off in how I've chosen to approach this? Any help is greatly appreciated.

 

Thanks.

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/145512-database-query-with-multiple-values/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.