• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Why is my variable not coming back with the correct result?

Hi all,
I using the function mysql_insert_id(); to take the last created auto-numeber and poplulate another table with the same result. This works fine:
$member_main_last_id = mysql_insert_id();

//the next table
GetSQLValueString($member_main_last_id, "int"),

Now when I try to use this variable $member_main_last_id to post it to the next page it gives me a strange result. When I echo it to the page the correct figure might be say "55" but it prints "35", always 20 less than the actual figure! I can't understand this.

Here is the source of the page if someone could have a look.
Cheers,
D

<?php require_once('../Connections/reacha_new.php'); ?>
<?php
session_start();

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']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "personal_reg")) {
  $insertSQL = sprintf("INSERT INTO member_main (mm_username, mm_full_address, mm_travel, mm_mem_type_id, mm_password, mm_native_town_id, mm_contact_nr, mm_contact_nr_alt, mm_email_add, mm_website_add, mm_lat, mm_long, mm_start_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, now())",
                       GetSQLValueString($_POST['txt_username'], "text"),
                                 GetSQLValueString($_POST['txt_full_add'], "text"),
                                 GetSQLValueString($_POST['drop_travel'], "text"),
                                 GetSQLValueString($_POST['teacher_type'], "int"),
                       GetSQLValueString($_POST['txt_password'], "text"),
                       GetSQLValueString($_POST['Town'], "int"),
                       GetSQLValueString($_POST['txt_num1'], "int"),
                       GetSQLValueString($_POST['txt_num2'], "int"),
                       GetSQLValueString($_POST['txt_email_add'], "text"),
                       GetSQLValueString($_POST['txt_web'], "text"),
                       GetSQLValueString($_POST['latitude'], "double"),
                       GetSQLValueString($_POST['longitude'], "double"));

  mysql_select_db($database_reacha_new, $reacha_new);
  $Result1 = mysql_query($insertSQL, $reacha_new) or die(mysql_error());
}

$member_main_last_id = mysql_insert_id();



if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "personal_reg")) {
  $insertSQL = sprintf("INSERT INTO member_per (mp_mem_id, mp_gender, mp_availability) VALUES (%s, %s, %s)",
                       GetSQLValueString($member_main_last_id, "int"),
                                 GetSQLValueString($_POST['select_gender'], "text"),
                       GetSQLValueString($_POST['txt_availabilty'], "text"));

  mysql_select_db($database_reacha_new, $reacha_new);
  $Result1 = mysql_query($insertSQL, $reacha_new) or die(mysql_error());
 
  $insertGoTo = "../Registration/reg6.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  $_SESSION['member_id'] = $_POST['member_id'];
  $_SESSION['distance'] = $_POST['distance'];
  $_SESSION['latitude'] = $_POST['latitude'];
  $_SESSION['longitude'] = $_POST['longitude'];
  $_SESSION['Town'] = $_POST['Town'];
  header(sprintf("Location: %s", $insertGoTo));
}


 //require_once('../Connections/reacha_new.php'); ?>
<?php
mysql_select_db($database_reacha_new, $reacha_new);
$query_rscounty_town = "SELECT county.c_county_id, county.c_county_name, town.t_town_id, town.t_town_name, town.t_lat, town.t_lon, town.t_county_id FROM county, town WHERE town.t_county_id = county.c_county_id";
$rscounty_town = mysql_query($query_rscounty_town, $reacha_new) or die(mysql_error());
$row_rscounty_town = mysql_fetch_assoc($rscounty_town);
$totalRows_rscounty_town = mysql_num_rows($rscounty_town);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Personal Registration Page</title>
<style type="text/css">
<!--
.style1 {font-size: 10px}
-->
</style>
<script type="text/javascript">
function showHideRow(selectboxValue, rowID) {
  if(selectboxValue == "yes") {
    document.getElementById(rowID).style.display = '';
  } else {
    document.getElementById(rowID).style.display = 'none';
  }
}
</script>
</head>

