insert into multiple tables linked by foreign keys (PHP, MySQL)

[AdventDeo]

Hello,

I've read on how to insert properly into two linked tables using mysql_insert_id() function from:
http://www.webproworld.com/web-programming-discussion-forum/87197-php-insert-different-records-into-multiple-mysql-tables-same-unique-id.html

now how about if i want it to be from 4 linked tables? can I still use the function? and I want some to be a dropdown lists.

My tables are:
Client(clientID, Name, Address, ContactNo, countryID*, supportID*)
Environment(environmentID, environmenttypeID, Hostname, ClientID)
Environenttype(environmenttypeID, Type)
System(systemID, Label, environmentID)

Note:
-*ignore these fields for this thread
-all IDs are auto_inc int except for environment type
-There are only 3 fixed environment types:
--------id--------|-------type------
--------P--------|----Production--
--------T--------|--------Test------
--------D--------|--------DR--------

And I want my form to be in one page like:

------Client------
Select clients' name: [drop-down box here|v|]
(other fields like address and contact will be echoed here based on the name selected)
------Environment------
Type: [show environmenttype.type where environmenttype.id=environment.id(drop-down box)|v|]
Hostname: [enter text]
------System------
Label: [enter text]

[Back] [Submit]

and after the user submits, they will be redirected to another page where they'll insert more data to other tables inside the db based on the id of the system submitted here. but i guess that's a different thing altogether.

Please help me in giving some light as to how to make this input form. Thank you so much!

[diafol]

I don't understand what you want to achieve with mysql_insert_id.

Creating the form shouldn't use mysql_insert_id.

If you need to get data into dropdown that comes from more than one table, use INNER JOINs, e.g.

SELECT table1.field2 AS id, table1.field8 AS name, table2.field3 AS label, table2.field4 AS confirm FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2 WHERE table1.field4 = 'test'

The 'while loop' will then allow you to populate the option tags in your select widget (dropdown).

<?php

...(previous mysql code and retrieval)...

//assume $result is your resultset from mysql_query()

while($data = mysql_fetch_array($result)){
  $label = stripslashes($data['label']);
  $option .= "\n\t<option id="{$data['id']}">{$label}</option>"; 
}
?>

<select id="tractors">
<?php echo $option; ?>
</select>

The above code joins info from 2 tables into the dropdown options. Personally, I'd place all the php routines above the DTD unless you're using a templating system like Smarty etc.

Check out the usage of INNER JOINS in the mysql user manual for more complicated scenarios.

[diafol]

Oh for goodness sakes!! I did it agin. I'm forever using 'id' instead of 'value' in my option elements.

Please change:

$option .= "\n\t<option id="{$data['id']}">{$label}</option>";

to:

$option .= "\n\t<option value="{$data['id']}">{$label}</option>";

[AdventDeo]

Okay, maybe I'm not clear enough and I apologize...

I want to make a form where user can input the information they want to add into the database... i need to use the mysql_insert_id() because of the foreign keys I have to assign automatically...

I think this should clarify things...

[diafol]

and after the user submits, they will be redirected to another page where they'll insert more data to other tables inside the db based on the id of the system submitted here. but i guess that's a different thing altogether.

OK, that is a different ask.

The fact that you're asking the user for hostname and system label will cause problems. Perhaps these should be provided in a dropdown?

Because you're doing this the environment table data and the system table data are directly linked to the client via 1:1 relationship. The data in these tables is not available to other clients, therefore I fail to see why they are in a separate table.

A healthier table would be (IMO):

CLIENT
clientID, Name, Address, ContactNo, countryID*, supportID*, environmenttypeID, Hostname, Label)

ENVIRONMENTTYPE
Environenttype(environmenttypeID, Type)

I don't think that you gain anything from the two other tables, as the data is unique to the client and to each other (1:1). I don't know if I'm making sense to you.

In this way you'll cut down on the complexity of your db and form.

[AdventDeo]

Mmm.. Actually one client will have 3 Environments named Production, Test, and DR... and each Environment can have many Systems... e.g. Client 1 Production sys 1, Client 1 Production sys 2, etc..

Hence am pretty sure my relational table are fine...

Anw, I got the thing solved using session handling... thank u anw for ur time to reply me! =)

Here's the 3 files (quite long so don't bother if you don't wanna read) just for reference. Am not closing this thread yet, though, as am opening the possibility of people giving me feedback on how I can improve my codes:

addenvform.php

<?php session_start(); ?>

<html>
<head>
</head>

<body background = "background.gif">
<center>

