FRIHOSTFORUMSSEARCHFAQTOSBLOGSCOMPETITIONS
You are invited to Log in or Register a free Frihost Account!


transfering mysql values form one table to another with php





mikelll
I need your help folks!

I need to make a php script that transfers some values from table A to table B while there is more then 10 regists in the table A.

In other words, when it detects that table A have more than 10 values in it, it passes that extra values to table B, deleting them form table A.

Thanks in advance
Peterssidan
Do you really have to use two different tables for this? If those in A are the 10 most recent you can just order by a timestamp or an id, and use LIMIT 10 to get these 10 most recent.
jmraker
This will select the 10 newest records assuming that table1_primaryKey is a field that's autoincrement and records are added where table1_primaryKey=0 or null so it uses the next number.

SELECT * FROM table1 ORDER BY table1_primaryKey DESC LIMIT 10
macky
you can use the INSERT SELECT...

you may first consider to check how many rows you have then do this just to make sure that least 10 will remain and others will be deleted.. if you only want to fetch the recent 10 rows then this approach would be useless..

the syntax would be

INSERT INTO tb1
SELECT * FROM tb2
WHERE id = '1'
LIMIT 0,10
mikelll
ok, but in PHP how do I put that?

thanks
D'Artagnan
Quote:
ok, but in PHP how do I put that?

thanks


if you really want to pass it throught PHP it can go somewhat like this, assuming you use mysql and i understood exacly what you posted (you can send me a pm in portuguese if you want...).

you'll have to test i didn't test this at all

Code:

<?php
//Connection here...
$q = mysql_query('SELECT id,campo1,campo2 FROM tb1 order by id);

if(mysql_num_rows($q) => 10){
   $i=0;
   while($a = mysql_fetch_array($q)){
       $i++;
       if($i=<10) continue;
       
       mysql_query("insert into tb2(id,campo1,campo2) values ( {$a['id']},{$a['campo1']},{$a['campo2']})" );
       mysql_query('delete from tb1 where  id = {$a['id']}")

   }
}


?>

Although this solution is VERY ROUGHT , there's probably a way solve your problem much more efficently
mikelll
D'Artagnan wrote:
Quote:
ok, but in PHP how do I put that?

thanks


if you really want to pass it throught PHP it can go somewhat like this, assuming you use mysql and i understood exacly what you posted (you can send me a pm in portuguese if you want...).

you'll have to test i didn't test this at all

Code:

<?php
//Connection here...
$q = mysql_query('SELECT id,campo1,campo2 FROM tb1 order by id);

if(mysql_num_rows($q) => 10){
   $i=0;
   while($a = mysql_fetch_array($q)){
       $i++;
       if($i=<10) continue;
       
       mysql_query("insert into tb2(id,campo1,campo2) values ( {$a['id']},{$a['campo1']},{$a['campo2']})" );
       mysql_query('delete from tb1 where  id = {$a['id']}")

   }
}


?>

Although this solution is VERY ROUGHT , there's probably a way solve your problem much more efficently


YIUUUPIII
is this that I need!
mikelll
your code didn't work... Sad

I did this:

Code:
<?php

$ligacao = mysql_connect("localhost", "root", "");
mysql_select_db("estuda",$ligacao);

$query = mysql_query("SELECT * FROM tabela");
$tabela = mysql_fetch_array($query);
while(mysql_num_rows($query) > 10){
    mysql_query("INSERT INTO tabela2(id, nome, morada) values({$tabela['id']},{$tabela['nome']},{$tabela['morada']}");
    mysql_query("DELETE FROM tabela (id, nome, morada) WHERE id = {$query['id']}");
    echo "o valor: {$query['id']} foi movido com sucesso!";
    }


But I get a: Fatal error: Maximum execution time of 30 seconds exceeded in on this line:

Code:
mysql_query("DELETE FROM tabela (id, nome, morada) WHERE id = {$query['id']}");
Peterssidan
It has been a while since I wrote anything in php/mysql so I might be wrong but your loop looks dangerous. To me it looks like $query['id'] is the same throughout the loop? and all in $query and $tabela also stay the same. mysql_num_rows($query) will return the number of rows in tabela2 so the loop will never end. That mean you are inserting the same element many many times. Hopefully the id is the key so there will not be more than one element inserted but if not your database will contain lots of rows.

I think you better use mysql_fetch_row inside the while condition and insert into tabela2, and after the loop when you are sure everything has been copied successfully you delete all rows in tabela in one query.
Fire Boar
Peterssidan is right. Try instead,

Code:
<?php

$ligacao = mysql_connect("localhost", "root", "");
mysql_select_db("estuda",$ligacao);

$query = mysql_query("SELECT id, nome, morada FROM tabela");
$rows = mysql_num_rows($query);
while($rows-- > 10) {
  $row = mysql_fetch_array($query);
  mysql_query("INSERT INTO tabela2(id, nome, morada) values({$row['id']},{$row['nome']},{$row['morada']}");
  mysql_query("DELETE FROM tabela (id, nome, morada) WHERE id = {$row['id']}");
  echo "o valor: {$row['id']} foi movido com sucesso!";
}
mikelll
Hi there, I solved it! Here is the correct code:

Code:
<?php
$ligacao = mysql_connect("localhost","root","");
    if (!$ligacao){
        die("Falhou a ligação à base de dados: ".mysql_error());
    }
$escolhe_bd = mysql_select_db("estuda",$ligacao);
    if (!$escolhe_bd){
        die("falhou a escolha da BD: ".mysql_error());
    }
$query1 = "SELECT id FROM tabela";
$resultado1 = mysql_query($query1);
    if (mysql_num_rows($resultado1) >10){
        $diferenca = mysql_num_rows($resultado1) - 10;
        $query2 = "SELECT * FROM tabela ORDER BY id LIMIT 10, $diferenca";
        $resultado2 = mysql_query($query2);
   
    while ($linha = mysql_fetch_array($resultado2)){
        mysql_query("INSERT INTO tabela2(id,nome2,morada2)VALUES('{$linha['id']}','{$linha['nome']}','{$linha['morada']}')");
        mysql_query("DELETE FROM tabela WHERE id={$linha['id']}");
        echo "O valor relativo a {$linha['nome']} foi apagado <br/>";
    }
 }
 mysql_close($ligacao);

?>
Fire Boar
I'd suggest SELECT COUNT(1) rather than SELECT id and then counting the results. The COUNT query is more efficient. It returns a single row with a single element containing the number of rows selected according to the WHERE clause.
mikelll
Fire Boar wrote:
I'd suggest SELECT COUNT(1) rather than SELECT id and then counting the results. The COUNT query is more efficient. It returns a single row with a single element containing the number of rows selected according to the WHERE clause.

Yes! You are right!
Related topics
form mail for php
[PhP] News Posting Tutorial (code, actually ^^')
A Simple Form Mail in PHP
Inserting data to MYSQL with a PHP script
Separating contents of MySQL row and put them in variables?
A very good PHP MySQL Tutorial
Database Privilege
150 frih to make this SIMPLE script
[man]Form Mail en PHP
Need Expert help in php-mysql
cannot add data to mysql from PHP form
Online Database programme
PHP and Javascript Issue handling data from a mysql query
Form handling in php is a pain in the neck
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.