Page 1 of 1

Zero entered into mysql table upon submit

Posted: Wed Aug 30, 2006 3:43 pm
by chimp
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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.

[syntax="sql"]

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[/syntax]

Code: Select all

$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

Code: Select all

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


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Aug 30, 2006 4:08 pm
by feyd
We're going to need to see the code you are using the generate this combo box in question as the problem likely lies there.

Posted: Wed Aug 30, 2006 4:14 pm
by chimp
is it ok to post my whole page code here then?

i know some sites dont like that sort of thing

Posted: Wed Aug 30, 2006 4:26 pm
by feyd
Unless it's insanely long the server will handle it just fine. Maximum post size, after the first pass of processing is 64K. How much actual text that is depends on how many tags you use that will be nudged by the first pass.

Posted: Wed Aug 30, 2006 4:34 pm
by chimp
ok here goes, i appear to have found the problem i had 2 fields named the same in my html code, however i now have another problem,

I have 2 drop downs

1 called "categories" and 1 called "bed"

when i change "categories" it also changes beds result on update, but if i change bed it always remains as the same as the "categories" result.

Code: Select all

<?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 class="colText">Bathroom</td>
    <td bgcolor="#FFFFFF"><select name="bath" id="bath">
      <?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">Year</td>
    <td bgcolor="#FFFFFF"><select name="year" id="year">
      <?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">Local Authority</td>
    <td bgcolor="#FFFFFF"><select name="subdiv" id="subdiv">
      <?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">Local Hospital</td>
    <td bgcolor="#FFFFFF"><select name="schoold" id="schoold">
      <?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">Grounds (Acres)</td>
    <td bgcolor="#FFFFFF"><select name="lot_size" id="lot_size">
      <?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">Square Feet</td>
    <td bgcolor="#FFFFFF"><select name="Square Feet" id="Square Feet">
      <?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);
?>