Page 1 of 1

Multi insert

Posted: Fri Oct 03, 2008 7:45 am
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 .

Re: Multi insert

Posted: Fri Oct 03, 2008 8:17 am
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.

Re: Multi insert

Posted: Fri Oct 03, 2008 8:30 am
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';
 

Re: Multi insert

Posted: Fri Oct 03, 2008 8:45 am
by deejay
doh!
thanks Kandanis, that helps me with a bit of code I'm writing at the moment. cheers

Re: Multi insert

Posted: Fri Oct 03, 2008 10:24 am
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','','')";
}

Re: Multi insert

Posted: Fri Oct 03, 2008 10:34 am
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

Re: Multi insert

Posted: Fri Oct 03, 2008 11:15 am
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";
}



?>