Zero entered into mysql table upon submit

[chimp8471]

i am trying to update my mysql table with a drop down box (changed from a text box) but when i click submit i get a zero entered instead of the id number of the option:

I am trying to update the field called "bed"

i have a table that stores a yes no value in it (will be others just trying this for testing purposes.

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)
) TYPE=MyISAM AUTO_INCREMENT=23 ;

--

-- Dumping data for table categories

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

my update sql is

$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'],"int").", 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")
					   );

when i echo my sql i get the following : highlighted the problem area in bold

UPDATE items SET `long`=NULL, `lat`=NULL, cid=15, title=NULL, price='25', special_price=NULL, description='Simply immaculate detached Bungalow enjoying a highly desirable semi rural location with beautiful countryside views. Situated only a few minutes drive from Taunton, the County town of Somerset, Lithe Lochan is an exclusive, private residential estate with an amenity area. With full gas fired central heating and sealed unit double glazing, the accommodation is comprehensive, all on one floor and comprises: Entrance Vestibule, Hallway, Lounge, separate Dining Room, Study, fitted dining Riverside Kitchen with Family area off, Utility Room, WC,
BEDROOMS
Master Bedroom with En-Suite Shower Room and Dressing Room, 4 further Bedrooms and family Bathroom.
B&B Price per person: £25.00 - £35.00
Rooms: 4, consisting of 3 double en-suite, 1 twin private bathroom
Dogs: no dogs
When Open/Closed: Closed Xmas
Nearest Road/s: A38, A379 Local directions: ', address='home', city='Abergele', `state`='Som', zip='T12344', [B]bed=0[/B], bath='0', subdiv='0', schoold = '0', year = 0, lot_size = '0', sqft = NULL WHERE id=12

can you please help i am really lost with this

cheers in advance

Andy

[thorpe]

Your showing us the scheme for a table called 'categories', and a query for a table called 'itmes'. Which is it? And what exactly is the problem?

[chimp8471]

ok it appears i have found the problem, i had 2 fields named the same in my html form, i am using the categories table id to store in the items table for bed and categories.

I now have 2 drop down boxes in my form, one called "categories" and one called "bed"

when i change the categories drop down it also changes the bed result...

any ideas? would it help to post my page code?

example of what happens:

ok here is a pic of my form, the first shows it before i change just the category field,

the second shows what happens when i change the option in the drop down box for just the category.

You can see by me just changing the top one all off the others change too, but if i change the other ones they dont change on my form they remain the same as the category selection.

First

Second

[ClarkF1]

Posting the page code will help

[chimp8471]

cheers

<?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'],"int").", bath=".GetSQLValueString($_REQUEST['bath'],"int").", subdiv=".GetSQLValueString($_REQUEST['subdiv'],"int").", schoold = ".GetSQLValueString($_REQUEST['schoold'],"int").", year = ".GetSQLValueString($_REQUEST['year'],"int").", lot_size = ".GetSQLValueString($_REQUEST['lot_size'],"int").", sqft = ".GetSQLValueString($_REQUEST['sqft'],"int")." 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</td> 
    <td bgcolor="#FFFFFF"><select name="bed" id="bed"> 
      <?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 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); 
?>