Multi insert
Moderator: General Moderators
-
Arsench2000
- Forum Commoner
- Posts: 38
- Joined: Sat Sep 15, 2007 8:18 pm
Multi insert
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 .
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 .
Re: Multi insert
do you mean something like this.
if not I don't think I quite understand what your getting at. Hope this helps.
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.
Re: Multi insert
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';
Re: Multi insert
doh!
thanks Kandanis, that helps me with a bit of code I'm writing at the moment. cheers
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
thank you for your replay friend, Im doing like this but the records can show correctly, but doesnt insert into table tKadanis 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';
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','','')";
}
Re: Multi insert
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
Alternatively, following on from my previous reply you can do the whole thing in 2 SQL statements (less connections to the database) by calling
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
For your code to insert data into the database you need to add
Code: Select all
mysql_query($ins);
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);
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
thank you very much, Ive resolved here is the resolved codeKadanis 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
Alternatively, following on from my previous reply you can do the whole thing in 2 SQL statements (less connections to the database) by callingCode: Select all
mysql_query($ins);
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.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);
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
<?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";
}
?>