Jump to content

Two fields into one database cell?

Vernexto

By Vernexto
in MySQL Help

 Share

Recommended Posts

Vernexto Newbie

Vernexto

Posted
Posted

Hi all!

 

First time posting here, though I've found this site to be incredibly resourceful.  I'm a novice at Mysql and PHP, though I've successfully developed a rather rudimentary database that fits my needs.  I am, however, having one slight problem that I can't quite figure out.

 

One field of my PHP-based form allows the user to select from a list of cities in which they reside.  The names of the cities have been pre-entered into the database when I built it, and are based on a list of cities from census data from 1998.  The user selection populates a field in the MySQL database named "city".  There are, however, some users that do not find their city in the list, and have to choose "Other" from the list, and manually enter the name of their city into a separate text field.  I have the form set up so that, if a user does this, the "city" field shows "other" and the user-entered text shows up in a field named "city_custom".  As you can imagine, it's quite bothersome, as I would like whatever the user types into "city_custom" to go into "city".

 

I guess, for lack of better phrasing, I am trying to figure out a way so that whenever the user selects the choice "other" in the drop-down menu, the database does not populate the "city" field with the word "other", but rather populates with whatever they manually type into "city_custom".  Hope this makes sense and that someone can advise!  Thanks!

Link to comment
Share on other sites
Mchl Regular Member

Mchl

Posted
Posted

The easiest way would be to do it when form processing takes place

 

if($_POST['city'] == "other") {
  $city = $_POST['city_custom'];
} else {
  $city = $_POST['city'];
}

//do not forget about sanitizing data

$sql = "INSERT INTO table (city) VALUES ($city)"; //query example

Link to comment
Share on other sites
  • 3 weeks later...
Vernexto Newbie

Vernexto

Posted
  • Author
Posted

Hi Mchl,

 

Thanks for your reply.  I entirely understand the concept you're going for here, but I just can't seem to get it to work.  The course/courseother was just an example, but I'm going to put some of the code below and hope you might be able to help me figure this out.

 

<?php require_once('../Connections/kerr.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "invalid.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $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;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO reserve (title, `catalog`, location, professor, course, quarter, `year`, owner, format, runtime, status, available, checkedinattendant, filmid, courseother) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['catalog'], "text"),
                       GetSQLValueString($_POST['location'], "text"),
                       GetSQLValueString($_POST['professor'], "text"),
                       GetSQLValueString($_POST['course'], "text"),
                       GetSQLValueString($_POST['quarter'], "text"),
                       GetSQLValueString($_POST['year'], "text"),
                       GetSQLValueString($_POST['owner'], "text"),
                       GetSQLValueString($_POST['format'], "text"),
                       GetSQLValueString($_POST['runtime'], "text"),
                       GetSQLValueString($_POST['status'], "text"),
                       GetSQLValueString($_POST['available'], "text"),
                       GetSQLValueString($_POST['checkedinattendant'], "text"),
                       GetSQLValueString($_POST['filmid'], "int"),
                       GetSQLValueString($_POST['courseother'], "text"));
  
  mysql_select_db($database_kerr, $kerr);
  $Result1 = mysql_query($insertSQL, $kerr) or die(mysql_error());

  $insertGoTo = "successful.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_kerr, $kerr);
$query_Recordset1 = "SELECT course FROM reserve ORDER BY course ASC";
$Recordset1 = mysql_query($query_Recordset1, $kerr) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$query_Recordset1 = "SELECT course, courseother FROM reserve ORDER BY course ASC";
$Recordset1 = mysql_query($query_Recordset1, $kerr) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_kerr, $kerr);
$query_admin_recordset = "SELECT adminname FROM `admin` ORDER BY adminname ASC";
$admin_recordset = mysql_query($query_admin_recordset, $kerr) or die(mysql_error());
$row_admin_recordset = mysql_fetch_assoc($admin_recordset);
$totalRows_admin_recordset = mysql_num_rows($admin_recordset);

mysql_select_db($database_kerr, $kerr);
$query_Recordset2 = "SELECT courseother FROM reserve";
$Recordset2 = mysql_query($query_Recordset2, $kerr) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
$query_Recordset1 = "SELECT DISTINCT course FROM reserve ORDER BY course ASC";
$Recordset1 = mysql_query($query_Recordset1, $kerr) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!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>Kerr Hall Learning Lab - Enter New Reserve</title>
<style type="text/css">
<!--
body {
font: 100% Verdana, Arial, Helvetica, sans-serif;
background: #666666;
margin: 0; /* it's good practice to zero the margin and padding of the body element to account for differing browser defaults */
padding: 0;
text-align: center; /* this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector */
color: #000000;
}
.oneColFixCtrHdr #container {
width: 780px;  /* using 20px less than a full 800px width allows for browser chrome and avoids a horizontal scroll bar */
background: #FFFFFF;
margin: 0 auto; /* the auto margins (in conjunction with a width) center the page */
border: 1px solid #000000;
text-align: left; /* this overrides the text-align: center on the body element. */
}
.oneColFixCtrHdr #header {
background: #DDDDDD; 
padding: 0 10px 0 20px;  /* this padding matches the left alignment of the elements in the divs that appear beneath it. If an image is used in the #header instead of text, you may want to remove the padding. */
}
.oneColFixCtrHdr #header h1 {
margin: 0; /* zeroing the margin of the last element in the #header div will avoid margin collapse - an unexplainable space between divs. If the div has a border around it, this is not necessary as that also avoids the margin collapse */
padding: 10px 0; /* using padding instead of margin will allow you to keep the element away from the edges of the div */
}
.oneColFixCtrHdr #mainContent {
padding: 0 20px; /* remember that padding is the space inside the div box and margin is the space outside the div box */
background: #FFFFFF;
}
.oneColFixCtrHdr #footer {
padding: 0 10px; /* this padding matches the left alignment of the elements in the divs that appear above it. */
background:#DDDDDD;
}
.oneColFixCtrHdr #footer p {
margin: 0; /* zeroing the margins of the first element in the footer will avoid the possibility of margin collapse - a space between divs */
padding: 10px 0; /* padding on this element will create space, just as the the margin would have, without the margin collapse issue */
}
-->
</style></head>

