dynamic MySQL select statement

cthorn112 · May 27, 2011, 6:13pm

Hello,

I’m putting together an internal company website. Right now its taking two fields firstName and lastName but I will be adding more fields (address,city,state,zip…etc). I want to create a dynamic select statement that will look at what fields the user has entered and use those in the select statement. I did some research and found two query generators/builders (http://www.phpkode.com/source/s/mysql-query-generator/mysql-query-generator/mquery.class.php and http://code.google.com/p/mysql-query-builder/source/browse/trunk/BasicQuery.php?spec=svn23&r=23). I tried applying them to my code with no success. Is this the approach I should be taking or something else ? I have posted my php file below. Thanks in advance.

[php]

<?php $dbhost = "localhost"; $dbuser = "root"; $dbpass = "test"; $dbname = "test"; //Connect to MySQL Server mysql_connect($dbhost, $dbuser, $dbpass); //Select Database mysql_select_db($dbname) or die(mysql_error()); // Retrieve data from Query String $firstName = $_GET['firstName']; $lastName = $_GET['lastName']; // Escape User Input to help prevent SQL Injection $firstName = mysql_real_escape_string($firstName); $lastName = mysql_real_escape_string($lastName); //build query $query = "SELECT * FROM `swipemaster` WHERE `FirstName` = '$firstName' AND `LastName` = '$lastName'"; //Execute query $qry_result = mysql_query($query) or die(mysql_error()); //Build Result String $display_string = ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; // Insert a new row in the table for each person returned while($row = mysql_fetch_array($qry_result)){ $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; $display_string .= ""; } //echo "Query: " . $query . "
"; $display_string .= "

First Name	Middle Name	Last Name	Suffix	Address	City	State	Zip Code	License Number	Expiration	Birthdate	Gender	Status	Date Scanned	Time Scanned	Banned	DJ	Opt
$row[firstName]	$row[middleName]	$row[lastName]	$row[suffix]	$row[address]	$row[city]	$row[state]	$row[zipCode]	$row[licenseNumber]	$row[expiration]	$row[birthdate]	$row[gender]	$row[status]	$row[dateScanned]	$row[timeScanned]	$row[banned]	$row[dj]	$row[opt]

"; echo $display_string; ?>

[/php]

-Chris

phphelp · May 27, 2011, 6:33pm

I want to create a dynamic select statement that will look at what fields the user has entered and use those in the select statement.

Not sure what kind of generator do you talk about... You already have a sql query by First/Last name. Next thing what you need is HTML form with fields where users can enter their values: First name, Last name, Address, City, etc. - any other fields what you like to query by.

cthorn112 · May 27, 2011, 7:19pm

I already have that. My apologies for not attaching it. The code is below. What I’m referring to is, what if the user only types in the firstName but not the last name or vice versa. For example if I type in smith for the lastName and nothing for firstName. The only results returned would be where smith is the lastName and firstName is blank or null. The desired result would be where smith is the last name and firstName is not in the select statement because the user did not type anything in. Hope that makes sense.

<html>
	<head>
		<title> Scans Page </title>
		<!-- <author> Chris Thorndike </author> -->
		<script language="javascript" type="text/javascript">
		<!-- 
		// Browser Support Code
		
		function jsFunctions() {
			if(formValidator()==true)
			{
			ajaxFunction();
			}
			return false;
		}
		
		function ajaxFunction(){
			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 firstName = document.getElementById('firstName').value;
			var lastName = document.getElementById('lastName').value;
			var queryString = "?firstName=" + firstName + "&lastName=" + lastName;
			ajaxRequest.open("GET", "id.php" + queryString, true);
			ajaxRequest.send(null); 
		}
		

		//-->
		</script>
	</head>
<body>

<form name='scanForm'>
First Name: <input type='text' id='firstName' /> <br />
Last Name: <input type='text' class='required' id='lastName' />
<br />
<input type='button' onclick='ajaxFunction()' value='Submit' />
</form>
<div id='ajaxDiv'>Your result will display here</div>
</body>
</html>

phphelp · May 27, 2011, 7:31pm

[php]$firstName = $_GET[‘firstName’];
$lastName = $_GET[‘lastName’];

$firstName = mysql_real_escape_string($firstName);
$lastName = mysql_real_escape_string($lastName);

$where = “”;
if($firstName!=’’) $where.=($where==""?"":" AND “).”FirstName = ‘$firstName’";
if($lastName!=’’) $where.=($where==""?"":" AND “).”LastName = ‘$lastName’";
// add any other conditions here

if($where!="") $where = " WHERE ".$where;

$query = "SELECT * FROM swipemaster".$where;[/php]

cthorn112 · May 27, 2011, 7:54pm

that did it thank you very much phphelp !!

-Chris