Jump to content

Dynamic Form with variable MySQL query

erichpowell

By erichpowell
in PHP Coding Help

 Share

Recommended Posts

erichpowell Newbie

erichpowell

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/130169-dynamic-form-with-variable-mysql-query/
Share on other sites
laPistola Member

laPistola

Posted
Posted

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 Newbie

erichpowell

Posted
  • Author
Posted

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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