Jump to content


Photo

changing text fields to drop downs


  • Please log in to reply
2 replies to this topic

#1 chimp

chimp
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 21 August 2006 - 10:58 PM

I have spent absolutely weeks playing around with this but cant get it to work no matter what.

I am trying to change a couple of fields in my php page from a text box to a drop down box.

Fields i am trying to change on my form are:

Php form name ----- Mysql store name

Beds              -----  Bed
Bathroom        -----  Bath
Year              -----  Year
Local Authority-----  Subdiv
Local Hospital  -----  Schoold
Grounds Acres -----  lot_size
Sqfeet          -----  sqft

I store the values of the drop down box in my mysql table

Table structure is as follows:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) default NULL,
  `description` text,
  `image` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (14, 'Yes', '-', '');
INSERT INTO `categories` VALUES (15, 'No', '-', '');

so in this case you can see I am just trying to use a yes or no value.

I want to store this in my table as a number

I already use the results from the drop down options in another field but cant seem to get this duplicated for my other fields.

my database store structure is as follows

CREATE TABLE `items` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) default NULL,
  `scid` int(11) default NULL,
  `mid` int(11) default NULL,
  `title` varchar(50) default NULL,
  `price` double default NULL,
  `special_price` double default NULL,
  `pdate` datetime default NULL,
  `sold` varchar(4) default NULL,
  `description` text,
  `address` varchar(50) default NULL,
  `city` varchar(45) default NULL,
  `state` varchar(45) default NULL,
  `zip` varchar(50) default NULL,
  `exp` int(11) default NULL,
  `featured` varchar(4) default NULL,
  `active` varchar(5) default NULL,
  `hits` int(11) default NULL,
  `bed` int(11) default NULL,
  `bath` int(11) default NULL,
  `subdiv` int(11) default NULL,
  `schoold` int(11) default NULL,
  `year` int(11) default NULL,
  `lot_size` int(11) default NULL,
  `sqft` int(11) default NULL,
  `lat` varchar(40) default NULL,
  `long` varchar(40) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

I want my php code to be able to update the database using only yes or no values from my categories field…

<?php require_once('Connections/myconn.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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']);
}

//begin debug
//print $_REQUEST['city'];
//die();
//end debug

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

//fetch logn and lat for address
$request_address = $_REQUEST['address'] . ', '.$_REQUEST['city'].' '.$_REQUEST['state'].' '.$_REQUEST['zip'];
require('admin/xmlparse.ini.php');
require('admin/llfetch.php');

  $updateSQL = sprintf("UPDATE items SET `long`=%s, `lat`=%s, cid=%s, title=%s, price=%s, special_price=%s, description=%s, address=%s, city=%s, `state`=%s, zip=%s, bed=".GetSQLValueString($_REQUEST['bed'],"text").", bath=".GetSQLValueString($_REQUEST['bath'],"text").", subdiv=".GetSQLValueString($_REQUEST['subdiv'],"text").", schoold = ".GetSQLValueString($_REQUEST['schoold'],"text").", year = ".GetSQLValueString($_REQUEST['year'],"int").", lot_size = ".GetSQLValueString($_REQUEST['lot_size'],"double").", sqft = ".GetSQLValueString($_REQUEST['sqft'],"double")." WHERE id=".GetSQLValueString($_REQUEST['id'],"int")."",
                       GetSQLValueString($address['long'], "text"),
                       GetSQLValueString($address['lat'], "text"),
                       GetSQLValueString($_POST['cat'], "int"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['price'], "double"),
                       GetSQLValueString($_POST['special_price'], "double"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['city'], "text"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['zip'], "text"),
                       GetSQLValueString($_POST['id'], "int")
					   );
//echo $updateSQL;
//die();
  mysql_select_db($database_myconn, $myconn);
  $Result1 = mysql_query($updateSQL, $myconn) or die(mysql_error());

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


?>
<? include('header.php'); ?>
<?php


//fetch all categories from database , and load into a assoc array
mysql_select_db($database_myconn, $myconn);
$query_cat = "SELECT * FROM categories ORDER BY title ASC";
$cat = mysql_query($query_cat, $myconn) or die(mysql_error());
$row_cat = mysql_fetch_assoc($cat);
$totalRows_cat = mysql_num_rows($cat);

//fetch member information from  datebase where email matches session varable
$colname_minfo = "1";
if (isset($_SESSION['MM_Username'])) {
  $colname_minfo = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_myconn, $myconn);
$query_minfo = sprintf("SELECT * FROM members WHERE email = '%s'", $colname_minfo);
$minfo = mysql_query($query_minfo, $myconn) or die(mysql_error());
$row_minfo = mysql_fetch_assoc($minfo);
$totalRows_minfo = mysql_num_rows($minfo);

mysql_select_db($database_myconn, $myconn);
$query_city = "SELECT * FROM city ORDER BY title ASC";
$city = mysql_query($query_city, $myconn) or die(mysql_error());
$row_city = mysql_fetch_assoc($city);
$totalRows_city = mysql_num_rows($city);

