Dynamic Form with variable MySQL query

erichpowell · October 26, 2008

Apologies in advance, I'm completely new to PHP & MySQL and I started developing this page using DreamWeaver, which has left me with a bunch of code that is probably superfluous, but which I am scared to delete. Please bear with me.

The idea for the webpage is simple. It contains a form with 2 text fields & a submit button, and then there is a space below to display the matching records. The name of the page is 'srchPres2.php', which matches the "action" of the form, so the form should call itself.

I have it set to display all records in the database if no form fields are completed (so that's what happens when the page is initially loaded).

The problem arises when I try to fill one (or both) of the fields and create a new criteria with conditions based on those values. Based on some print statements it seems that only the value from the 'first name' field is being picked up and not the 'last name'. Even then though, I do not get a new query based on the 'first name' value. Any help would be greatly appreciated. I know that's a pretty big code dump below, and apologize for that, I just didn't know make it any smaller.

<?php require_once('Connections/getPres.php'); ?>

<?php
//Dreamweaver adds this function to check form value
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
//USED CHECK IF FORM FIELD IS EMPTY
function notEmpty($variable){
if($variable == NULL || $variable == ''){
$ntEmpty = false;
} 
else {
$ntEmpty = true;
}
return($ntEmpty);
} //end notEmpty function

$num_fields = 0; //Initiate counter for filled fields
$colname_FName = "-1";//Set field variables to 'missing'
$colname_LName = "-1";//......

//CHECK EACH FIELD TO SEE IF IT'S EMPTY
if (isset($_POST['Name']) && notEmpty($_POST['Name'])) {
  $colname_FName = $_POST['Name'];
  $num_fields++;
}
if (isset($_POST['Last Name']) && notEmpty($_POST['Last Name'])) {
  $colname_LName = $_POST['Last Name'];
  $num_fields++;
}

//Check values with print statements
print("Number of Filled Fields " . $num_fields . "<br>");
print("Correct First Name? " . $colname_FName . "<br>");
print("Correct Last Name? " . $colname_LName . "<br>");

mysql_select_db($database_getPres, $getPres);

//CREATE VARIABLE QUERY BASED ON NUMBER OF FILLED FIELDS
$query_Presidents = "SELECT * FROM test";

if (num_fields > 0) {
print($query_Presidents);
$query_Presidents = $query_Presidents . " WHERE "; 
if ($colname_LName != "-1") {
	$query_Presidents = $query_Presidents . "lname LIKE '" . GetSQLValueString($colname_LName, "text") . "%'";
	$num_fields--;
	if ($num_fields > 0) {
		$query_Presidents = $query_Presidents ." AND ";
	}
}
if ($colname_FName != "-1") {
	$query_Presidents = $query_Presidents . "fname LIKE '" . GetSQLValueString($colname_FName, "text") . "%'";
	$num_fields--;
}
}

print($query_Presidents . "<br>");

$Presidents = mysql_query($query_Presidents, $getPres) or die(mysql_error());
$row_Presidents = mysql_fetch_assoc($Presidents);
$totalRows_Presidents = mysql_num_rows($Presidents);
?> 



<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>President Search</title>
<link href="css/hilite.css" rel="stylesheet" type="text/css" />
</head>

<body>
<h1>Just a test...</h1>
<form name="form1" method="post" action="srchPres2.php">
  <p><label for="Name">Name:</label>
  <input type="text" name="Name" id="Name"></p>
  <p><label for="Last Name">Last Name</label>
  <input type="text" name="Last Name" id="Last Name"></p>
  <p><input type="submit" name="Submit" id="Submit" value="Submit" />
  </p>
</form>
<p> 
Records <?php echo ($startRow_Presidents + 1) ?> to <?php echo min($startRow_Presidents + $maxRows_Presidents, $totalRows_Presidents) ?> of <?php echo $totalRows_Presidents ?> </p>
<table width="400">
  <tr>
    <th scope="col">First Name</th>
    <th scope="col">Last Name</th>
  </tr>
  <?php $counter = 0; // initialize counter outside loop ?>
  <?php do { ?>
    <tr <?php if ($counter++ % 2) {echo 'class="hilite"';} ?>>  
      <td><?php echo $row_Presidents['fname']; ?></td>
      <td><?php echo $row_Presidents['lname']; ?></td>
    </tr>
    <?php } while ($row_Presidents = mysql_fetch_assoc($Presidents)); ?>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Presidents);
?>

laPistola · October 26, 2008

let me get this straight.

On page load you what it to display all the records in the recordset but if you type in and First and Last name and click go you wont it to then reload and display only the record that contains both that first name and that last name?

erichpowell · October 26, 2008

