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

Using Record Insertion to 2 Tables

Hi All,
I have a form that the users fills in and then I want the values to go to two different tables in the database (MYSQL, PHP 5). I have set up all the fields manually and then used the "record insertion" in DW 8 to bind each of the fields to a database field. This works fine and enters the details correctly in the database. However, I have the database broken into a few different tables where I have a main users table (with the primary key det_user_id) which holds all of the general information for the user. Then I have another table (remote_details_table) that holds all of the info pertaining to users with remote phones. In the remote_details_table I have the foreign key user_id (callled rem_user_id) that links each user to the relevant record. However for the main details table the user_id is an auto number. How do I make DW read the auto-number from the details table and automatically generate the same integer for the remote_details_table? I've tryed all types of solutions with hidden fields but can seem to get it to function. At the moment I don't have foreign keys defined in the database. Is it possible to link the user_id's from the database instead or is that the best solution to the problem? Any help would be greatly appreciated.
Here's the code for the page if it helps :)
<?php require_once('../Connections/GSM_mobile.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']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['user_id_fk'], "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));
                              

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsNetworks = "SELECT * FROM network_table";
$rsNetworks = mysql_query($query_rsNetworks, $GSM_mobile) or die(mysql_error());
$row_rsNetworks = mysql_fetch_assoc($rsNetworks);
$totalRows_rsNetworks = mysql_num_rows($rsNetworks);

$colname_rsModels = "-1";
if (isset($_GET['Type_variable'])) {
  $colname_rsModels = (get_magic_quotes_gpc()) ? $_GET['Type_variable'] : addslashes($_GET['Type_variable']);
}
mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsModels = sprintf("SELECT mod_model_id, mod_model_name, mod_type_id FROM model_table WHERE mod_type_id = %s", $colname_rsModels);
$rsModels = mysql_query($query_rsModels, $GSM_mobile) or die(mysql_error());
$row_rsModels = mysql_fetch_assoc($rsModels);
$totalRows_rsModels = mysql_num_rows($rsModels);

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsId = "SELECT det_user_id FROM details_table";
$rsId = mysql_query($query_rsId, $GSM_mobile) or die(mysql_error());
$row_rsId = mysql_fetch_assoc($rsId);
$totalRows_rsId = mysql_num_rows($rsId);
?><form action="<?php echo $editFormAction; ?>" method="POST" name="remote_user" id="remote_user">
  <p>
    <label>User Name
    <input name="text_username" type="text" id="text_username">
    </label>
  </p>
  <p>
    <label>Email Address
    <input name="text_email_add" type="text" id="text_email_add">
    </label>
  </p>
  <p>
    <label>Model:
    <select name="modeldrop" id="modeldrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsModels['mod_model_id']?>"><?php echo $row_rsModels['mod_model_name']?></option>
      <?php
} while ($row_rsModels = mysql_fetch_assoc($rsModels));
  $rows = mysql_num_rows($rsModels);
  if($rows > 0) {
      mysql_data_seek($rsModels, 0);
        $row_rsModels = mysql_fetch_assoc($rsModels);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>If Other Phone Enter Here
    <input name="text_if_other" type="text" id="text_if_other">
    </label>
</p>
  <p>
    <label>Network
    <select name="networkdrop" id="networkdrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsNetworks['net_network_id']?>"><?php echo $row_rsNetworks['net_network_name']?></option>
      <?php
} while ($row_rsNetworks = mysql_fetch_assoc($rsNetworks));
  $rows = mysql_num_rows($rsNetworks);
  if($rows > 0) {
      mysql_data_seek($rsNetworks, 0);
        $row_rsNetworks = mysql_fetch_assoc($rsNetworks);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>IMEI Number
    <input name="text_IMEI" type="text" id="text_IMEI">
    </label>
  </p>
  <p>
    <label>Submit
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="remote_user">
<input type="hidden" name="user_id_fk" value=""></form>
<?php
mysql_free_result($rsNetworks);

mysql_free_result($rsModels);

mysql_free_result($rsId);
?>
0
dereksheahan
Asked:
dereksheahan
  • 13
  • 11
1 Solution
 
Jason C. LevineNo oneCommented:
Hi Derek,

I'll confess that I am having a little trouble following the table flow from the above.  When you insert the form above, which table receives the data first that generates the ID you will be using?

There is a PHP command that gets the last inserted primary key from the preceeding query: mysql_insert_id().  You use it after an INSERT to pull the key value into a new variable and then use it with the next INSERT and so on...

So.  Look for my comments below to see what I'm doing.

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

// GET THE LAST INSERTED ID HERE

$details_table_last_id = mysql_insert_id();

// Now, use it below if needed to create the remote_details_table ID

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"), // here is the alteration
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

// If you need to get a new primary key value, just run mysql_insert_id() again here and assign it.

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));
                         

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