<body>
<form id="personal_reg" name="personal_reg" method="POST" action="<?php echo $editFormAction; ?>">
<?php echo $_POST['drop_travel'] ?>
  <label for="textfield">Personal Details <br />
  <br />
  </label>
  <table width="428" border="1">
    <tr>
      <td width="191"><label for="lbl_username">Username<br />
      <span class="style1">(this is what will be displayed to the users) </span></label></td>
      <td width="221"><input type="text" name="txt_username" id="txt_username" /></td>
    </tr>
    <tr>
      <td><label for="lbl_gender">Gender</label></td>
      <td><select name="select_gender" id="select_gender">
        <option value="male">Male</option>
        <option value="female">Female</option>
      </select></td>
    </tr>
    <tr>
      <td><label for="lbl_password">Password</label></td>
      <td><input type="password" name="txt_password" id="lbl_password"  /></td>
    </tr>
    <tr>
      <td><label for="lbl_re_password">Re-enter Password</label></td>
      <td><input type="password" name="txt_re_password" id="lbl_re_password" /></td>
    </tr>
    <tr>
      <td>Are you willing to travel to students? </td>
      <td><label for="lbl_travel"></label>
        <select name="drop_travel" id="lbl_travel" onchange="showHideRow(this.options[this.selectedIndex].value, 'travel2');">
          <option value="no">No</option>
          <option value="yes">Yes</option>
        </select>      </td>
    </tr>
    <tr id="travel2" style="display:none;">
      <td>Approx how many km are you willing to travel? </td>
      <td><label for="lbl_distance"></label>
        <select name="distance" id="distance" >
          <option value="5">5</option>
          <option value="10">10</option>
          <option value="20">20</option>
          <option value="50">50</option>
          <option value="75">75</option>
        </select>
      </td>
    </tr>
    <tr>
      <td><label for="lbl_avail">Availability</label></td>
      <td><textarea name="txt_availabilty" cols="34" rows="3" id="lbl_avail"></textarea></td>
    </tr>
  </table>
 

    <p>&nbsp;</p>
    <p>Location Details: </p>
    <table width="400" border="1">
      <tr>
        <td>Select your County </td>
        <td><select name="County" id="County" onchange="TCN_reload(this)">
          <option selected="selected">County</option>
        </select></td>
      </tr>
      <tr>
        <td>Select your Town</td>
        <td><select name="Town" id="Town" onchange="TCN_reload(this)">
          <option selected="selected">Town</option>
        </select></td>
      </tr>
      <tr>
        <td><label for="lbl_full_add">Full Address (*)</label></td>
        <td><textarea name="txt_full_add" cols="30" rows="3" id="label9"></textarea></td>
      </tr>
      <tr>
        <td colspan="2"><div align="center">Click <a href="../map/sfliveaddpt.php" target="myChildWindow">here</a> to add your exact address on a map </div></td>
      </tr>
    </table>
   
    <p>
      <script language="JavaScript" type="text/javascript">
