UPDATE/INSERT large amounts of data???

[gunhoe86]

I'm building a database for a trading card game in which the user can insert/update his/her deck, which is 60-75 different rows. What's the best way to do this?

<?php

$query_maindeck = "SELECT * FROM decks where deck_id=\"$deck_id\" and quantity > \"0\" "; 
$result_maindeck = mysql_query($query_maindeck);

$query_sb = "SELECT * FROM decks where deck_id=\"$deck_id\" and sb > \"0\" "; 
$result_sb = mysql_query($query_sb);

while ($row = mysql_fetch_assoc($result_maindeck))
{
echo("<p>Quantity: <input name=\"quantity\" type=\"text\" value=\"$row[quantity]\" size=\"15\"> Card Name: <input name=\"card\" type=\"text\" value=\"$row[card]\" size=\"15\"> 
Card Type: <input name=\"type\" type=\"text\" value=\"$row[type]\" size=\"15\"> Card Text: <input name=\"card_text\" type=\"text\" value=\"$row[card_text]\" size=\"15\"> 
Cost: <input name=\"cost\" type=\"text\" value=\"$row[cost]\" size=\"15\"> Power/Toughness: <input name=\"pt\" type=\"text\" value=\"$row[pt]\" size=\"15\"></p>");
}

echo("<tr><td>Sideboard:</td></tr>");

while ($row2 = mysql_fetch_assoc($result_sb))
{
echo("<p> Quantity: <input name=\"quantity\" type=\"text\" value=\"$row2[sb]\" size=\"15\"> Card Name: <input name=\"card\" type=\"text\" value=\"$row2[card]\" size=\"15\"> 
Card Type: <input name=\"type\" type=\"text\" value=\"$row2[type]\" size=\"15\"> Card Text: <input name=\"card_text\" type=\"text\" value=\"$row2[card_text]\" size=\"15\"> 
Cost: <input name=\"cost\" type=\"text\" value=\"$row2[cost]\" size=\"15\"> Power/Toughness: <input name=\"pt\" type=\"text\" value=\"$row2[pt]\" size=\"15\"></p>");
}

?>

For each set of <insert> tags in each while() loop, I'd like to insert/update that row in the db. Is running 60-75 insert/update queries the best way? It can't be very efficient....

Does this make any sense?

~JOSH

[tomhath]

I'd go ahead with what you have. Unless your database server is really loaded by lots of people loading decks constantly, the database shouldn't have any trouble keeping up.

It's usually better to keep it simple at first and get it working right than to optimize something that doesn't need to be optimized.

[gunhoe86]

Thanks for the advice, I'll try it out.

~JOSH

[Roger_Ramjet]

60-70 queries is definitely NOT the best way. Unless you are running an old version you can use 1 query with multiple value lists

The INSERT ... VALUES form with multiple value lists is supported in MySQL 3.22.5 or later.

Instead of looping through the input and building and running a query with one set of values for each input, loop through and build a query with multiple value lists and then run it once.

Unless your database server is really loaded by lots of people loading decks constantly

OR you are on a shared server with lots of other websites, then your users will suffer from server load.