er**********@yahoo.fr (EricRobineau) wrote in message
news:<c4**************************@posting.google. com>...
I have a DB with many inter-related tables (MySQL)
My main table called "content" has almost only foreign keys (integers)
some have a 3 level relation (ex: content->city->region->country)
I would like admins to be able to insert new records including all the
"content" fields (in 1 page form)
But how can I make an insert form that would display the related
tables as list menus instead of a textfield with a number? (ex: select
country from a drop down menu instead of typing the foerign key
number)
Since I don't know how your tables are set up, let me offer a
hypothetical example...
You have three tables:
content:
IDRef
IDLocation
IDDescription
country:
ID
name
items:
ID
description
To add a new item, whose description will then be associated
with a country, you need something like this (assuming the connection
to the database has already been established):
$select = 'SELECT * FROM country ORDER BY name';
$selected = mysql_query ($select)
or die ('Can't execute the SELECT query...');
echo '<form action="insert.php" method="POST">',
'Enter item's description: <br>',
'<textarea name="description"></textarea>',
'<select name="countryID">';
while ($record = mysql_fetch_array ($selected)) {
echo '<option value="', $record['ID'],
'">', $record['name'];
}
echo '</select><br>',
'<input type="submit" value="Insert">',
'</form>';
This form will generate a POST submission with two fields:
'countryID' and 'description'. Your next script can handle
this submission as a transaction (for brevity, I am not
writing any error checks or ROLLBACKs):
mysql_query ('BEGIN');
mysql_query ("INSERT INTO items SET description = '" .
$_POST['description'] . "'")
or die ();
$itemsID = mysql_insert_id ()
or die ();
mysql_query ("INSERT INTO content SET IDLocation = '" .
$_POST['ID'] . "', IDDescription = '$itemsID'")
or die ();
mysql_query ('COMMIT');
Arranging two INSERT queries into transaction ensures that data
is entered either completely or not at all.
Cheers,
NC
