Solved

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

Posted on 2006-07-17
3
313 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Is there a limit to how many inner joins you should use? 4 33
Wordpress Taxonomy 2 30
mysql update statement 3 23
PHP Parse error: syntax error, unexpected '[' 1 25
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

832 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