Jump to content

Archived

This topic is now archived and is closed to further replies.

Jessica

Inserting into Multiple Tables in One Query

Recommended Posts

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*

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
10 shouldn't be a problem -- provided you're not opening and closing a new DB connection for each successive query.

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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 :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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...

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[!--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!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.