CODEWITHSUNDEEP
phpmysqlforeachwhile-loop
David Madhatter
David Madhatter

Reputation: 285

Loop MySql INSERT query. While or Foreach?

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

Upvotes: 0

Views: 2688

Answers (2)

Neil Hampton
Neil Hampton

Reputation: 1883

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[...]

Upvotes: 1

Tomas Creemers
Tomas Creemers

Reputation: 2715

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

Upvotes: 1

Related Questions