PHP/MySql - Inserting Multiple Records

[cancer10]

I want to insert records into multiple tables at one go, I am doing this in the following way, is this the correct way of doing? If not, what is the better way?

mysql_query("insert into table1 values('abc')");
mysql_query("insert into table2 values('ttt')");
mysql_query("insert into table3 values('5t6')");
mysql_query("insert into table4 values('ghy')");
mysql_query("insert into table5 values('gfd')");

Please help


Thanx

[hoopyfrood]

Hi cancer10,

what you're doing is indeed one of the many ways of doing it. What you're essentially doing is querying the database each time you're entering the data.

providing you know how many records you'd be entering you could either:

• a) merge it all in one long query
• b) use foreach/while (whichever you prefer) to build the dynamic SQL and query it automatically
• c) does each record goes to defferent table? you could optimise your SQL to group relevant SQL...

If you feel like providing sample of your code I might be able to help you more...

Good luck,
hoopyfrood

[flforlife]

insert multple records in a table at one time!



Hi i'm trying to develop an application where I can add records using one php script and one form. Form that add multiple records to a database. I've been searching but i cant find a solution.

Majors scoreboard

Place Team WL GB %AGE etc....
1 cards 5-0 7 1
2 jays 9-0 7 3
3 yanks 5-0 7 3
4 soxs 9-0 7 2



here is the database setup from ^....

CREATE TABLE major (
id INT NOT NULL AUTO_INCREMENT,
place VARCHAR(65) NOT NULL DEFAULT '',
team VARCHAR(65) NOT NULL DEFAULT '',
wl VARCHAR(65) NOT NULL DEFAULT '',
gb VARCHAR(65) NOT NULL DEFAULT '',
pcage VARCHAR(65) NOT NULL DEFAULT '',
rs VARCHAR(65) NOT NULL DEFAULT '',
ra VARCHAR(65) NOT NULL DEFAULT '',
ags VARCHAR(65) NOT NULL DEFAULT '',
PRIMARY KEY(id)
);




here is the form...


<form method="post" action="add.php">
<h1>Major</h1>
<table border="1">

<tr>
<td>PLACE</td>
<td>TEAM</td>
<td>WIN-LOSE</td>
<td>GB</td>
<td>%AGE</td>
<td>RUNS SCORED</td>
<td>RUNS ALLOWED</td>
<td>AVERAGE GAME SCORE</td>
</tr>

<tr>
<td><input name="place" type="text" id="place" /></td>
<td><input name="team" type="text" id="team" /></td>
<td><input name="wl" type="text" id="wl" /></td>
<td><input name="gb" type="text" id="gb" /></td>
<td><input name="pcage" type="text" id="pcage" /></td>
<td><input name="rs" type="text" id="rs" /></td>
<td><input name="ra" type="text" id="ra" /></td>
<td><input name="ags" type="text" id="ags" /></td>
</tr>

<tr>
<td><input name="place" type="text" id="place" /></td>
<td><input name="team" type="text" id="team" /></td>
<td><input name="wl" type="text" id="wl" /></td>
<td><input name="gb" type="text" id="gb" /></td>
<td><input name="pcage" type="text" id="pcage" /></td>
<td><input name="rs" type="text" id="rs" /></td>
<td><input name="ra" type="text" id="ra" /></td>
<td><input name="ags" type="text" id="ags" /></td>
</tr>


</table>
<input type="submit" />

</form>


I want to add more records using php. here is my add.php code


<?php

//add fourteam data

$hostname = 'localhost';
$username = 'root';
$password = ''; //add later
$database = 'test';

$mysql = mysql_connect($hostname, $username, $password);
mysql_select_db($database);



$sql="INSERT INTO major (place, team, wl, gb, pcage, rs, ra, ags)
VALUES
('$_POST[place]','$_POST[team]','$_POST[wl]','$_POST[gb]','$_POST[pcage]','$_POST[rs]','$_POST[ra]','$_POST[ags]')";

if (!mysql_query($sql,$mysql))
{

die('Error: ' . mysql_error());

}
header("location:ftadmin.php");
echo "1 record added";

mysql_close($mysql)






?>

[hoopyfrood]

See my reply at: insert multple records in a table at one time!

Hope it helps.

Let me know.

hoopyfrood