Jump to content

Inserting into Multiple Tables in One Query


Jessica

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*
Link to comment
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?
Link to comment
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.
Link to comment
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.
Link to comment
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 :)
Link to comment
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.
Link to comment
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...
Link to comment
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".
Link to comment
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?
Link to comment
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?
Link to comment
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.