Link to mysql_insert_id documentation:  http://us2.php.net/mysql_insert_id
0
 
dereksheahanAuthor Commented:
Hi Jason,
The details_table contains the primary key auto-numbered user_id so that table generates the integer. All of the above code you see I was just playing around with this afternoon using the record insert in DW.
0
 
dereksheahanAuthor Commented:
Just on a side-note: is it necessary to link the database tables using a foreign key? Would it help in any way?
0
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.

 
Jason C. LevineNo oneCommented:
>> The details_table contains the primary key auto-numbered user_id so that table generates the integer. All of the above code you see I was just playing around with this afternoon using the record insert in DW.

Okay, well the advice stands then.  You use mysql_insert_id to get the recently inserted ID and assign it to whatever other tables you want.  Your structure above is generally correct except for that one detail.

>> Just on a side-note: is it necessary to link the database tables using a foreign key? Would it help in any way?

Never hurts to be able to pull up related records between two tables.  You may not use it now, but in the future you might be thanking yourself for adding this.  Usually you need a foreign key if there is no other way to relate the data.  Not sure if that is necessary here if the user's model info is present in both tables.
0
 
dereksheahanAuthor Commented:
The user model info won't be present in both tables though. Only in the remote_details table. The link will be to the details_table through the user_id.
0
 
dereksheahanAuthor Commented:
Hi Jason,
Tried your above suggestion but the remote_details_table.rem_user_id field still returns empty. Here is the revised code for the page on your suggestions. Think I made the correct alternations? When I hit the submit button on the page a screen comes up "Query is empty". I don't know if that has anything to do with the problem??
Thanks,
D

<?php require_once('../Connections/GSM_mobile.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']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['$details_table_last_id'], "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));
                              

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());
}

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsNetworks = "SELECT * FROM network_table";
$rsNetworks = mysql_query($query_rsNetworks, $GSM_mobile) or die(mysql_error());
$row_rsNetworks = mysql_fetch_assoc($rsNetworks);
$totalRows_rsNetworks = mysql_num_rows($rsNetworks);

