Insert more then one record/row at a time?
Moderator: General Moderators
Insert more then one record/row at a time?
Hi, I'm newer then a newbie!
I have probably 10 PHP scripts running on my site, but I basically just plug and play. I'd like to try to modify one. I setup a database interface called DaDaBIK and it works nicely. I have a youth football site where I'd like coaches to submit their weekly scores. Right now it accepts one score at a time from a form. I'd like to now have 10 entries for their 10 teams on one form and submit it all at once.
I'm an AS/400 programmer and in RPG I would probably place all 10 scores in an array and then loop through it duplicating the process. I'm wondering if anyone is familiar with DaDaBIK or can just give me some basic ideas as to how to loop through the process or what to do. I was assuming that I'd be able to find the 'insert' logic and wrap something around it.
Thanks in advance.
Mike
I have probably 10 PHP scripts running on my site, but I basically just plug and play. I'd like to try to modify one. I setup a database interface called DaDaBIK and it works nicely. I have a youth football site where I'd like coaches to submit their weekly scores. Right now it accepts one score at a time from a form. I'd like to now have 10 entries for their 10 teams on one form and submit it all at once.
I'm an AS/400 programmer and in RPG I would probably place all 10 scores in an array and then loop through it duplicating the process. I'm wondering if anyone is familiar with DaDaBIK or can just give me some basic ideas as to how to loop through the process or what to do. I was assuming that I'd be able to find the 'insert' logic and wrap something around it.
Thanks in advance.
Mike
which database do you use?
afaik in any sql-92 compatible db you can state as many insert-records as you like
if it is a web-based interface you may let php create the arrays for you from the posted data. To see how this would work play a bit arround with
afaik in any sql-92 compatible db you can state as many insert-records as you like
Code: Select all
INSERT INTO tablename (field1,field2....,fieldn) VALUES (v1, v2, ...vn),(va, vb, ...vz), (vI, vII, ...v+)....Code: Select all
<html><body>
<pre><?php
print_r($_POST);
?></pre>
<form method="POST">
name: <input type="text" name="arrї0]ї'name']" />
league: <input type="text" name="arrї0]ї'league']" />
<hr/>
name: <input type="text" name="arrї1]ї'name']" />
league: <input type="text" name="arrї1]ї'league']" />
<hr/>
name: <input type="text" name="arrї2]ї'name']" />
league: <input type="text" name="arrї2]ї'league']" />
<hr/>
<input type="submit" />
</form>
</body></html>I'm using a MySQL database and all I know how to do with that is really set it up. DaDaBIK is the interface tool I found on hotscripts.com. I knew enough to put my form on the front end of it.
Here is a link to my form if you want to take a peek. One entry works fine, I'd just like to add 10 so they can enter up to 10 at a time!
Mike
http://www.bux-mont.com/forms/home_asso ... _sheet.php
Here is a link to my form if you want to take a peek. One entry works fine, I'd just like to add 10 so they can enter up to 10 at a time!
Mike
http://www.bux-mont.com/forms/home_asso ... _sheet.php
Thanks Volka. Would I need to do a loop to do this or can I enter field1 ... fieldn in one statement? I'm trying your approach now. I'll let you know how I make out.volka wrote:which database do you use?
afaik in any sql-92 compatible db you can state as many insert-records as you likeCode: Select all
INSERT INTO tablename (field1,field2....,fieldn) VALUES (v1, v2, ...vn),(va, vb, ...vz), (vI, vII, ...v+)....
Thanks again.
depends on what names-properties the input-fields have and how you receive the values.
maybe viewtopic.php?t=3560 helps you
maybe viewtopic.php?t=3560 helps you
what you can do, since the sql query is just a string so far as php cares, you can build up the string in a loop and then just execute it once...
something like
which i use for deleting orders... (in my system up to 40 can be deleted in one go, sure more oculd be but i dont want to)
something like
Code: Select all
<?php
for($i=0;$i<XX;$i++){
$temp = 'Blah' . $i;
if($HTTP_POST_VARSї$temp] =='delete'){
if($string == '')
$string = $temp . " = NULL";
else
$string = $string . ', ' . $temp . " = NULL";
}
}
$sql = 'UPDATE Players SET ' . $string . " WHERE user = '" . $HTTP_SESSION_VARSї'user'] . "'";
$result = mysql_query($sql);
?>Here's where I'm working at. I guess I'll try to stuff everything into a query string ...
function insert_record($HTTP_POST_VARS, $conn, $db_name, $table_name, $table_internal_name)
// goal: insert a new record in the main database
// input $HTTP_POST_VARS (the array containing all the values inserted in the form, $conn, $db_name, $table_name, $table_internal_name
// output: nothing
{
// get the post variables of the form
reset ($HTTP_POST_VARS);
while (list($key, $value) = each ($HTTP_POST_VARS)){
$$key = $value;
} // end while
// get the array containg label ant other information about the fields
$fields_labels_ar = build_fields_labels_array($conn, $table_name, $table_internal_name, "1");
// build the insert statement
/////////////////////////////
$sql = "";
$sql .= "insert into `$table_name` (";
for ($i=0; $i<count($fields_labels_ar); $i++){
if ($fields_labels_ar[$i]["present_insert_form_field"] == "1" or $fields_labels_ar[$i]["type_field"] == "insert_date" or $fields_labels_ar[$i]["type_field"] == "update_date" or $fields_labels_ar[$i]["type_field"] == "ID_user" or $fields_labels_ar[$i]["type_field"] == "password_record"){ // if the field is in the form or need to be inserted because it's an insert data, an update data, an ID_user or a password_record
$sql .= "`".$fields_labels_ar[$i]["name_field"]."`, "; // add the field name to the sql statement
} // end if
} // end for
$sql = substr("$sql", 0, (strlen($sql)-2));
$sql .= ") values (";
for ($i=0; $icount($fields_labels_ar); $i++){
if ($fields_labels_ar[$i]["present_insert_form_field"] == "1"){ // if the field is in the form
switch ($fields_labels_ar[$i]["type_field"]){
case "select_multiple_menu":
case "select_multiple_checkbox":
$sql .= "'";
if (isset($$fields_labels_ar[$i]["name_field"])){ // otherwise the user hasn't checked any options
for ($j=0; $jcount($$fields_labels_ar[$i]["name_field"]); $j++){
$sql .= $fields_labels_ar[$i]["separator_field"].${$fields_labels_ar[$i]["name_field"]}[$j];// add the field value to the sql statement
} // end for
$sql .= $fields_labels_ar[$i]["separator_field"]; // add the last separator
} // end if
$sql .= "', ";
break;
case "date":
$field_name_temp = $fields_labels_ar[$i]["name_field"];
$year_field = $field_name_temp."_year";
$month_field = $field_name_temp."_month";
$day_field = $field_name_temp."_day";
$mysql_date_value = $$year_field."-".$$month_field."-".$$day_field;
$sql .= "'".$mysql_date_value."', "; // add the field value to the sql statement
break;
case "select_single":
$field_name_temp = $fields_labels_ar[$i]["name_field"];
$field_name_other_temp = $fields_labels_ar[$i]["name_field"]."_other____";
if ($fields_labels_ar[$i]["other_choices_field"] == "1" and $$field_name_temp == "......" and $$field_name_other_temp != ""){ // insert the "other...." choice
$sql .= "'".$$field_name_other_temp."', "; // add the field value to the sql statement
$foreign_key_temp = $fields_labels_ar[$i]["foreign_key_field"];
if ($foreign_key_temp != ""){
if (substr($foreign_key_temp, 0, 4) != "SQL:"){ // with arbitrary sql statement the insert in the primary key table is not supported yet
$primary_key_field_temp = substr($foreign_key_temp, (strpos($foreign_key_temp, ".") + 1), (strlen($foreign_key_temp) - (strpos($foreign_key_temp, ".") + 1))); // remove "table_name."
$primary_key_table_temp = substr($foreign_key_temp, 0, strpos($foreign_key_temp, ".")); // remove ".field_name"
if (!table_contains($primary_key_table_temp, $primary_key_field_temp, $$field_name_other_temp)){ // check if the table doesn't contains the value inserted as other
$sql_insert_other = "insert into ".$primary_key_table_temp." (".$primary_key_field_temp.") values ('".$$field_name_other_temp."')";
display_sql($sql_insert_other);
// insert into the table of other
$res_insert = execute_db("$sql_insert_other", $conn);
} // end if
} // end if
} // end
else{ // no foreign key field
if (!eregi($fields_labels_ar[$i]["separator_field"].$$field_name_other_temp.$fields_labels_ar[$i]["separator_field"], $fields_labels_ar[$i]["select_options_field"])){ // the other field inserted is not already present in the $fields_labels_ar[$i]["select_options_field"] so we have to add it
$select_options_field_updated = $fields_labels_ar[$i]["select_options_field"].$$field_name_other_temp.$fields_labels_ar[$i]["separator_field"];
$sql_update_other = "update `$table_internal_name` set `select_options_field` = '".$select_options_field_updated."' where `name_field` = '".$field_name_temp."'";
display_sql($sql_update_other);
// update the internal table
$res_update = execute_db("$sql_update_other", $conn);
// re-get the array containg label ant other information about the fields changed with the above instruction
$fields_labels_ar = build_fields_labels_array($conn, $table_name, $table_internal_name, "1");
} // end if
} // end else
} // end if
else{
$sql .= "'".$$field_name_temp."', "; // add the field value to the sql statement
} // end else
break;
default: // textual field and select single
if ($$fields_labels_ar[$i]["name_field"] == $fields_labels_ar[$i]["prefix_field"]){ // the field contain just the prefix
$$fields_labels_ar[$i]["name_field"] = "";
} // end if
$sql .= "'".$$fields_labels_ar[$i]["name_field"]."', "; // add the field value to the sql statement
break;
} // end switch
} // end if
elseif ($fields_labels_ar[$i]["type_field"] == "insert_date" or $fields_labels_ar[$i]["type_field"] == "update_date"){ // if the field is not in the form but need to be inserted because it's an insert data or an update data
$sql .= "'".date("Y-m-d H:i:s")."', "; // add the field name to the sql statement
} // end elseif
elseif ($fields_labels_ar[$i]["type_field"] == "ID_user"){ // if the field is not in the form but need to be inserted because it's an ID_user
$user = get_user();
$sql .= "'$user', "; // add the field name to the sql statement
} // end elseif
elseif ($fields_labels_ar[$i]["type_field"] == "password_record"){ // if the field is not in the form but need to be inserted because it's a password record
$pass = md5(uniqid(microtime(), 1)).getmypid();
$sql .= "'$pass', "; // add the field name to the sql statement
} // end elseif
} // end for
$sql = substr("$sql", 0, (strlen($sql)-2));
$sql .= ")";
/////////////////////////////
// end build the insert statement
display_sql($sql);
// insert the record
$res_insert = execute_db("$sql", $conn);
//////////////////////////////////////////////////////
// send email notification of record update execution
//////////////////////////////////////////////////////
$message = "Boyd -\nAn association has submitted an entry into the score sheet.\n\nhttp://bux-mont.com/forms/index.php\n\nPlease do not reply to this email.\nIt has been generated automatically by the database interface.\n";
mail("webmaster@bux-mont.com", "New Record Inserted", $message,
"From: <webmaster@bux-mont.com>\r\n"
."Reply-To: \"you@yourhost.com\"\r\n"
."X-Mailer: PHP/" . phpversion());
/////////////////////////////////////////////////////////
// end send email notification of record update execution
/////////////////////////////////////////////////////////
} // end function insert_record
To bring up a dead topic. I'm going to attempt this again! Here's what was recently suggested, but I couldn't get it to work.
The suggestion:
its not out of league, its a beginners script, very basic. you need a loop, yes, you dont really need arrays
just name the 25 different boxes different things like scrore1, score2 etc...
in php file a simple way of doing it would be
for($count;$count<25)$count++)
insert into database $_POST["score".$count."" ] if score$count !=""
end loop
My reply and where I'm stuck now:
do i have to declare 'count' somewhere? it doesn't like the beginning of the loop. craps out on that line of code.
Here's what works now. These are frields entered once in the database. I'm thinking now that I might modify this:
PHP:
--------------------------------------------------------------------------------
<?php
include_once "ez_sql.php";
$result = mysql_query("INSERT INTO weeklyscores (assoc_name, league_name, submitted_by, email, week, game_date)
VALUES ('$assoc_name', '$league_name', '$submitted_by', '$email', '$week', '$game_date')");
--------------------------------------------------------------------------------
This code didn't work. If I could just get this field working I can apply it to the others:
PHP:
--------------------------------------------------------------------------------
for($count;$count<5)$count++)
{
insert into weeklyscores $_POST["score_a_".$count."" ] if score_a_$count !="" ;
}
The suggestion:
its not out of league, its a beginners script, very basic. you need a loop, yes, you dont really need arrays
just name the 25 different boxes different things like scrore1, score2 etc...
in php file a simple way of doing it would be
for($count;$count<25)$count++)
insert into database $_POST["score".$count."" ] if score$count !=""
end loop
My reply and where I'm stuck now:
do i have to declare 'count' somewhere? it doesn't like the beginning of the loop. craps out on that line of code.
Here's what works now. These are frields entered once in the database. I'm thinking now that I might modify this:
PHP:
--------------------------------------------------------------------------------
<?php
include_once "ez_sql.php";
$result = mysql_query("INSERT INTO weeklyscores (assoc_name, league_name, submitted_by, email, week, game_date)
VALUES ('$assoc_name', '$league_name', '$submitted_by', '$email', '$week', '$game_date')");
--------------------------------------------------------------------------------
This code didn't work. If I could just get this field working I can apply it to the others:
PHP:
--------------------------------------------------------------------------------
for($count;$count<5)$count++)
{
insert into weeklyscores $_POST["score_a_".$count."" ] if score_a_$count !="" ;
}