Page 1 of 1

Insert more then one record/row at a time?

Posted: Sun Oct 13, 2002 6:51 am
by mjhaston
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

Posted: Sun Oct 13, 2002 9:56 am
by volka
which database do you use?
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+)....
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

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>

Posted: Sun Oct 13, 2002 12:39 pm
by mjhaston
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

Posted: Sun Oct 13, 2002 8:02 pm
by mjhaston
volka wrote:which database do you use?
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+)....
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.

Thanks again.

Posted: Sun Oct 13, 2002 9:00 pm
by volka
depends on what names-properties the input-fields have and how you receive the values.
maybe viewtopic.php?t=3560 helps you

Posted: Mon Oct 14, 2002 4:36 am
by Coco
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

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);
?>
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)

Posted: Mon Oct 14, 2002 8:38 pm
by mjhaston
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



Posted: Sat Jun 28, 2003 10:07 pm
by mjhaston
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 !="" ;
}