How to insert data from 1 form into multiple tables WITH relational IDs

Total php/sql noob here: I’m working my way through Kevin Yank’s (Sitepoint) really great “Build Your Own Database Driven Web Site Using PHP & MySQL” and am following pretty much everything.

The book uses a Joke submission website as a working example. I’m not understanding one part, though:

If I wanted to have a form (keeping with the joke example) where a user submits their name, email address and a joke all at the same time, how do I write to multiple tables at once and preserve their relationships in the database?

if the three tables are:

Author
id | name

Email
id | email | authorid

Joke
id | joketext | authorid

if this is how I’m going about it (cleaned up example):

if (isset($_GET[‘addjokeauthoremail’]))
{
$name = mysqli_real_escape_string($link, $_POST[‘name’]);
$email = mysqli_real_escape_string($link, $_POST[‘email’]);
$joketext = mysqli_real_escape_string($link, $_POST[‘joketext’]);
$sql = “INSERT INTO tblAuthor SET name=‘$firstname’”;
}

how do I insert that data to the 3 different tables while keeping the PRIMARY KEY that is auto-incremented by MySQL for the Author ID column the same as the authorid column in the other two tables?

Any help would be greatly appreciated!

After you insert the name in table Author, you can use mysql_insert_id to get the value of the id created for that insert and then use it as the value for authorid in the inserts for the other 2 tables.

Another option is to use last_insert_id

A third option is after you insert in table Author, run a query to retrieve the id where name = the name you just inserted and then use the retrieved id as the authorid in the inserts in the other 2 tables.

Also, just a suggestion - I would change the name of id in Author to authorid so that it is clear that the authorid in the 3 tables represents the same type of id data.

Great, I’ll investigate all of these. Is any of them considered a best practice (or better practice than the other two)?

Thanks for the tip. I will do that.

So this doesn’t work to write to multiple tables:

$sql = “INSERT INTO author SET name=‘$firstname’, INSERT INTO email SET email=‘$email’”;

Is there a way to do that? Do I just separate all the variables and INSERT commands and run them one after another?

I’m not sure if any is considered “best practice” but personally I would just run a separate select query (option 3) which guarantees the correct id is retrieved assuming the Author insert was successful.

You can run multiple queries in one go in an sql GUI like SQLyog or phpMyAdmin. But [fphp]mysql_query[/fphp] can run only 1 query at a time, so you will have to run the 3 inserts separately, but do the Author insert first so you can get the authorid for the other 2 inserts.

aidos, thanks a lot. you’ve saved me a lot of potential headache and probably saved my weekend! thanks!

You’re welcome :slight_smile:

And I can think of much more fun ways to get a headache this weekend :lol: :drink:

Happy New Year…!!! :Partydude:

Thinking about your suggestion to write to the Author table and then select ID from the Author table where the name = name just submitted…assuming i have an infinite amount of users, isn’t there a hole in this? Even if I said “SELECT id FROM author WHERE name=‘$name’ AND joketext=‘$joketext’”

hypothetically, if two people with the same name submit the same joke, wouldn’t I have a problem?

struggling with mysql_insert_id. The following works. It successfully connects to the db and inserts the info into two separate tables:

// Insert New Joke
if (isset($_GET[‘jokesubmit’]))
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;

$firstname = mysqli_real_escape_string($link, $_POST['name']);
$sql = "INSERT INTO tblAuthor SET name='$name'";
		
if (!mysqli_query($link,$sql))
{
die('Error: ' . mysql_error());
}

$joketext = mysqli_real_escape_string($link, $_POST['joketext']);
$sql = "INSERT INTO tblJoke SET joketext='$joketext'";
		
if (!mysqli_query($link,$sql))
{
die('Error: ' . mysql_error());
}

exit();

}

However, if I try to use mysql_insert_id on the joketext insertion part (as below):

// Insert New Joke
if (isset($_GET[‘jokesubmit’]))
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;

$firstname = mysqli_real_escape_string($link, $_POST['name']);
$sql = "INSERT INTO tblAuthor SET name='$name'";
		
if (!mysqli_query($link,$sql))
{
die('Error: ' . mysql_error());
}

    [b]$authorid = mysql_insert_id($link);[/b]

$joketext = mysqli_real_escape_string($link, $_POST['joketext']);
$sql = "INSERT INTO tblJoke SET joketext='$joketext', authorid=$authorid'";
		
if (!mysqli_query($link,$sql))
{
die('Error: ' . mysql_error());
}

exit();

}

I get this error: “Warning: mysql_insert_id(): supplied argument is not a valid MySQL-Link resource”

I’ve tried without the ($link)…I’ve tried ($link,$sql). What am I doing wrong here?

well, it actually was working…i simply had different variable names for $authorid and ‘authorid’ in the SQL statement. (changed the variables for the purpose of making them public here)

ah, what fun this is :injured:

Yes you would and it would be a big one if you allowed multiple rows with the same name in table Author if you didn’t have a way of distinguishing users with the same name. I assumed the name field would be unique.

Say you had a requirement to extract all the jokes for a particular author named Fred and you had at least 2 records in Author with the name field set to Fred. With your current table structure you can’t distinguish the jokes from the different Freds. One way around this is to make the users login with a unique username before submitting a joke or at least make them register a unique username which they must submit with their joke to identify themselves.

gotcha, thanks

i do have a real world project in which i’m applying some of these methods and techniques, but I’m not actually collecting unique users at this point.