Insert more then one record/row at a time?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjhaston
Forum Newbie
Posts: 8
Joined: Sun Oct 13, 2002 6:51 am

Insert more then one record/row at a time?

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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>
mjhaston
Forum Newbie
Posts: 8
Joined: Sun Oct 13, 2002 6:51 am

Post 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
mjhaston
Forum Newbie
Posts: 8
Joined: Sun Oct 13, 2002 6:51 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

depends on what names-properties the input-fields have and how you receive the values.
maybe viewtopic.php?t=3560 helps you
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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)
mjhaston
Forum Newbie
Posts: 8
Joined: Sun Oct 13, 2002 6:51 am

Post 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


mjhaston
Forum Newbie
Posts: 8
Joined: Sun Oct 13, 2002 6:51 am

Post 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 !="" ;
}
Post Reply