Loop MySql INSERT query. While or Foreach?

Question

I'm struggling whit this... Basically, I have a script that will run every day (cron job). This script has to retrieve data from a table and it will post the data in another table (same database).

Here is my example script:

<?php

$c = mysql_connect("localhost", "user", "password");
$db = mysql_select_db("mydb", $c);


$query_sel = "SELECT id, rating, rating_count FROM mytableone";
$result_sel = mysql_query($query_sel) or die(mysql_error());
$ids = array();

while($id = mysql_fetch_array($result_sel))

    $ids[] = $row;

foreach($ids as $id){ 

$lid = $row['id'];
$etvalue = $row['user_rating'];
$etcount = $row['rating_count'];

mysql_query("INSERT INTO mytabletwo VALUES ('$lid','$etvalue','$etcount',CURRENT_DATE())");

}
?>

My main idea is to insert data (as mysql query in foreach statement) "for each id from mytableone".

Where is my fault?

Thanks

If you are concerned about multiple INSERT INTO queries, then just add transaction usage. dev.mysql.com/doc/refman/5.0/en/commit.html Plus since you added mysql tag, you should probably read about it here stackoverflow.com/tags/mysql/info — Eugene
– Eugene, Commented Aug 8, 2013 at 22:12

Neil Hampton · Accepted Answer · 2013-08-08 22:21:26Z

1

You can do this with one SQL query

INSERT INTO mytabletwo SELECT id,rating,rating_count,CURRENT_DATE() FROM mytableone

As to the fault you are using $row instead of $id[...]

edited Aug 8, 2013 at 22:21

answered Aug 8, 2013 at 22:07

Neil Hampton

1,88313 silver badges14 bronze badges

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

1 Comment

David Madhatter Over a year ago

I have it, thank you. You're right. This is the simplest way. Many thanks!

Tomas Creemers · Accepted Answer · 2013-08-08 22:06:42Z

1

The easiest way to do this is:

INSERT INTO mytabletwo(idColumnName, ratingColumnName, rating_countColumnName, dateColumnName) SELECT id, rating, rating_count, CURRENT_DATE() FROM mytableone;

(That is one statement.)

answered Aug 8, 2013 at 22:06

Tomas Creemers

2,71518 silver badges15 bronze badges

Collectives™ on Stack Overflow

Loop MySql INSERT query. While or Foreach?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related