Jump to content


Photo

Inserting into Multiple Tables in One Query


  • Please log in to reply
13 replies to this topic

#1 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 15 February 2006 - 04:09 PM

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

I want to consolidate this into one query
$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();
}

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*
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 February 2006 - 05:28 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 15 February 2006 - 05:34 PM

[!--quoteo(post=346076:date=Feb 15 2006, 11:28 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 11:28 AM) View Post[/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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 February 2006 - 07:09 PM

10 shouldn't be a problem -- provided you're not opening and closing a new DB connection for each successive query.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 15 February 2006 - 07:12 PM

[!--quoteo(post=346115:date=Feb 15 2006, 01:09 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 01:09 PM) View Post[/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
if ($db_result = $db_conn->Execute($insert_items)) {
$db_result->Close();
}
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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 February 2006 - 08:40 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 15 February 2006 - 08:42 PM

[!--quoteo(post=346149:date=Feb 15 2006, 02:40 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 15 2006, 02:40 PM) View Post[/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 :)
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 February 2006 - 07:45 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 16 February 2006 - 09:59 PM

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.

#10 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 16 February 2006 - 10:00 PM

[!--quoteo(post=346551:date=Feb 16 2006, 03:59 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 16 2006, 03:59 PM) View Post[/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...
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 February 2006 - 10:07 PM

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".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 16 February 2006 - 10:37 PM

[!--quoteo(post=346554:date=Feb 16 2006, 04:07 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 16 2006, 04:07 PM) View Post[/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?
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 February 2006 - 11:37 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#14 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 16 February 2006 - 11:47 PM

[!--quoteo(post=346584:date=Feb 16 2006, 05:37 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 16 2006, 05:37 PM) View Post[/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!
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users