PHP Mysqli SELECT, INSERT and UPDATE from different databases

Question

I am trying to select values from one DB. And insert and update the result into another. This is cronjob that needs to run everyday to replicate some data from one DB into another. I know I am missing steps / correct syntax, but I hope someone can help me out.

<?php

    $con_1=mysqli_connect("host","user","pw","db");
    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $con_2=mysqli_connect("host","user","pw","db");
    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $result = mysqli_query($con_1,"SELECT id, name FROM table GROUP BY 1,2");

    $mysqli->query($con_2, "INSERT INTO `table2`(`id`, `name`) VALUES ('".$result[1]."', ".$result[2].") 
    ON DUPLICATE KEY UPDATE name = ".$result[2]."");

    }

    mysqli_close($con_1);
    mysqli_close($con_2);
    ?>

It looks like you are executing SQL but not checking to see if it was successful. Add this in after you get your $result. Check the PHP docs to see what mysqli_query returns in the case of an error (I believe it will be false). — halfer
– halfer, Commented Aug 25, 2014 at 10:11
use try catch with transactions dev.mysql.com/doc/refman/5.0/en/commit.html for more safe — user3209031
– user3209031, Commented Aug 25, 2014 at 10:21

Ende Neu · Accepted Answer · 2014-08-25 10:12:02Z

1

mysqli_query returns a query object, using $result[1] doesn't make sense, you need to fetch the rows in a loop:

while($row = $result->fetch_assoc()) {
  // insert result in second database
}

For other access methods check the documentation.

answered Aug 25, 2014 at 10:12

Ende Neu

15.8k5 gold badges61 silver badges72 bronze badges

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

2 Comments

Ende Neu Over a year ago

@JurienGroot it's hard to read form the comment, you probably should ask another question and better explain your problem.

Jurien Groot Over a year ago

yes, something went wrong. Sorry about that. I tried to make mysql_affected_rows to work, but didn't succeed. Your answer to the current question worked well though. Thanks

Collectives™ on Stack Overflow

PHP Mysqli SELECT, INSERT and UPDATE from different databases

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related