mysql_select_db($database_myconn, $myconn);
$query_state = "SELECT * FROM `state` ORDER BY title ASC";
$state = mysql_query($query_state, $myconn) or die(mysql_error());
$row_state = mysql_fetch_assoc($state);
$totalRows_state = mysql_num_rows($state);

//fetch all active credits from accounting table in database
$colname_a = $row_minfo['id']; // store member id
mysql_select_db($database_myconn, $myconn);
$query_a = sprintf("SELECT * FROM accounting WHERE mid = %s", $colname_a) . " and active = 'Yes'";
//echo $query_a;
$a = mysql_query($query_a, $myconn) or die(mysql_error());
$row_a = mysql_fetch_assoc($a);
$totalRows_a = mysql_num_rows($a);

//fetch item from database and place in assoc array
if (isset($_GET['id'])) {
  $colname_item = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_myconn, $myconn);
$query_item = sprintf("SELECT * FROM items WHERE id = %s", $colname_item) .  " and mid =" . $row_minfo['id'];

$item = mysql_query($query_item, $myconn) or die(mysql_error());
$row_item = mysql_fetch_assoc($item);
$totalRows_item = mysql_num_rows($item);
?>
<link href="mystyle.css" rel="stylesheet" type="text/css">


<form method="POST" action="<?php echo $editFormAction; ?>" name="form1">
<table width="500" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#FFFFFF">
  <caption align="left" class="pageTitle">
  <h1>Edit Property Listing</h1>
  </caption>
  <tr>
    <td class="colText">Category</td>
    <td bgcolor="#FFFFFF"><select name="cat" id="cat">
      <?php
do {
?>
      <option value="<?php echo $row_cat['id']?>"<?php if (!(strcmp($row_cat['id'], $row_item['cid']))) {echo "SELECTED";} ?>><?php echo $row_cat['title']?></option>
      <?php } while ($row_cat = mysql_fetch_assoc($cat));
  $rows = mysql_num_rows($cat);
  if($rows > 0) {
      mysql_data_seek($cat, 0);
	  $row_cat = mysql_fetch_assoc($cat);
  }
?>
    </select></td>
  </tr>
    <tr>
    <td class="colText">Price:</td>
    <td bgcolor="#FFFFFF"><input name="price" type="text" id="price" value="<?php echo $row_item['price']; ?>" size="25"></td>
  </tr>
  <tr>
    <td class="colText">Address</td>
    <td bgcolor="#FFFFFF"><input name="address" type="text" id="address" value="<?php echo $row_item['address']; ?>" size="40"></td>
  </tr>
  <tr>
    <td class="colText">City/Town</td>
    <td bgcolor="#FFFFFF">      <span class="rowText">
	<? if(mysql_num_rows($city) <=0){?>
	    <input name="city" type="text" id="city" value="<?php echo $row_item['city'] ?>">
	<? }else { ?>
	    <select name="city" id="city">
	      <?php
do {
?>
	      <option value="<?php echo $row_city['title']?>"<?php if ($row_city['title'] == $row_item['city']) {echo "SELECTED";} ?>><?php echo $row_city['title']?></option>
	      <?php
} while ($row_city = mysql_fetch_assoc($city));
  $rows = mysql_num_rows($city);
  if($rows > 0) {
      mysql_data_seek($city, 0);
	  $row_city = mysql_fetch_assoc($city);
  }
?>
	    </select>
	<? } ?>
    </span></td>
  </tr>
  <tr>
    <td class="colText">County</td>
    <td bgcolor="#FFFFFF"><span class="rowText">      <select name="state" id="state" style="width: 109px;">
      <?php
do {
?>
      <option value="<?php echo $row_state['stitle']?>"<?php if (!(strcmp($row_state['stitle'], $row_item['state']))) {echo "SELECTED";} ?>><?php echo $row_state['title']?></option>
          <?php
} while ($row_state = mysql_fetch_assoc($state));
  $rows = mysql_num_rows($state);
  if($rows > 0) {
      mysql_data_seek($state, 0);
	  $row_state = mysql_fetch_assoc($state);
  }
?>
      </select>