TCN_contents=new Array();
TCN_tempArray=new Array();
TCN_counter=0;
function TCN_addContent(str){
      TCN_contents[TCN_counter]=str;
      TCN_counter++;
}
function TCN_split(){
      TCN_arrayValues = new Array();
      for(i=0;i<TCN_contents.length;i++){
            TCN_arrayValues[i]=TCN_contents[i].split(separator);
            TCN_tempArray[0]=TCN_arrayValues;
      }
}
function TCN_makeSelValueGroup(){
      TCN_selValueGroup=new Array();
      var args=TCN_makeSelValueGroup.arguments;
      for(i=0;i<args.length;i++){
            TCN_selValueGroup[i]=args[i];
            TCN_tempArray[i]=new Array();
      }
}
function TCN_makeComboGroup(){
      TCN_comboGroup=new Array();
      var args=TCN_makeComboGroup.arguments;
      for(i=0;i<args.length;i++) TCN_comboGroup[i]=findObj(args[i]);
}
function TCN_setDefault(){
      for (i=TCN_selValueGroup.length-1;i>=0;i--){
            if(TCN_selValueGroup[i]!=""){
                  for(j=0;j<TCN_contents.length;j++){
                        if(TCN_arrayValues[j][(i*2)+1]==TCN_selValueGroup[i]){
                              for(k=i;k>=0;k--){
                                    if(TCN_selValueGroup[k]=="") TCN_selValueGroup[k]=TCN_arrayValues[j][(k*2)+1];
                              }
                        }
                  }
            }
      }
}
function TCN_loadMenu(daIndex){
      var selectionMade=false;
      daArray=TCN_tempArray[daIndex];
      TCN_comboGroup[daIndex].options.length=0;
      for(i=0;i<daArray.length;i++){
            existe=false;
            for(j=0;j<TCN_comboGroup[daIndex].options.length;j++){
                  if(daArray[i][(daIndex*2)+1]==TCN_comboGroup[daIndex].options[j].value) existe=true;
            }
            if(existe==false){
                  lastValue=TCN_comboGroup[daIndex].options.length;
                  TCN_comboGroup[daIndex].options[TCN_comboGroup[daIndex].options.length]=new Option(daArray[i][daIndex*2],daArray[i][(daIndex*2)+1]);
                  if(TCN_selValueGroup[daIndex]==TCN_comboGroup[daIndex].options[lastValue].value){
                        TCN_comboGroup[daIndex].options[lastValue].selected=true;
                        selectionMade=true;
                  }
            }
      }
      if(selectionMade==false) TCN_comboGroup[daIndex].options[0].selected=true;
}      
function TCN_reload(from){
      if(!from){
            TCN_split();
            TCN_setDefault();
            TCN_loadMenu(0);
            TCN_reload(TCN_comboGroup[0]);
      }else{
            for(j=0; j<TCN_comboGroup.length; j++){
                  if(TCN_comboGroup[j]==from) index=j+1;
            }
            if(index<TCN_comboGroup.length){
                  TCN_tempArray[index].length=0;
                  for(i=0;i<TCN_comboGroup[index-1].options.length;i++){
                        if(TCN_comboGroup[index-1].options[i].selected==true){
                              for(j=0;j<TCN_tempArray[index-1].length;j++){
                                    if(TCN_comboGroup[index-1].options[i].value==TCN_tempArray[index-1][j][(index*2)-1]) TCN_tempArray[index][TCN_tempArray[index].length]=TCN_tempArray[index-1][j];
                              }
                        }
                  }
            TCN_loadMenu(index);
            TCN_reload(TCN_comboGroup[index]);
            }
      }
}
function findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}
TCN_makeSelValueGroup("","");
TCN_makeComboGroup("County","Town");
 var separator="+#+";
<?php do{?>
TCN_addContent("<?php echo $row_rscounty_town['c_county_name']; ?>+#+<?php echo $row_rscounty_town['c_county_id']; ?>+#+<?php echo $row_rscounty_town['t_town_name']; ?>+#+<?php echo $row_rscounty_town['t_town_id']; ?>");
<?php } while ($row_rscounty_town = mysql_fetch_assoc($rscounty_town)); ?>
TCN_reload();

      </script>
    </p>
   
    <p>
      <label for="lbl_lat">Latitude</label>
      <input type="text" name="latitude" id="latitude" value="<?php echo htmlspecialchars($_POST['new_lat']);?>" />
    </p>
  <p>
      <label for="lbl_long">Longitude</label>
      <input type="text" name="longitude" id="longitude" value="<?php echo htmlspecialchars($_POST['new_lon']);?>" />
  </p>
  <p>Contact Details: </p>
  <table width="400" border="1">
    <tr>
      <td><label for="lbl_num1">Contact Number</label></td>
      <td><input type="text" name="txt_num1" id="txt_num1" /></td>
    </tr>
    <tr>
      <td><label for="lbl_num2">Contact Number 2 * </label></td>
      <td><input type="text" name="txt_num2" id="txt_num2" /></td>
    </tr>
    <tr>
      <td><label for="lbl_email_add">Email Address</label></td>
      <td><input type="text" name="txt_email_add" id="txt_email_add" /></td>
    </tr>
    <tr>
      <td><label for="lbl_web_add">Website Address * </label></td>
      <td><input type="text" name="txt_web" id="txt_web" /></td>
    </tr>
  </table>
  <p>&nbsp;</p>
  <p>
    <label for="Submit">Proceed to Subject Information</label>
    <input type="submit" name="Submit" value="Proceed" id="Submit" />
    <label for="label10"></label>
