Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero entered into mysql table upon submit

    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
    PHP Code:
    $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:
    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Can you post the html for the "bed" form element?

  • #3
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    what does the dropdown box code look like???

  • #4
    New to the CF scene
    Join Date
    May 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 Code:
    <?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($cat0);
          
    $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($city0);
          
    $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($state0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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($cat0);
          
    $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);
    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •