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

Question

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!

if($sth->fetch()) => while($sth->fetch()), and use placeholders to fetch and insert. perlmonks.org/?node_id=284436 — mpapec
– mpapec, Commented Jul 2, 2014 at 11:00

Borodin · Accepted Answer · 2014-07-02 11:21:44Z

2

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);

edited Jul 2, 2014 at 11:21

answered Jul 2, 2014 at 11:15

Borodin

127k9 gold badges72 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Miller Over a year ago

I like your heredoc style for SQL statements. I realize it's close to what's recommended by Conway, but I'm bookmarking for future reference anyway. +1

Aamir · Accepted Answer · 2014-07-02 11:05:14Z

-1

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') ");

}

answered Jul 2, 2014 at 11:05

Aamir

3262 silver badges8 bronze badges

2 Comments

Borodin Over a year ago

But ... this is a Perl question

Aamir Over a year ago

thanks, i actually thought the problem is with the primary keys gave php for a reference but agree dat its a mistake ....

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related