$colname_rsModels = "-1";
if (isset($_GET['Type_variable'])) {
  $colname_rsModels = (get_magic_quotes_gpc()) ? $_GET['Type_variable'] : addslashes($_GET['Type_variable']);
}
mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsModels = sprintf("SELECT mod_model_id, mod_model_name, mod_type_id FROM model_table WHERE mod_type_id = %s", $colname_rsModels);
$rsModels = mysql_query($query_rsModels, $GSM_mobile) or die(mysql_error());
$row_rsModels = mysql_fetch_assoc($rsModels);
$totalRows_rsModels = mysql_num_rows($rsModels);

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsId = "SELECT det_user_id FROM details_table";
$rsId = mysql_query($query_rsId, $GSM_mobile) or die(mysql_error());
$row_rsId = mysql_fetch_assoc($rsId);
$totalRows_rsId = mysql_num_rows($rsId);
?><form action="<?php echo $editFormAction; ?>" method="POST" name="remote_user" id="remote_user">
  <p>
    <label>User Name
    <input name="text_username" type="text" id="text_username">
    </label>
  </p>
  <p>
    <label>Email Address
    <input name="text_email_add" type="text" id="text_email_add">
    </label>
  </p>
  <p>
    <label>Model:
    <select name="modeldrop" id="modeldrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsModels['mod_model_id']?>"><?php echo $row_rsModels['mod_model_name']?></option>
      <?php
} while ($row_rsModels = mysql_fetch_assoc($rsModels));
  $rows = mysql_num_rows($rsModels);
  if($rows > 0) {
      mysql_data_seek($rsModels, 0);
        $row_rsModels = mysql_fetch_assoc($rsModels);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>If Other Phone Enter Here
    <input name="text_if_other" type="text" id="text_if_other">
    </label>
</p>
  <p>
    <label>Network
    <select name="networkdrop" id="networkdrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsNetworks['net_network_id']?>"><?php echo $row_rsNetworks['net_network_name']?></option>
      <?php
} while ($row_rsNetworks = mysql_fetch_assoc($rsNetworks));
  $rows = mysql_num_rows($rsNetworks);
  if($rows > 0) {
      mysql_data_seek($rsNetworks, 0);
        $row_rsNetworks = mysql_fetch_assoc($rsNetworks);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>IMEI Number
    <input name="text_IMEI" type="text" id="text_IMEI">
    </label>
  </p>
  <p>
    <label>Submit
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="remote_user">
<input type="hidden" name="user_id_fk" value=""></form>
<?php
mysql_free_result($rsNetworks);

mysql_free_result($rsModels);

mysql_free_result($rsId);
?>
0
 
Jason C. LevineNo oneCommented:
The problem is that we don't know WHICH query is empty as there are three.  You need to modify the die() statements so we have more information to go on.  Look for these lines:

$Result1 = mysql_query($insertSQL, $GSM_mobile) or die(mysql_error());

Modify them to:

$Result[x] = mysql_query($insertSQL, $GSM_mobile) or die("Result[x] ".mysql_error());

where [x] is 1, 2, and 3 respectively.  That should produce an error message like:

"Result1 The Query is empty"

and we can test further.

0
 
dereksheahanAuthor Commented:
Hi Jason,
So.. made the alterations you suggested and it returned that "Result[3]Query was empty". To be honest I've been relying on DW so far to make update forms so I'm not very familar with the code. I'll try to learn a bit about it this afternoon so I know what a little about what I'm talking about :) Here's the new page with your inclusion if it helps you.
Thanks,
D

<?php require_once('../Connections/GSM_mobile.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']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['$details_table_last_id'], "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));
                              

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result3 = mysql_query($insertSQL, $GSM_mobile) or die("Result[3]".mysql_error());
}

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsNetworks = "SELECT * FROM network_table";
$rsNetworks = mysql_query($query_rsNetworks, $GSM_mobile) or die(mysql_error());
$row_rsNetworks = mysql_fetch_assoc($rsNetworks);
$totalRows_rsNetworks = mysql_num_rows($rsNetworks);

$colname_rsModels = "-1";
if (isset($_GET['Type_variable'])) {
  $colname_rsModels = (get_magic_quotes_gpc()) ? $_GET['Type_variable'] : addslashes($_GET['Type_variable']);
}
mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsModels = sprintf("SELECT mod_model_id, mod_model_name, mod_type_id FROM model_table WHERE mod_type_id = %s", $colname_rsModels);
$rsModels = mysql_query($query_rsModels, $GSM_mobile) or die(mysql_error());
$row_rsModels = mysql_fetch_assoc($rsModels);
$totalRows_rsModels = mysql_num_rows($rsModels);

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsId = "SELECT det_user_id FROM details_table";
$rsId = mysql_query($query_rsId, $GSM_mobile) or die(mysql_error());
$row_rsId = mysql_fetch_assoc($rsId);
$totalRows_rsId = mysql_num_rows($rsId);
?><form action="<?php echo $editFormAction; ?>" method="POST" name="remote_user" id="remote_user">
  <p>
    <label>User Name
    <input name="text_username" type="text" id="text_username">
    </label>
  </p>
  <p>
    <label>Email Address
    <input name="text_email_add" type="text" id="text_email_add">
    </label>
  </p>
  <p>
    <label>Model:
    <select name="modeldrop" id="modeldrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsModels['mod_model_id']?>"><?php echo $row_rsModels['mod_model_name']?></option>
      <?php
} while ($row_rsModels = mysql_fetch_assoc($rsModels));
  $rows = mysql_num_rows($rsModels);
  if($rows > 0) {
      mysql_data_seek($rsModels, 0);
        $row_rsModels = mysql_fetch_assoc($rsModels);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>If Other Phone Enter Here
    <input name="text_if_other" type="text" id="text_if_other">
    </label>
</p>
  <p>
    <label>Network
    <select name="networkdrop" id="networkdrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsNetworks['net_network_id']?>"><?php echo $row_rsNetworks['net_network_name']?></option>
      <?php
} while ($row_rsNetworks = mysql_fetch_assoc($rsNetworks));
  $rows = mysql_num_rows($rsNetworks);
  if($rows > 0) {
      mysql_data_seek($rsNetworks, 0);
        $row_rsNetworks = mysql_fetch_assoc($rsNetworks);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>IMEI Number
    <input name="text_IMEI" type="text" id="text_IMEI">
    </label>
  </p>
  <p>
    <label>Submit
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="remote_user">
<input type="hidden" name="user_id_fk" value=""></form>
<?php
mysql_free_result($rsNetworks);

mysql_free_result($rsModels);

mysql_free_result($rsId);
?>

0
 
Jason C. LevineNo oneCommented:
Well, that means there is a problem getting the form values into this query:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));
                         

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result3 = mysql_query($insertSQL, $GSM_mobile) or die("Result[3]".mysql_error());
}

