Solved

Insert form data into two MySQL tables problem

Posted on 2011-02-13
5
374 Views
Last Modified: 2012-08-13
Gentlemen,

I have a small problem that I am unsure how to resolve.  Basically I currently have a simple form that I use to setup appointments.

Right now the way it is setup all of the form fields are sent to one database table in MySQL.  I don't have an issue with getting this done since it is pretty straight forward.  The table looks like this:

Appointments
-----------------------
| ID                         | <-Primary Key
| Name                   |
|Address               |
|Problem                |
------------------------

What I would like to do is have two (2) seperate tables like this (I have already set them up using the InnoDB storage engine and the relationship which I think I did correctly):

App_CustomerName
-----------------------
| ID_client              | <-Primary Key
| Name                   |
|Address               |
------------------------

App_Details
-----------------------
| ID_Job                   | <-Primary Key
| ID_Client                | <-Indexed
|Problem_Text         |
------------------------

Reason being, I would like to at a later time be able to query the job history of a client with the same ID rather than what I have now where the same client may have multiple ID's with the same data.  If I querry for instance the name "Smith" I would come up with quite a few different and the same "Smith".


So, when I make the form field I would like to submit the data fields to the relevant Tables and have the same exact ID_Client data in both tables be the same.  The Primary Key in the App_CustomerName table is set to AUTO_INCREMENT.  I tried a relational database tutorial without any luck.

The form would look something like this:





Unfortunately, I am using Adobe Dreamweaver to assist me.  I know there are a number of you out there that would despise me for using it.  But, since I am a novice; please have pittty on me.

Any assistance would be greatly appreciated.
<?php require_once('../Connections/appointments.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO App_CustomerName (Name, Address) VALUES (%s, %s)",
                       GetSQLValueString($_POST['Name'], "text"),
                       GetSQLValueString($_POST['Address'], "text")),
					   
			   sprintf("INSERT INTO App_Details (Problem_Text) VALUES (%s)",
                       GetSQLValueString($_POST['Problem_Text'], "text"));

  mysql_select_db($database_appointments, $appointments);
  $Result1 = mysql_query($insertSQL, $appointments) or die(mysql_error());
}

mysql_select_db($database_appointments, $appointments);

$query_Recordset1 = "SELECT * FROM App_CustomerName, App_Details";
$Recordset1 = mysql_query($query_Recordset1, $appointments) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!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=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<p>&nbsp;</p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
  <table align="center">
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Name:</td>
      <td><input type="text" name="Name" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Address:</td>
      <td><input type="text" name="Address" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Problem:</td>
      <td><input type="text" name="Problem_Text" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record" /></td>
    </tr>
  </table>
  <input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

Open in new window

0
Comment
Question by:shark1998
  • 2
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34884974
Hi shark1998,

You need to use a PHP function to get the Primary Key from table 1 after insert and then do a second insert record behavior to stick it in table 2.

http://php.net/manual/en/function.mysql-insert-id.php

0
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 34885004
The way to make this happen is to execute one SQL statement at a time. So do your insert into the first table then get the client id to pass to the next insert:
$ID_client = mysql_insert_id();

Open in new window

... then execute the second insert.
0
 

Author Comment

by:shark1998
ID: 34920473
So I understand.  I need to create one form where I enter (say) the Name and address of a client on one page, and then add another form field on another page with the problem text box.  That way when I enter the client information the unique ID is generated on the MySQL server and I can call that same Unique ID on the second page with the problem text box.  Is that what you were describing?

MadaboutASP - I am intrigued, can you show me an example of the mysql_insert_id()?  I appreciate your help.
0
 
LVL 11

Accepted Solution

by:
Ovid Burke earned 500 total points
ID: 34921525
@shark1998:

Try replacing lines 39 through 49 of your code with the following:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  
  mysql_select_db($database_appointments, $appointments);
  
  $insertSQL_1 = sprintf("INSERT INTO App_CustomerName (Name, Address) VALUES (%s, %s)",
                       GetSQLValueString($_POST['Name'], "text"),
                       GetSQLValueString($_POST['Address'], "text"));
  $Result1 = mysql_query($insertSQL_1, $appointments) or die(mysql_error());
 
  $ID_Client = mysql_insert_id(); 
                                          
  $insertSQL_2 = sprintf("INSERT INTO App_Details (ID_Client, Problem_Text) VALUES (%s, %s)",
                        $ID_Client,
			GetSQLValueString($_POST['Problem_Text'], "text"));
  $Result2 = mysql_query($insertSQL_2, $appointments) or die(mysql_error());
  
}

Open in new window


Hopefully, it works and Dreamweaver does not attempt to rewrite it. I do not despise you for using Dreamweaver. I started that way to, but like me, I am sure you are quickly finding out that relying on code generated by Dreamweaver might not be the best option.

I would recommend the book Beginning PHP and MySQL 5 - From Novice to Professional, to help you get a handle on PHP/MySQL.
0
 

Author Closing Comment

by:shark1998
ID: 35175919
Not really what I wanted, but it helps explain a few things.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

685 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