</span></td>
  </tr>
  <tr>
    <td class="colText">Postcode</td>
    <td bgcolor="#FFFFFF"><input name="zip" type="text" id="zip" value="<?php echo $row_item['zip']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Bed(s)</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="bed" type="text" id="bed" value="<?php echo $row_item['bed']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Bathroom(s)</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="bath" type="text" id="bath" value="<?php echo $row_item['bath']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Year</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="year" type="text" id="year" value="<?php echo $row_item['year']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Local Authority</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="subdiv" type="text" id="subdiv" value="<?php echo $row_item['subdiv']; ?>" size="40" maxlength="60"></td>
  </tr>
  <tr>
    <td class="colText">Local Hospital</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="schoold" type="text" id="schoold" value="<?php echo $row_item['schoold']; ?>" size="40" maxlength="60"></td>
  </tr>
  <tr>
    <td class="colText">Grounds (Acres)</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="lot_size" type="text" id="lot_size" value="<?php echo $row_item['lot_size']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Square Feet </td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="sqft" type="text" id="sqft" value="<?php echo $row_item['sqft']; ?>" size="15"></td>
  </tr>
  <tr>
    <td colspan="2" class="colText"><p>Description</p>    </td>
  </tr>
  <tr>
    <td colspan="2" class="colText"><div align="center">
      <textarea name="description" cols="70" rows="15" id="description"><?php echo $row_item['description']; ?></textarea>
    </div></td>
  </tr>
  <tr>
    <td colspan="2"><div align="right">
        <input name="id" type="hidden" id="id" value="<?php echo $row_item['id']; ?>">
        <input type="submit" name="Submit4" value="Submit">
        <input type="reset" name="Submit22" value="Reset">
        <input name="Submit32" type="button" onClick="window.location='a_index.php'" value="Cancel">
    </div></td>
  </tr>
</table>
<input type="hidden" name="MM_update" value="form1">
</form>
<? include('footer.php'); ?>
<?php
mysql_free_result($city);

mysql_free_result($state);

mysql_free_result($item);
?>

Please help this is driving me insane

#2 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 21 August 2006 - 11:17 PM

I aint reading all that code cos its too late for me

to make a drop down box from a database
<?php
echo "<select name='mydropdown'>\n"
."<option>Default Option</option>\n";
$query = "SELECT * FROM table WHERE whatever = 'something'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
echo "<option>".$row["filedname"]."</option>\n";
}
echo "</select>\n";
?>
I hope this helps in some way

//forgot to end the php code
Tell me the problem, I will try tell you the solution

#3 chimp

chimp
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 22 August 2006 - 10:14 AM

ok i stripped the code right down thought it would be easier if people could see the whole picture

I have spent absolutely weeks playing around with this but cant get it to work no matter what.

I am trying to change a couple of fields in my php page from a text box to a drop down box.

Fields i am trying to change on my form are:

Php form name ----- Mysql store name

Beds              -----  Bed
Bathroom        -----  Bath

I store the values of the drop down box in my mysql table

Table structure is as follows:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) default NULL,
  `description` text,
  `image` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (14, 'Yes', '-', '');
INSERT INTO `categories` VALUES (15, 'No', '-', '');

so in this case you can see I am just trying to use a yes or no value.

I want to store this in my table as a number

I already use the results from the drop down options in another field but cant seem to get this duplicated for my other fields.

my database store structure is as follows


  `bed` int(11) default NULL,
  `bath` int(11) default NULL,


I want my php code to be able to update the database using only yes or no values from my categories field…

  $updateSQL = sprintf("UPDATE items SET `long`=%s, `lat`=%s, cid=%s, title=%s, price=%s, special_price=%s, description=%s, address=%s, city=%s, `state`=%s, zip=%s, bed=".GetSQLValueString($_REQUEST['bed'],"text").", bath=".GetSQLValueString($_REQUEST['bath'],"text").", subdiv=".GetSQLValueString($_REQUEST['subdiv'],"text").", schoold = ".GetSQLValueString($_REQUEST['schoold'],"text").", year = ".GetSQLValueString($_REQUEST['year'],"int").", lot_size = ".GetSQLValueString($_REQUEST['lot_size'],"double").", sqft = ".GetSQLValueString($_REQUEST['sqft'],"double")." WHERE id=".GetSQLValueString($_REQUEST['id'],"int")."",
                       GetSQLValueString($address['long'], "text"),
                       GetSQLValueString($address['lat'], "text"),
                       GetSQLValueString($_POST['cat'], "int"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['price'], "double"),
                       GetSQLValueString($_POST['special_price'], "double"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['city'], "text"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['zip'], "text"),
                       GetSQLValueString($_POST['id'], "int")
					   );

and further down my code is this

  <tr>
    <td class="colText">Bed(s)</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="bed" type="text" id="bed" value="<?php echo $row_item['bed']; ?>" size="15"></td>
  </tr>
  <tr>
    <td class="colText">Bathroom(s)</td>
    <td bgcolor="#FFFFFF" class="rowText"><input name="bath" type="text" id="bath" value="<?php echo $row_item['bath']; ?>" size="15"></td>
  </tr>

i believe this bit is the code that i need to be using to use for the drop down box.

    <td class="colText">Category</td>
    <td bgcolor="#FFFFFF"><select name="cat" id="cat">
      <?php
do {
?>
      <option value="<?php echo $row_cat['id']?>"<?php if (!(strcmp($row_cat['id'], $row_item['cid']))) {echo "SELECTED";} ?>><?php echo $row_cat['title']?></option>
      <?php } while ($row_cat = mysql_fetch_assoc($cat));
  $rows = mysql_num_rows($cat);
  if($rows > 0) {
      mysql_data_seek($cat, 0);
	  $row_cat = mysql_fetch_assoc($cat);
  }
?>

Please help this is driving me insane, i have cut out all the rubbish from the code, and only left in the bits that refer to the section i am trying to change.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users