Check the form for values in fields:  text_if_other, networkdrop, text_IMEI.  If none of those are set in the form, the query will fail.  You will need to write additional conditions into that opening IF statement so that it checks for at least one value there.
0
 
dereksheahanAuthor Commented:
Hi Jason,
I'm not sure I understand what you mean by "Check the form for values in fields". Do you mean the names of the text fields? If so they are all correct. If you mean the inital values then networkdrop is the only one that gets its values from a recordset. I think I'll try and start the page from scratch again, that way maybe I'll be able to figure out whats going on a little better as I build it up. Unless you have any other ideas? :)
0
 
Jason C. LevineNo oneCommented:
If the query is empty, only one of two things are possible:

One, the assignment of the form values to the SQL fields in the PHP is wrong.

Two, the form values themselves are all blank.

The former can be caused by mismatching field types (text to int, for example).  The latter is caused by not filling anything in the fields.

0
 
dereksheahanAuthor Commented:
Yeah, think I'll give the page a go from scratch for a while. Maybe then I'll have a better understanding of the exact problem
0
 
Jason C. LevineNo oneCommented:
Actually, I think I see the problem:

$insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

There are four %s values but only three assignments from the form.  Change it to:

$insertSQL = sprintf("INSERT INTO remote_details_table (rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

and try it.
0
 
dereksheahanAuthor Commented:
Good man, that sorts out the empty query problem! The rem_user_id is still not getting the value from the $details_table_last_id = mysql_insert_id(); statement. Or maybe it is but its not entering it into the database? I'll try what you suggested yesterday (I think) and do an Echo statement to see if it is actually getting the user_id auto-number from the details_table
0
 
Jason C. LevineNo oneCommented:
>> The rem_user_id is still not getting the value from the $details_table_last_id = mysql_insert_id(); statement.

Erm.  Is that between query1 and query2?  If so, my bad on the code I gave you (this is why I prefer simple problems)

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['$details_table_last_id'], "int"), // this is wrong
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

The above is f--ked up.  Use the below:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id', "int"),  // we assigned this ourselves, not from the $_POST array.
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

0
 
Jason C. LevineNo oneCommented:
Crap:

 GetSQLValueString($details_table_last_id, "int"),

is the correct line.  Delete the trailing single quote.
0
 
dereksheahanAuthor Commented:
Brillant it works! Only one problem: Two records are created in the remote_details_table. The first has the correct auto-number from the table and the second is blank?? All of the other fields are filled in on both records created. God, I'm learning so much
0
 
Jason C. LevineNo oneCommented:
Look at your code again for queries two and three.  Both are inserting to remote_details_table so you will get two different rows.
0
 
dereksheahanAuthor Commented:
Got it! Yeah didn't really know what I was doing (as you prob guessed!) so created two similar insertsql statements with the wizard. Deleted the third and it works perfect now. Thanks so much
0
 