Yes, that's correct. Thanks a bunch for having a look.

I actually just took a little break and had an epiphany. I'm pretty sure I've got it solved now.

The first problem was that the name of one of my fields, 'last name', contains a space and it was not being recognized cause of that. Fixed by using underscore instead: 'last_name'.

Second problem was that the if statement (which checks whether any fields were filled) was missing a dollar sign.

Two noobie mistakes, what can I say

Thanks for having a look, though. Much appreciated.

I posted the corrected code below. Note, however, that the original code used a % (wildcard operator) in the SQL query, and this revised version doesn't. I realized that the Dreamweaver function GetSQLValueString is not compatible with that.

<?php require_once('Connections/getPres.php'); ?>

<?php
//Dreamweaver adds this function to check form value
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
//USED CHECK IF FORM FIELD IS EMPTY
function notEmpty($variable){
if($variable == NULL || $variable == ''){
$ntEmpty = false;
} 
else {
$ntEmpty = true;
}
return($ntEmpty);
} //end notEmpty function

$num_fields = 0; //Initiate counter for filled fields
$colname_FName = "-1";//Set field variables to 'missing'
$colname_LName = "-1";//......

//CHECK EACH FIELD TO SEE IF IT'S EMPTY
if (isset($_POST['Name']) && notEmpty($_POST['Name'])) {
  $colname_FName = $_POST['Name'];
  $num_fields++;
}
if (isset($_POST['Last_Name']) && notEmpty($_POST['Last_Name'])) {
  $colname_LName = $_POST['Last_Name'];
  $num_fields++;
}

//Check values with print statements
print("Number of Filled Fields " . $num_fields . "<br>");
print("Correct First Name? " . $colname_FName . "<br>");
print("Correct Last Name? " . $colname_LName . "<br>");
print("First Name: " . $_POST['Name']. "<br>");
print("Last Name: " . $_POST['Last_Name']. "<br>");

mysql_select_db($database_getPres, $getPres);

//CREATE VARIABLE QUERY BASED ON NUMBER OF FILLED FIELDS
$query_Presidents = "SELECT * FROM test";

if ($num_fields > 0) {
print($query_Presidents . "<br>");
$query_Presidents = $query_Presidents . " WHERE "; 
if ($colname_LName != "-1") {
	$query_Presidents = $query_Presidents . "lname LIKE " . GetSQLValueString($colname_LName, "text") ;
	$num_fields--;
	if ($num_fields > 0) {
		$query_Presidents = $query_Presidents ." AND ";
	}
}
if ($colname_FName != "-1") {
	$query_Presidents = $query_Presidents . "fname LIKE " . GetSQLValueString($colname_FName, "text") ;
	$num_fields--;
}
}

print($query_Presidents . "<br>");

$Presidents = mysql_query($query_Presidents, $getPres) or die(mysql_error());
$row_Presidents = mysql_fetch_assoc($Presidents);
$totalRows_Presidents = mysql_num_rows($Presidents);
?> 



<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>President Search</title>
<link href="css/hilite.css" rel="stylesheet" type="text/css" />
</head>


<body>
<h1>Just a test...</h1>
<form name="form1" method="post" action="srchPres2.php">
  <p><label for="Name">Name:</label>
  <input type="text" name="Name" id="Name"></p>
  <p><label for="Last Name">Last Name</label>
  <input type="text" name="Last_Name" id="Last Name"></p>
  <p><input type="submit" name="Submit" id="Submit" value="Submit" />
  </p>
</form>
<p> 
Records <?php echo ($startRow_Presidents + 1) ?> to <?php echo min($startRow_Presidents + $maxRows_Presidents, $totalRows_Presidents) ?> of <?php echo $totalRows_Presidents ?> </p>
<table width="400">
  <tr>
    <th scope="col">First Name</th>
    <th scope="col">Last Name</th>
  </tr>
  <?php $counter = 0; // initialize counter outside loop ?>
  <?php do { ?>
    <tr <?php if ($counter++ % 2) {echo 'class="hilite"';} ?>>  
      <td><?php echo $row_Presidents['fname']; ?></td>
      <td><?php echo $row_Presidents['lname']; ?></td>
    </tr>
    <?php } while ($row_Presidents = mysql_fetch_assoc($Presidents)); ?>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Presidents);
?>

DeanWhitehouse · October 26, 2008

my advice to you is to learn what you are doing and don't just rely on dreamweaver, go to sites like tizag.com and w3schools.com to learn about php and mysql

Sign In

Dynamic Form with variable MySQL query

Recommended Posts

erichpowell

Link to comment

Share on other sites

laPistola

Link to comment

Share on other sites

erichpowell

Link to comment

Share on other sites

DeanWhitehouse

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information