<body class="oneColFixCtrHdr">

<div id="container">
  <div id="header">
    <h1>Kerr Hall Learning Lab - Lab Attendants</h1>
    <!-- end #header --></div>
  <div id="mainContent">
    <h1> Enter New Reserve DVD, VHS, or LD</h1>
   
    
    <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
      <table align="center">
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Title:</td>
          <td><input type="text" name="title" value="" size="32" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Catalog:</td>
          <td><input type="text" name="catalog" value="" size="32" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Location:</td>
          <td><select name="location" type="text">
          <option value="#-B Drawer">#-B Drawer</option>
          <option value="C-E Drawer">C-E Drawer</option>
          <option value="F-H Drawer">F-H Drawer</option>
          <option value="I-L Drawer">I-L Drawer</option>
          <option value="M-O Drawer">M-O Drawer</option>
          <option value="P-R Drawer">P-R Drawer</option>
          <option value="S-V Drawer">S-V Drawer</option>
          <option value="W-Z Drawer">W-Z Drawer</option>
          <option value="Z Drawer">Video Game Drawer (Z)</option>
          </select>
          
          </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Owner:</td>
          <td><select name="owner" type="text">
          <option value="Video Services">Video Services</option>
          <option value="Film Studies">Film Studies</option>
          <option value="Professor">Professor</option>
          <option value="Other">Other</option>
       
          </select>
          
          </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Professor:</td>
          <td><input type="text" name="professor" value="" size="32" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Course:</td>
          <td><select name="course" type="text">
            <option value="other">Other</option>
            <?php
do {  
?>
            <option value="<?php echo $row_Recordset1['course']?>"><?php echo $row_Recordset1['course']?></option>
            <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
          </select> Other:
          <input type="text" name="courseother" value="" size="16" />


          </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Quarter:</td>
          <td><select name="quarter" type="text">
          <option value="Spring">Spring</option>
          <option value="Summer">Summer</option>
          <option value="Summer Session A">Summer Session A</option>
          <option value="Summer Session B">Summer Session B</option>
          <option value="Fall">Fall</option>
          <option value="Winter">Winter</option>
          </select>
          <select name="year" type="text">
          <option value="2009">2009</option>
          <option value="2008">2008</option>
          <option value="2007">2007</option>
          <option value="2006">2006</option>
          <option value="2005">2005</option>
          </select>
          
          </td>
        </tr>
        
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Format:</td>
          <td><select name="format" type="text">
          <option value="DVD">DVD</option>
          <option value="VHS">VHS</option>
          <option value="Laserdisc">Laserdisc</option>
          <option value="VG">Video Game</option>
          <option value="Other">Other</option>
          </select>
          </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Runtime:</td>
          <td><input type="text" name="runtime" value="" size="32" />
            <label>
            <input name="status" type="hidden" id="status" value="Not Checked Out" checked="checked" />
          </label>
          <input name="filmid" type="hidden" checked="checked"  />
          </td>
        </tr>
       
          <td><input name="available" type="hidden" value="Available" checked="checked" /></td>
       
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Checked In By:</td>
          <td><select name="checkedinattendant" type="text">
            <?php
do {  
?>
            <option value="<?php echo $row_admin_recordset['adminname']?>"><?php echo $row_admin_recordset['adminname']?></option>
            <?php
} while ($row_admin_recordset = mysql_fetch_assoc($admin_recordset));
  $rows = mysql_num_rows($admin_recordset);
  if($rows > 0) {
      mysql_data_seek($admin_recordset, 0);
  $row_admin_recordset = mysql_fetch_assoc($admin_recordset);
  }
?>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right"> </td>
          <td><input type="submit" value="Submit" /></td>
        </tr>
      </table>
      <input type="hidden" name="MM_insert" value="form1" />
      <!-- end #mainContent -->
    </form>
    </div>
  <center><p>Note to all Lab Attendants; when entering a new reserve film, please make sure to use applicable department codes.  For example, in the film is for course "Film Studies 46," please enter the course as "FLMST 46."  If the course is for "Writing 2," please enter the course as "WRIT 2."  Thank you!</p></center>
  <div id="footer">
    <p>© 2009 Brandon Koch</p>
  <!-- end #footer --></div>
<!-- end #container --></div>
</body>
</html>
<?php
mysql_free_result($Recordset1);

mysql_free_result($admin_recordset);

mysql_free_result($Recordset2);
?>

 

Essentially, I'm trying to have the field "course," when its drop-down menu is set to "other," take the contents from "courseother" and put the "courseother" contents in the "course" field.  I had presumed taking the code you supplied me in the previous post (obviously changing the example city/city_custom names and replacing them with the respective course/courseother) and placing that code just after the final GetSQLValueString($_POST...) would do the trick, but it simply did not seem to work.

 

Any help you can provide would be immensely appreciated!

Link to comment
Share on other sites
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.