how to make a "sophisticated" insert record form ?

[EricRobineau]

hello
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)

Main content Table:
IDRef = primary key
IDLocation = foreign key --related to tables city->region->country
IDDescription = foreign key --related to table Description (=several
big and small text fields)
IDImages = foreign key --related to image (=image and thumbs path, alt
text, etc..)

Do you know any tutorial, help or solution ?

Thank you very much

[Nikolai Chuvakhin]

Re: how to make a "sophistic ated" insert record form ?

ericrobineau@ya hoo.fr (EricRobineau) wrote in message
news:<c4ba8d44. 0312190256.7adb 60be@posting.go ogle.com>...[color=blue]
>
> 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)[/color]

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="descripti on"></textarea>',
'<select name="countryID ">';
while ($record = mysql_fetch_arr ay ($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

[EricRobineau]

Re: how to make a &quot;sophistic ated&quot; insert record form ?

I'm still a newbie so I'm not sure I got it all but I have a good example to
start with
Thanks for your help