CODEWITHSUNDEEP
mysqlperl
Thor_that_new_in_programming
Thor_that_new_in_programming

Reputation: 193

How to retrieve multiple values from database and set it as variable

Inside table(1)

ID      Name      Address        Phone       InsertDate

1      Andrew     12-A,ABC      576849203    2014/05/06
1      Andrew     12-A,ABC      123456789    2014/07/08

Inside table(2)

ID      Name      Address        Phone       InsertDate

1      Andrew     12-A,ABC       123456789    2014/07/08

The problem I met is when I Select * from table(1) where id='1'. It will retrieve two records. Then when I try to insert to table(2). It only show one record in table(2) instead of 2 records.

What should I do so I can get the two records from table(1) and insert to table(2)?

My example Code:

$mysql="Select * from table(1) where id='1'";
**Perform the query**

**Retrieve values**
$sth->bind_col(1, \$Name);
$sth->bind_col(2, \$Address);
$sth->bind_col(3, \$Phone);
$sth->bind_col(4, \$InsertDate);

if($sth->fetch()){
  $mysql="Insert into table(2) values($Name,$Address,$Phone,InsertDate)";
  **Perform the query**
}

Thanks for viewing,comments and answers!

Upvotes: 0

Views: 705

Answers (2)

Borodin
Borodin

Reputation: 126722

You are doing SELECT * FROM table(1) so you are fetching all of the columns. The first is ID so your column binding is incorrect, and it is hard to believe that your code copies even one record correctly. In addition, you are executing fetch only once, so you are only ever trying to insert one of the two rows into table(2).

You need to write something like this

my $read = $dbh->prepare(<<'__END_SQL__');
SELECT Name, Address, Phone, InsertDate
FROM table(1)
WHERE ID = ?
__END_SQL__

my $write = $dbh->prepare(<<'__END_SQL__');
INSERT INTO table(2) (Name, Address, Phone, InsertDate)
VALUES (?, ?, ?, ?)
__END_SQL__

$read->execute(1);

while (my $row = $read->fetchrow_arrayref) {
  $write->execute(@$row);
}

But, unless there is a reason why you want access at the Perl level to the records being copied, you could let the database do all of it for you, like this

my copy = $dbh->prepare(<<'__END_SQL__');
INSERT INTO table(2) (Name, Address, Phone, InsertDate)
  SELECT Name, Address, Phone, InsertDate
  FROM table(1)
  WHERE ID = ?
__END_SQL__

$copy->execute(1);

Upvotes: 2

Aamir
Aamir

Reputation: 326

first of all make sure in table2 , id or name or some other field is not set to primary or it will not allow u insert duplicate records ..

try this PHP code...

result=mysql_query(" select * from table where id=1 ");

while($row=mysql_fetch_assoc($result))
{
$name=$row["Name"];
$address==$row["address"];
$phone==$row["phone"];
$insertdate=$row["insertdate"];

mysql_query( "insert into table2(name,address,phone,insertdate) values('$name','$address','$phone','$insertdate') ");

}

Upvotes: -1

Related Questions