dereksheahanAuthor Commented:
Hi Jason,
Not sure if you'll look at this now that the question is answered but I'll ask it here first anyways. One other thing I need to happen when the form is submitted. If the "text_if_other" field is entered then I don't want the modeldrop value to enter into the database. I've tried an If statement in the  GetSQLValueString($_POST['networkdrop'], "int"), section but can't seem to get it to work?
Thanks,
D
0
 
Jason C. LevineNo oneCommented:
repost the current PHP code and I will fix it.  It's a simple if to create
0
 
dereksheahanAuthor Commented:
Here's the full redesigned page:
Cheers,

<?php require_once('../Connections/GSM_mobile.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']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}



mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsNetworks = "SELECT * FROM network_table";
$rsNetworks = mysql_query($query_rsNetworks, $GSM_mobile) or die(mysql_error());
$row_rsNetworks = mysql_fetch_assoc($rsNetworks);
$totalRows_rsNetworks = mysql_num_rows($rsNetworks);

$colname_rsModels = "-1";
if (isset($_GET['Type_variable'])) {
  $colname_rsModels = (get_magic_quotes_gpc()) ? $_GET['Type_variable'] : addslashes($_GET['Type_variable']);
}
mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsModels = sprintf("SELECT mod_model_id, mod_model_name, mod_type_id FROM model_table WHERE mod_type_id = %s", $colname_rsModels);
$rsModels = mysql_query($query_rsModels, $GSM_mobile) or die(mysql_error());
$row_rsModels = mysql_fetch_assoc($rsModels);
$totalRows_rsModels = mysql_num_rows($rsModels);

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsId = "SELECT det_user_id FROM details_table";
$rsId = mysql_query($query_rsId, $GSM_mobile) or die(mysql_error());
$row_rsId = mysql_fetch_assoc($rsId);
$totalRows_rsId = mysql_num_rows($rsId);
?><form action="<?php echo $editFormAction; ?>" method="POST" name="remote_user" id="remote_user">
  <p>
    <label>User Name
    <input name="text_username" type="text" id="text_username">
    </label>
  </p>
  <p>
    <label>Email Address
    <input name="text_email_add" type="text" id="text_email_add">
    </label>
  </p>
  <p>
    <label>Model:
    <select name="modeldrop" id="modeldrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsModels['mod_model_id']?>"><?php echo $row_rsModels['mod_model_name']?></option>
      <?php
} while ($row_rsModels = mysql_fetch_assoc($rsModels));
  $rows = mysql_num_rows($rsModels);
  if($rows > 0) {
      mysql_data_seek($rsModels, 0);
        $row_rsModels = mysql_fetch_assoc($rsModels);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>If Other Phone Enter Here
    <input name="text_if_other" type="text" id="text_if_other">
    </label>
</p>
  <p>
    <label>Network
    <select name="networkdrop" id="networkdrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsNetworks['net_network_id']?>"><?php echo $row_rsNetworks['net_network_name']?></option>
      <?php
} while ($row_rsNetworks = mysql_fetch_assoc($rsNetworks));
  $rows = mysql_num_rows($rsNetworks);
  if($rows > 0) {
      mysql_data_seek($rsNetworks, 0);
        $row_rsNetworks = mysql_fetch_assoc($rsNetworks);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>IMEI Number
    <input name="text_IMEI" type="text" id="text_IMEI">
    </label>
  </p>
  <p>
    <label>Submit
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="remote_user">
<input type="hidden" name="user_id_fk" value=""></form>
<?php
mysql_free_result($rsNetworks);

mysql_free_result($rsModels);

mysql_free_result($rsId);
?>


0
 
Jason C. LevineNo oneCommented:
if (isset($_POST['text_if_other'])) {

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add) VALUES (%s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}
} else {

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}



}
0
 
dereksheahanAuthor Commented:
Hi Jason,
think I make the correct alteration to your code but the page keeps coming up blank in the browser! I'll keep playing with it here. Here's the code with your alteration.
Cheers,
D

