Solved

Insert form data into two MySQL tables problem

Posted on 2011-02-13
5
363 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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…
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 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 …

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now