Solved

Insert form data into two MySQL tables problem

Posted on 2011-02-13
5
378 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
[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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

738 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