inserting into multiple tables

using one form with sql/php to insert into more than one table

[Gertrude]

I just started learning php and mysql about a week ago, so I'm still a complete newbie and my knowledge of this is terribly simple. So far, I've learned the basics of mysql, how to use html forms with php to insert and update information into mysql, and how to post information from the database onto my site.

I've been adding to the information stored in the database and it is becoming too complex to be reasonably organized in one table. I can split the types of information in a section into three major groups, each with its own table. What I would like to do is be able to submit all of my information to more than one table at a single time.

I haven't had much luck finding examples of how to do this, and I've even been told it can't be done. There must be some way around having to make three separate forms for each type of data in a section. Can it be done, and if so, how? And if it really can't, what would be a good alternative?

[daisho]

You can have 1 form that asks for all the information. Nothing stops you from having 3 insert statements that get run when the 1 form is posted.

daisho

[lorax]

I'd second daisho and add that you could even use one of the INSERT statements as your primary. That first table would have an AUTOINCREMENT field. Once the insert is done get the ID# of the AUTOINCREMENT field and use it as an ID# for the other tables as well. This will allow you to keep track of records across the mulitiple tables.

[Birdman]

Sure it can be done! You just need to build your MySQL [mysql.com] query accordingly.

$query = "INSERT INTO my_table (id, val1, val2) VALUES ('', '$form_val1', '$form_val2');
INSERT INTO my_other_table (id, val3, val4) VALUES ('', '$form_val3', '$form_val4');";
mysql_query [php.net]($query);

I hope that makes sense to you. You basically do two INSERTs and use the posted variables in each IMSERT accordingly.

knew I typed that one too slow

Good point about using the id in the other table, lorax. I usually use a unique auto-increment field for every table and then have another field with an index that will contain the id from the main table to cross-reference it.

[Gertrude]

Thanks for the responses but it still isn't working for me. Whenever I add the 2nd INSERT, none of my information goes to either of my tables. I don't get any errors though. Birdman, I copied and pasted that code exactly (replacing the tables and values with my own information), and it still does the same thing. I deleted the 2nd INSERT and it worked.

I was having a problem when I first began submitting information to my table with forms that also didn't give me any errors. I had to use GET to make it work.

So instead of this:

$query = "INSERT INTO table (value1, value2) VALUES ('$value1', '$value2');
";
mysql_query($query);

I have to use this:

$query = "INSERT INTO table (value1, value2) VALUES ('$_GET[value1]', '$_GET[value2]');
";
mysql_query($query);

Could this be part of the problem? It's the only other problem I've had with php/sql so far that didn't give me an error message but still didn't work. And even if it isn't part of the problem with having two inserts, I would still like to know if there is a way I could get my forms to work without using it.

[Birdman]

If you have phpMyAdmin available, you can test your MySQL [mysql.com] query from their interface and see what's going on.

1) First, change the mysql_query() [php.net] line in your script to echo $query;
2) Then, copy/paste the output of the echo statement to your phpMyAdmin textarea and submit.

[daisho]

Ok a couple problems with this. I would change your SQL to:

$query = "INSERT INTO table (value1, value2) VALUES ('${_GET['value1']}', '${_GET['value2']}')";
mysql_query($query);

That's the basic format I use. Now you do not need the ";" at the end of the SQL. (though ofcourse you still need it after the last quote as the end of the PHP statement.

Also not quoting the value inside the array (ie $_GET[value1]) is a PHP syntax warning. With warnings surpressed you will not see the warning and all will work but you could run into some other problems. Namely Defines and Depriciation. Once you quote the key you must ad the "{}" as I have done or you _WILL_ get a PHP Parse error.

Next I would use $_REQUEST rather $_GET. $_REQUEST hosts all POST and GET variables which will make your code a little more portable (It also does COOKIE variables). There are some times when you wouldn't want to use $_REQUEST but for most cases I would.

