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
Solved

Insert form data into two MySQL tables problem

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

856 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