Solved

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

Posted on 2006-07-17
3
327 Views
Last Modified: 2012-05-05
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
Comment
Question by:dereksheahan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Expert Comment

by:AmigoJack
ID: 17122646
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
 

Author Comment

by:dereksheahan
ID: 17122718
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
 
LVL 12

Accepted Solution

by:
AmigoJack earned 500 total points
ID: 17128234
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question