CODEWITHSUNDEEP
phpmysqlinsertmysqliwhile-loop
Kristi Simonson
Kristi Simonson

Reputation: 515

MySQL insert loop only inserts one row

I'm doing a one-time import of data (so overhead is not an issue) from one old table into a Wordpress comments table. I loop through the old table, use php to tweak some of the data for the new table, then perform each insert. It'll only insert the first row though. If I run the code again, it'll insert the same first row over, so I don't think there's a primary key issue.

require('wp-config.php');

$con=mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME); 

$result = mysqli_query($con,"SELECT *
    FROM user_import u, wp_posts p, wp_postmeta m
    WHERE p.ID = m.post_id
    AND m.meta_key = 'fanfic_id'
    AND m.meta_value = u.fanfic_id");

while($row = mysqli_fetch_array($result))
  {
    $nick=$row['user_nickname'];
    $ip=$row['user_ip_address'];
    $date=strToTime($row['user_fanfic_date']);
    $date2=strftime('%Y-%m-%d 12:00:00',$date);
    $gmt=strftime('%Y-%m-%d 12:00:00', $date);
    $datemine = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

    $fanfic_id=$row['fanfic_id'];

    $getPostID="SELECT post_id FROM wp_postmeta WHERE meta_key='fanfic_id' and meta_value=$fanfic_id";

    $result2 = mysqli_query($con,$getPostID );

    while($row2 = mysqli_fetch_array($result2)){
      $post_id=$row2['post_id'];

    }

    $review=$row['user_fanfic_review'];
    $sql="INSERT INTO wp_comments(comment_approved, user_id, comment_post_id, comment_author, comment_author_ip, comment_date, comment_date_gmt, comment_content)
      VALUES(1, 0, $post_id,'$nick','$ip', '$date2', '$gmt', '$review') ";

    $result = mysqli_query($con,$sql);

  }

mysqli_close($con);

Upvotes: 0

Views: 2557

Answers (2)

Konsole
Konsole

Reputation: 3475

The problem exists at this point:

$getPostID="SELECT post_id FROM wp_postmeta WHERE meta_key='fanfic_id' and meta_value=$fanfic_id";

$result2 = mysqli_query($con,$getPostID );

while($row2 = mysqli_fetch_array($result2)){
 $post_id=$row2['post_id'];
}

What you are doing is always getting the same post_id inside the loop. That means everytime when the first while loop while($row = mysqli_fetch_array($result)) runs the second while fetches the same data and continue insert operation.

P.S. Consider using $wpdb object for wordpress database operation http://codex.wordpress.org/Class_Reference/wpdb.

Upvotes: 0

invisal
invisal

Reputation: 11181

I believe because of this line

$sql="INSERT INTO wp_comments(comment_approved, user_id, comment_post_id, comment_author, comment_author_ip, comment_date, comment_date_gmt, comment_content)
  VALUES(1, 0, $post_id,'$nick','$ip', '$date2', '$gmt', '$review') ";
$result = mysqli_query($con,$sql); // <--- this line.

Since you reassign the $result of the SELECT to the result of the INSERT. Can you change that line to only this?

 mysqli_query($con,$sql);

Upvotes: 3

Related Questions