Inserting into Multiple Tables in One Query

[Jessica]

The sign up form for MySQL Freaks Forum is down for maintainance :(

I want to consolidate this into one query
[code]
$insert_items = "INSERT INTO equipped_items VALUES('$userid', '0', '0', '0', '0', '0', '0')";
if ($db_result = $db_conn->Execute($insert_items)) {
$db_result->Close();
}

$insert_clothing = "INSERT INTO clothing_worn VALUES('$userid', '0', '0', '0', '0', '0', '0', '0')";
if ($db_result = $db_conn->Execute($insert_clothing)) {
$db_result->Close();
}

$insert_stats = "INSERT INTO stats VALUES('$username', '$userid', '$hp', '$hp', '$intel', '$speed', '$strength', '$agility', '$defense', '$theft')";
if ($db_result = $db_conn->Execute($insert_stats)) {
$db_result->Close();
}[/code]

All the tutorials I see when I google Multiple Inserts is for putting multiple entries into one table, and I'm not sure how to do what I want. Help? *thanks*

[fenway]

That's because you can't accomplish the task you desire -- one INSERT statement per table is the best you can do (even though, as you found, you can have a multi-VALUEd insert). BTW, what would it achieve? You're not even putting the same info into all 3 tables -- why consolidate?

[Jessica]

[!--quoteo(post=346076:date=Feb 15 2006, 11:28 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 11:28 AM) [snapback]346076[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That's because you can't accomplish the task you desire -- one INSERT statement per table is the best you can do (even though, as you found, you can have a multi-VALUEd insert). BTW, what would it achieve? You're not even putting the same info into all 3 tables -- why consolidate?
[/quote]

I was afraid it wasn't possible. I wanted to do it all at once so that I didn't have to keep doing queries. I'm trying to plan to scale it for a large userbase, and have plenty of places where I do 10 queries on one page.

Thanks anyway.

[fenway]

10 shouldn't be a problem -- provided you're not opening and closing a new DB connection for each successive query.

[Jessica]

[!--quoteo(post=346115:date=Feb 15 2006, 01:09 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 01:09 PM) [snapback]346115[/snapback][/div][div class=\'quotemain\'][!--quotec--]
10 shouldn't be a problem -- provided you're not opening and closing a new DB connection for each successive query.
[/quote]

I'm doing it exactly the way I typed up there - I'm not very familiar with MySQL, I kinda know just enough to do what I want, but not how it works. Is the
[code]if ($db_result = $db_conn->Execute($insert_items)) {
$db_result->Close();
}[/code]
part where I open the connection? Because if so, than I am doing it each time.

I'm using ADODB to do this, my fiance showed me how initially because he said it'd be easiest for me. Sorry if I'm being dumb.

[fenway]

Apparently, according to [a href=\"http://www.w3schools.com/ado/ado_ref_connection.asp\" target=\"_blank\"]this ADODB reference page[/a] -- you simply Execute() the queries, no need to close the connection after each one.

[Jessica]

[!--quoteo(post=346149:date=Feb 15 2006, 02:40 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 02:40 PM) [snapback]346149[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Apparently, according to [a href=\"http://www.w3schools.com/ado/ado_ref_connection.asp\" target=\"_blank\"]this ADODB reference page[/a] -- you simply Execute() the queries, no need to close the connection after each one.
[/quote]

Thanks so much! I read that page, but I don't see when I do close the query. Should I close it at the end of the pages I open it on, after I've done my 10 queries??

I really appreciate your help :)

[fenway]

I assume that you (or some include script) opens the actual connection at the start of script execution, so yes, you should definitely explicitly close it at the end.

[wickning1]

I don't know about that database package, but if you use mysqli you can use mysqli_multi_query() to send many statements in the same communication. I built support for it into my personal database package, and my tests do show a thin speed increase on a 100Mbps LAN.

The best thing you can do to make your application scale to many users is to make sure NONE of your queries are forced to do a table scan. Not only do table scan queries eat up processor/drive resources, but they also lock the tables involved, so even if the machine has cycles to spare, your users are all locked up waiting on that table scan. Of course, use EXPLAIN to get information about how your query is executed by MySQL.

[Jessica]

[!--quoteo(post=346551:date=Feb 16 2006, 03:59 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 16 2006, 03:59 PM) [snapback]346551[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I don't know about that database package, but if you use mysqli you can use mysqli_multi_query() to send many statements in the same communication. I built support for it into my personal database package, and my tests do show a thin speed increase on a 100Mbps LAN.

The best thing you can do to make your application scale to many users is to make sure NONE of your queries are forced to do a table scan. Not only do table scan queries eat up processor/drive resources, but they also lock the tables involved, so even if the machine has cycles to spare, your users are all locked up waiting on that table scan. Of course, use EXPLAIN to get information about how your query is executed by MySQL.
[/quote]

I'm sorry, I don't even know what a table scan is...

[fenway]

wickining1 is simply referring to have correct indexes set up for your DB tables, so that the server never has to resort to actually seeking through the actual table data to find the records you request. This sounds easier than it actually is, but for the most part, common sense will prevent 90% of "typical" queries from causing a "table scan".

[Jessica]

[!--quoteo(post=346554:date=Feb 16 2006, 04:07 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 16 2006, 04:07 PM) [snapback]346554[/snapback][/div][div class=\'quotemain\'][!--quotec--]
wickining1 is simply referring to have correct indexes set up for your DB tables, so that the server never has to resort to actually seeking through the actual table data to find the records you request. This sounds easier than it actually is, but for the most part, common sense will prevent 90% of "typical" queries from causing a "table scan".
[/quote]

Okay. I've read a lot of articles and even a few books on game design and database design, so I think I have a fair idea of the concept, it's just a few of the technical hang ups that get me. I'd just never heard of a table scan.

My tables all have IDs, so when I need an entry I get the entry at ID x, that's what you mean right?

[fenway]

That's exactly what an index is for -- and yes, generally speaking, most (if not all) tables have a UID field that is indexed as the PRIMARY key. So if you ask for UID=5324521, the DB doesn't have to read through the entire table just to find it, since the index "knows" where the record is located within the table file itself. If this were not the case -- e.g. if you searched for first_name = 'joe' -- and there was no index on first_name, the DB would have no choice but to search the entire table for instances of 'joe', which is several orders of magnitude slower.

Make sense?

[Jessica]

[!--quoteo(post=346584:date=Feb 16 2006, 05:37 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 16 2006, 05:37 PM) [snapback]346584[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That's exactly what an index is for -- and yes, generally speaking, most (if not all) tables have a UID field that is indexed as the PRIMARY key. So if you ask for UID=5324521, the DB doesn't have to read through the entire table just to find it, since the index "knows" where the record is located within the table file itself. If this were not the case -- e.g. if you searched for first_name = 'joe' -- and there was no index on first_name, the DB would have no choice but to search the entire table for instances of 'joe', which is several orders of magnitude slower.

Make sense?
[/quote]

Perfectly :) Thanks for all your help!