Lastly and not shown in my example is you should _ALWAYS_ use mysql_escape_string to ensure data being passed cannot contain security holes. This is extremly important.

Here is another example that includes everything. A little more complicated but it will do the trick and be as safe as possbile:

$query = "INSERT INTO table (value1, value2) VALUES (
'".mysql_escape_string($_REQUEST['value1'])."', '".mysql_escape_string($_REQUEST['value2'])."')";
if(!mysql_query($query) ) {
print "Error with query: \"$query\"<br>".mysql_error();
// Simple error handling...
}

Let me know if I can clarify anything.

daisho

[Gertrude]

Wow, thanks for taking the time to type all that out. I won't have access to a computer I can check it on until later tonight so I'm not sure if it works for me yet. I can kind of follow along based on what you explained and what I've learned so far, but there's still a lot there that isn't clicking with me yet.

Going by your example, how is it supposed to look when I add more insert statements?

[daisho]

You just build a new query string and run mysql_query against it again. Keep doing that over and over again.

ie.

$query = "INSERT INTO table (value1, value2) VALUES (
'".mysql_escape_string($_REQUEST['value1'])."', '".mysql_escape_string($_REQUEST['value2'])."')";
if(!mysql_query($query) ) {
print "Error with query: \"$query\"<br>".mysql_error();
// Simple error handling...
}

$query = "INSERT INTO table2 (value1, value2) VALUES (
'".mysql_escape_string($_REQUEST['value10'])."', '".mysql_escape_string($_REQUEST['value20'])."')";
if(!mysql_query($query) ) {
print "Error with query: \"$query\"<br>".mysql_error();
// Simple error handling...
}

[daisho]

BTW looks like this is your first set of posts and we all forgot to welcome you :).

Welcome to Web Master World Gertrude.

daisho

[Gertrude]

I'm also curious about why I have to use GET, POST, and REQUEST when I insert data. I'm not really asking why I should or what the benefits are, but rather why I have to use them or else it doesn't work at all. I looked up a lot of information about inserting data and nearly all of the tutorials and examples I found don't use them. So I take it as default that most people don't have to do that?

[daisho]

It has to do with different settings in PHP. This is all explained in the docs at php.net but I will give a quick rundown of history.

PHP has a feature called "Register Globals". This "feature" takes all the parameters that you send to it in a request and makes a variable out of it.

ie:
foo.com/test.php?name=daisho&lang=php

would give you 2 variables in the program called $name and $lang.

This is a security issue since problems could happen if a malicious user added a new variable that you used in your program but for some reason did not initialize. Your code would then use the passed value. This in most cases is not the behaviour you wanted.

As of a couple PHP versions ago they have turned off "Register Globals" to solve this problem. It can still be turned on for compatibility but you should not use them. Instead they gave you a few arrays that are "User Passed" values and theirfore should not be trusted. The $_REQUEST is an array the consists of everything from $_GET $_POST and $_COOKIES which is why I recommened using $_REQUEST makes life easier.

Many older tutorials you will find will show examples from a time when "Register Globals" was on. It seems that your server does not have "Register Globals" on which is why you ran into some problems.

I hope that clears things up.

daisho.

[edited by: jatar_k at 5:09 am (utc) on April 30, 2003]

[Gertrude]

That's exactly what I wanted to know. Thanks for the welcome... seems like a good place here :)

[grahamstewart]

It's the only other problem I've had with php/sql so far that didn't give me an error message but still didn't work.

This should generate a warning about using an undefined variable. You'll probably find that your error reporting isn't turned all the way up.

To maximise the number of errors reported (which during development is a very good thing) open your php.ini file and look for the error_reporting setup variable - change it to..

error_reporting = E_ALL

You might also want to set the log_errors = On and give a sensible path for error_log. That way you can refer to the log file to read the error messages if they get garbled in amongst html on screen.

As a final step, consider switching display_errors = Off in your online version. That way users aren't faced with weird error messages but you can still find them in the error log.