Multi insert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Arsench2000
Forum Commoner
Posts: 38
Joined: Sat Sep 15, 2007 8:18 pm

Multi insert

Post by Arsench2000 »

Hello,
How can I insert from table1 to table2 the same fields but only where are one condition?

for exampl. inser into table2.id, tb2.name from table1 where the user=$user .
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: Multi insert

Post by deejay »

do you mean something like this.

Code: Select all

 
 
$query = "SELECT * FROM tbl1 WHERE field=$cond ";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)){
 mysql_query ("INSERT INTO tbl1(field1, field2) VALUES ('$field1', '$field2',.. '' )"); 
}
 

if not I don't think I quite understand what your getting at. Hope this helps.
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Re: Multi insert

Post by Kadanis »

You should be able to do it straight from SQL with out the loop

Code: Select all

 
INSERT INTO `table2` SELECT * FROM `table1` WHERE  `table1`.`field` = 'cond';
 
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: Multi insert

Post by deejay »

doh!
thanks Kandanis, that helps me with a bit of code I'm writing at the moment. cheers
Arsench2000
Forum Commoner
Posts: 38
Joined: Sat Sep 15, 2007 8:18 pm

Re: Multi insert

Post by Arsench2000 »

Kadanis wrote:You should be able to do it straight from SQL with out the loop

Code: Select all

 
INSERT INTO `table2` SELECT * FROM `table1` WHERE  `table1`.`field` = 'cond';
 
thank you for your replay friend, Im doing like this but the records can show correctly, but doesnt insert into table t
here is the code

$q=mysql_query("SELECT p_id,p_name FROM p WHERE p_name <> ''") or die(mysql_error());
while($r=mysql_fetch_array($q))
{
$p_id=$r["p_id"];
$p_name=$r["p_name"];

echo"$p_id ";
echo"$p_name";
echo"<br>";

$ins="INSERT INTO t VALUES('$p_id','$p_name','','')";
}
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Re: Multi insert

Post by Kadanis »

Based on the code example you provided I would say it isn't inserting because you haven't called a database function (unless some code is missing) all you are doing is assigning the SQL statement string to a variable $ins

For your code to insert data into the database you need to add

Code: Select all

 
mysql_query($ins);
 
Alternatively, following on from my previous reply you can do the whole thing in 2 SQL statements (less connections to the database) by calling

Code: Select all

 
//creates a recordset of the items meeting the condition 
$sqlOutput = "SELECT p_id, p_name FROM p WHERE p.p_name <> ''";
$rstOutput = mysql_query($sqlOutput);
 
//iterate over recordset and build output string
while ($row = mysql_fetch_assoc($rstOutput)){
    $output = sprintf("ID: %s  |  Name: %s  <br />", 
    $row['p_id'], 
    $row['p_name']);
}
//output to page
echo $output; 
 
//copy the items to the new table
$sqlMove = "INSERT INTO t " . $sqlOutput
mysql_query($sqlMove);
 
In effect this would copy all the entries from table p into table t where the value of p_name in table p is not empty.

If you want to list them out to the page as well then the looping is still required, but you could do that either before or after the insert statement. With your code as it is you will be making a call to the database for every single record in your recordset which, if it grows, could become pretty intensive
Arsench2000
Forum Commoner
Posts: 38
Joined: Sat Sep 15, 2007 8:18 pm

Re: Multi insert

Post by Arsench2000 »

Kadanis wrote:Based on the code example you provided I would say it isn't inserting because you haven't called a database function (unless some code is missing) all you are doing is assigning the SQL statement string to a variable $ins

For your code to insert data into the database you need to add

Code: Select all

 
mysql_query($ins);
 
Alternatively, following on from my previous reply you can do the whole thing in 2 SQL statements (less connections to the database) by calling

Code: Select all

 
//creates a recordset of the items meeting the condition 
$sqlOutput = "SELECT p_id, p_name FROM p WHERE p.p_name <> ''";
$rstOutput = mysql_query($sqlOutput);
 
//iterate over recordset and build output string
while ($row = mysql_fetch_assoc($rstOutput)){
    $output = sprintf("ID: %s  |  Name: %s  <br />", 
    $row['p_id'], 
    $row['p_name']);
}
//output to page
echo $output; 
 
//copy the items to the new table
$sqlMove = "INSERT INTO t " . $sqlOutput
mysql_query($sqlMove);
 
In effect this would copy all the entries from table p into table t where the value of p_name in table p is not empty.

If you want to list them out to the page as well then the looping is still required, but you could do that either before or after the insert statement. With your code as it is you will be making a call to the database for every single record in your recordset which, if it grows, could become pretty intensive
thank you very much, Ive resolved here is the resolved code

<?php
include'db.php';

$q=mysql_query("SELECT p_id,p_name FROM p WHERE p_name <> ''") or die(mysql_error());
while($r=mysql_fetch_array($q))
{
$p_id=$r["p_id"];
$p_name=$r["p_name"];

echo"$p_id ";
echo"$p_name";
echo"<br>";

$ins="INSERT INTO t VALUES('','$p_id','$p_name','','')";
}
if(!mysql_query($ins))
{
die(' Error: ' .mysql_error());
}
else
{
echo"Los datos ".$p_id. "y";
echo"".$p_name. "han insertado correctamente";
}



?>
Post Reply