<?php require_once('../Connections/GSM_mobile.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']);
}
 if (isset($_POST['text_if_other'])) {

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add) VALUES (%s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}
} else {

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO details_table (det_username, det_email_add, det_model_id) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['text_username'], "text"),
                       GetSQLValueString($_POST['text_email_add'], "text"),
                       GetSQLValueString($_POST['modeldrop'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result1 = mysql_query($insertSQL, $GSM_mobile) or die("Result[1] ".mysql_error());
}
$details_table_last_id = mysql_insert_id();

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "remote_user")) {
  $insertSQL = sprintf("INSERT INTO remote_details_table (rem_user_id, rem_if_other, rem_network_id, rem_IMEI_nr) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($details_table_last_id, "int"),
                       GetSQLValueString($_POST['text_if_other'], "text"),
                       GetSQLValueString($_POST['networkdrop'], "int"),
                       GetSQLValueString($_POST['text_IMEI'], "int"));

  mysql_select_db($database_GSM_mobile, $GSM_mobile);
  $Result2 = mysql_query($insertSQL, $GSM_mobile) or die("Result[2]".mysql_error());
}


}


mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsNetworks = "SELECT * FROM network_table";
$rsNetworks = mysql_query($query_rsNetworks, $GSM_mobile) or die(mysql_error());
$row_rsNetworks = mysql_fetch_assoc($rsNetworks);
$totalRows_rsNetworks = mysql_num_rows($rsNetworks);

$colname_rsModels = "-1";
if (isset($_GET['Type_variable'])) {
  $colname_rsModels = (get_magic_quotes_gpc()) ? $_GET['Type_variable'] : addslashes($_GET['Type_variable']);
}
mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsModels = sprintf("SELECT mod_model_id, mod_model_name, mod_type_id FROM model_table WHERE mod_type_id = %s", $colname_rsModels);
$rsModels = mysql_query($query_rsModels, $GSM_mobile) or die(mysql_error());
$row_rsModels = mysql_fetch_assoc($rsModels);
$totalRows_rsModels = mysql_num_rows($rsModels);

mysql_select_db($database_GSM_mobile, $GSM_mobile);
$query_rsId = "SELECT det_user_id FROM details_table";
$rsId = mysql_query($query_rsId, $GSM_mobile) or die(mysql_error());
$row_rsId = mysql_fetch_assoc($rsId);
$totalRows_rsId = mysql_num_rows($rsId);
?><form action="<?php echo $editFormAction; ?>" method="POST" name="remote_user" id="remote_user">
  <p>
    <label>User Name
    <input name="text_username" type="text" id="text_username">
    </label>
  </p>
  <p>
    <label>Email Address
    <input name="text_email_add" type="text" id="text_email_add">
    </label>
  </p>
  <p>
    <label>Model:
    <select name="modeldrop" id="modeldrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsModels['mod_model_id']?>"><?php echo $row_rsModels['mod_model_name']?></option>
      <?php
} while ($row_rsModels = mysql_fetch_assoc($rsModels));
  $rows = mysql_num_rows($rsModels);
  if($rows > 0) {
      mysql_data_seek($rsModels, 0);
        $row_rsModels = mysql_fetch_assoc($rsModels);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>If Other Phone Enter Here
    <input name="text_if_other" type="text" id="text_if_other">
    </label>
</p>
  <p>
    <label>Network
    <select name="networkdrop" id="networkdrop">
      <?php
do {  
?>
      <option value="<?php echo $row_rsNetworks['net_network_id']?>"><?php echo $row_rsNetworks['net_network_name']?></option>
      <?php
} while ($row_rsNetworks = mysql_fetch_assoc($rsNetworks));
  $rows = mysql_num_rows($rsNetworks);
  if($rows > 0) {
      mysql_data_seek($rsNetworks, 0);
        $row_rsNetworks = mysql_fetch_assoc($rsNetworks);
  }
?>
    </select>
    </label>
  </p>
  <p>
    <label>IMEI Number
    <input name="text_IMEI" type="text" id="text_IMEI">
    </label>
  </p>
  <p>
    <label>Submit
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="remote_user">
<input type="hidden" name="user_id_fk" value=""></form>
<?php
mysql_free_result($rsNetworks);

mysql_free_result($rsModels);

mysql_free_result($rsId);
?>
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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