</p>

<input type="hidden" name="MM_insert" value="personal_reg"/>
<input type="hidden" name="teacher_type" value="<?php echo $_POST['cat_select'] ?>"/>
<input type="hidden" name="member_id" value="<?php echo $member_main_last_id ?>"/>
<?php echo $member_main_last_id ?>
</form>
</body>
</html>
<?php
mysql_free_result($rscounty_town);
?>
0
dereksheahan
Asked:
dereksheahan
  • 2
1 Solution
 
AmigoJackCommented:
you expect us to understand that wad of code? make a test list: see what are the outputs for each number from 1 to 55. mostly this hereins from intval() numbers with leading zeroes (which are understood by php as octals rather than decimals), but im not seeing that case right now.

also make sure your javascript part isnt the origin of the value change
0
 
dereksheahanAuthor Commented:
hi AmigoJack,
sorry yeah it is a bit of a mess alright :)
I'm not in control of the number generated as its an SQL function that takes the result of the last auto-number. As I said, it does update the database correctly but even when I try to echo the variable between the two record updates it still gives me a strange result:
i.e.
f ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "personal_reg")) {
  $insertSQL = sprintf("INSERT INTO member_main (mm_username, mm_full_address, mm_travel, mm_mem_type_id, mm_password, mm_native_town_id, mm_contact_nr, mm_contact_nr_alt, mm_email_add, mm_website_add, mm_lat, mm_long, mm_start_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, now())",
                       GetSQLValueString($_POST['txt_username'], "text"),
                            GetSQLValueString($_POST['txt_full_add'], "text"),
                            GetSQLValueString($_POST['drop_travel'], "text"),
                            ...

  mysql_select_db($database_reacha_new, $reacha_new);
  $Result1 = mysql_query($insertSQL, $reacha_new) or die(mysql_error());
}

$member_main_last_id = mysql_insert_id();
//echoing it in here gives a different result than what is updated in the next table????


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "personal_reg")) {
  $insertSQL = sprintf("INSERT INTO member_per (mp_mem_id, mp_gender, mp_availability) VALUES (%s, %s, %s)",
                       GetSQLValueString($member_main_last_id, "int"),
                            GetSQLValueString($_POST['select_gender'], "text"),
                       GetSQLValueString($_POST['txt_availabilty'], "text"));


Any other ideas on how to test it?
How could I test to see if the JS is the problem? (I don't think it is though as I use this script on many other pages and haven't experienced this problem)
Thanks,
D
0
 
AmigoJackCommented:
comments at http://www.php.net/manual/en/function.mysql-insert-id.php also say there might be problems using mysql_insert_id(). so do the following for testing purposes:

mysql_select_db($database_reacha_new, $reacha_new);
  $Result1 = mysql_query('select LAST_INSERT_ID() as last_f, max(m.id) as last_m from member_main m', $reacha_new) or die(mysql_error());
  $row= mysql_fetch_row($Result1);

  echo 'the mysql-function LAST_INSERT_ID() returned '. $row['last_f']. ' --- the highest id in the table is '. $row['last_m'];

now you have this and see what it returned, you might want to use one of the two other ways to get your latest ID (i suspected your table "member_main" has the column named "id")
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now