...

View Full Version : Zero entered into mysql table upon submit



chimp
08-30-2006, 10:51 PM
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', bed=0, 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

Fumigator
08-30-2006, 11:00 PM
Can you post the html for the "bed" form element?

mic2100
08-30-2006, 11:02 PM
what does the dropdown box code look like???

chimp
08-30-2006, 11:48 PM
right i appear to have sorted the problem, i had two fields named the same, DOH!!!!


but have now run into another

I hope i am ok to post this amount of code, sorry in advance if i am not meant to:

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.



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum