1st record inserted x times, 2nd x-1, 3rd x-2 etcetera!

timmm

Hi,

There is a record (id) selected from TABLE1 where 'code' is for
example '1'.
The id is used to delete a record (with that id) from TABLE2(!).
After this the record with that id from TABLE1 is inserted (again)
into TABLE2.

Using update isn't an option because id can't be a primairy key. (two
records can have the same id)

The problem occures while browsing through TABLE1 there are for
example 5 records with the 'code' = '1'. When executing the script the
first result (id=1) in TABLE 1 will be inserted 5 times in TABLE2 at
the end. The second result (id=2) will be inserted 4 times, the third
(id=3) will be inserted 3 times, the fourth (id=4) 2 times, and the
last (id=5) 1 time.

According to this it's easy to conclude it's looping until he cant
find any records with a 'code' = '1' (that's the last record (id=5)

Does some body know a solution for this problem? I need only to
(re)insert the records once 🙂!

Thanks a lot in @ for your help!

Tim.

The code:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<? php
if ($submit) {
$db = @mysql_connect("localhost","db","password") or
Die(mysql_error());
@mysql_select_db("db",$db) or Die("Error: " . mysql_error());

echo "Connection made, counting records ";

// record count

$sqlrows = ("SELECT Count(*) FROM table1 WHERE Code = '1' OR Code2 =
'1'");

$resultrows = @($sqlrows,$db);
$rows = mysql_fetch_row($resultrows);
echo "The are ".$rows[0]." records total ";

// selecting from table1

$sql = ("SELECT Id FROM table1 WHERE Code = '1' OR Code2 = '1'");

$result = @($sql,$db) or Die(mysql_error());
while(list($Id) = @mysql_fetch_row($result)) {

print "Record with Id = $Id selected from table1 ";

// deleted from table2

$sql2 = ("DELETE FROM table2 WHERE Id=".$Id."");
@($sql2,$db) or Die(mysql_error());
print "Record with Id = $Id deleted from tabel2 ";

// insert again in table2

$sql3 = ("INSERT INTO table2 (field1, field2, field3 ) SELECT field1,
field2, field3 FROM table1 WHERE Code = '1' OR Code2 = '1'");

@($sql3,$db) or Die(mysql_error());
mysql_close($db);

print "Record with Id = $Id inserted again in table2 ";
}
?>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

timmm

does nobody know an answer?

bastien

which is what you have here and it is written incorrectly anyway

$sql3 = ("INSERT INTO table2 (field1, field2, field3 ) SELECT field1,
field2, field3 FROM table1 WHERE Code = '1' OR Code2 = '1'");

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

i don't knowif this is the whole problem but it is a part of it

also

$sql = ("SELECT Id FROM table1 WHERE Code = '1' OR Code2 = '1'");

should probably use the distinct clause to reduce the number of records selected ( select distinct(id) from...)

one trick here is to show all the results from the select first to see if the data duplicates itself at all and then refine the sql to get just one record for every ID and then go from there....

to get around the subselect try this, select all the data needed in the first select where you currently get the ID

$sql="select distinct(ID), Field1, field2, field3 from table1 where code='1' or code2='1'";
then you have all the data for the insert into table 2 if you assign all the values

$result = mysql_query($sql) or die("Db not available");
while ($row=mysql_fetch_array($result)){
$id = $row['ID'];
$field1=$row['field1'];
$field2=$row['field2'];
$field3=$row['field3'];

//run delete query for $id

$sql2="delete from table2 where id = $id";

//run insert query for $id
$sql4="insert into table2 values('$field1', '$field2', '$field3');

}

use the mysql_fetch_array as well to pull all the needed records out and hold them, uses only one DB call can you then iterate through the records to delete and re-insert into table2

hth

timmm

Thnx a lot bastian i'm going to try your example! I post te resulkt in this forum thnx again!

Timmm.