DaniWeb Forum Index > Web Development > PHP

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

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Aug 2009

Posts: 31

Reputation: AdventDeo is an unknown quantity at this point

Solved Threads: 0

AdventDeo

Offline

Light Poster

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

Aug 31st, 2009

Hello,

I've read on how to insert properly into two linked tables using mysql_insert_id() function from:
http://www.webproworld.com/web-progr...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!

•

Join Date: Oct 2006

Posts: 1,078

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Offline

Veteran Poster

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

-1

Aug 31st, 2009

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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'
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).

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?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>
<?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.

Happy Humbugging Christmas

•

Join Date: Oct 2006

Posts: 1,078

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Offline

Veteran Poster

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

-1

Aug 31st, 2009

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

Please change:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$option .= "\n\t<option id="{$data['id']}">{$label}</option>";
$option .= "\n\t<option id="{$data['id']}">{$label}</option>";

to:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$option .= "\n\t<option value="{$data['id']}">{$label}</option>";
$option .= "\n\t<option value="{$data['id']}">{$label}</option>";

Happy Humbugging Christmas

•

Join Date: Aug 2009

Posts: 31

Reputation: AdventDeo is an unknown quantity at this point

Solved Threads: 0

AdventDeo

Offline

Light Poster

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

Aug 31st, 2009

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...

•

Join Date: Oct 2006

Posts: 1,078

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Offline

Veteran Poster

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

-1

Sep 1st, 2009

•

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.

Happy Humbugging Christmas

•

Join Date: Aug 2009

Posts: 31

Reputation: AdventDeo is an unknown quantity at this point

Solved Threads: 0

AdventDeo

Offline

Light Poster

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

Sep 1st, 2009

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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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>
 
<?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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?
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';
 
?>
<?
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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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();
 
?>
<?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();

?>

Last edited by AdventDeo; Sep 1st, 2009 at 10:23 pm.

•

Join Date: Oct 2006

Posts: 1,078

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Offline

Veteran Poster

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

-1

Sep 2nd, 2009

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.

Happy Humbugging Christmas

•

Join Date: Oct 2006

Posts: 1,078

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Offline

Veteran Poster

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

-1

Sep 2nd, 2009

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.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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>
<?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>