<table style="width:902px;margin-top:0px;margin-bottom:0px;background-color:#fff9e3;border="0" cellpadding="0" cellspacing="0">
<tr><td align="center"><a name="top"></a>
<img style="margin-top:0px" border="0" src="thomson_reuters_logo.gif" width="500" height="104">
</td></tr>
</table>

<hr />

<fieldset>
<legend>
Record New Environment:
</legend>

<form name=sel>
<table>
<tr><td align="left">Country:</td>
<td><font id=country><select>
<option value='0'>=================</option>
</select></font></td>
</tr>
<tr><td align="left">Client:</td>
<td><font id=client><select>
<option value='0'>=================</option>
</select></font></td>
</tr>
</table>
</form>

<form method="post" action="addenvsubmit.php">
<table>
<tr><td align="left">Environment:</td>
<td><input type="text" name="env" size="20"></td>
</tr>
<tr><td colspan="2">
<p align="center">
<input type="submit" value="Enter record">
</td></tr>
</table>
</form>

</fieldset>


</center>
</body>
</html>

<script language=Javascript>
function Inint_AJAX() {
try { return new ActiveXObject("Msxml2.XMLHTTP");  } catch(e) {}
try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch(e) {}
try { return new XMLHttpRequest();          } catch(e) {}
alert("XMLHttpRequest not supported");
return null;
};

function dochange(src, val) {
var req = Inint_AJAX();
req.onreadystatechange = function () {
 if (req.readyState==4) {
      if (req.status==200) {
           document.getElementById(src).innerHTML=req.responseText;
      }
 }
};
req.open("GET", "addenvdropdown.php?data="+src+"&val="+val);
req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=iso-8859-1");
req.send(null);
}

window.onLoad=dochange('country', -1);
</script>

addenvdropdown.php

<?
session_start();

header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");

header("content-type: application/x-javascript; charset=tis-620");

$data=$_GET['data'];
$val=$_GET['val'];

include 'config.php';
include 'opendb.php';

if ($data=='country') {
  echo "<select name='country' 
  onChange=\"dochange('client', this.value)\">\n";

  echo "<option value='0'>Select Country</option>\n";

  $result=mysql_query("SELECT c.`Country ID`, c.`Name` 
		       FROM clientconfiguration.country c 
                       ORDER BY c.`Name`") 
		       or exit (mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='client') {
  echo "<select name='client'
  onChange=\"dochange('env', this.value)\">\n";

  echo "<option value='0'>Select Client</option>\n";    
               
  $result=mysql_query("SELECT cl.`Client ID`, cl.`Name` 
		       FROM `client` cl, country co 
		       WHERE cl.`Country ID` = co.`Country ID`
		       AND cl.`Country ID` = '$val'
		       ORDER BY cl.`Name`") 
		       or exit(mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='env') {
  echo "<select name='env'>\n";
               
  $_SESSION['envid']= $val;

}

echo "</select>\n";

include 'closedb.php';

?>

addenvsubmit.php

<?php

session_start();

include 'config.php';
include 'opendb.php';

$envid = $_SESSION['envid'];


$sql="INSERT INTO Environment 
VALUES (NULL, NULL, '$_POST[label]',".$envid.")";

if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }

echo "1 record added";


include 'closedb.php';
session_destroy();

?>

[diafol]

Ok, didn't realise you could have multiple entries for a client. This wasn't obvious from the form.

My solution is still relational but can't do what you want.

I don't have the time at the moment to look at the code, but I hope to get back to you later.

[diafol]

If you've got a login form where a client's details are checked you could get the client_id and client name and place that into session variables, to be used in the form.

I'd use a single form to add new environments (host, system) and not bother with ajax.

<?php
  ...(retrieve environment type data from db - in $result)....
  while ($data = mysql_fetch_array($result)){
     $envoptions .= "\n\t\t<option value='{$data['id']}'>{$data['type']}</option>";
  }
?>

<form ....> 
 <p>Name: <?php echo $_SESSION['client_name'];?></p>
 <input type="hidden" value="<?php echo $_SESSION['client_id'];?>" id="id" name="id" />
 <label for="envtype">Environment type:</label>
 <select id="envtype" name="envtype">  
   <?php echo $envoptions;?>
 </select>
 <label for="host">Hostname:</label>
 <input id="host" name="host" type="text" />
 <label for="system">System:</label>
 <input id="system" name="system" type="text" />
 <input type="submit" id="subrec" name="subrec" value="Enter record">
</form>

This should give you all the ids required to update all the various tables via $_POST array.

[AdventDeo]

Hey thanks alot dude! I shall mark this a solved! =)