1

I want to update remote database from local database, I have written php script to do that but it is showing me fatal error.

Remote database and local database has same name, same table, same fields.

I have tried this way but its not working.

    $tablename="pc_games";
    $database = 'games';

    $local_query = "SELECT * FROM $tablename LIMIT 100 OFFSET $remoterows";
    $local_result = mysql_query($local_query, $connection) or trigger_error("SQL", E_USER_ERROR);

    while($list=mysql_fetch_array($local_result))
    {
    $remote_update=mysql_query("INSERT INTO $tablename SETLECT * from $tablename");
    $remote_update_result = mysql_query($remote_update, $remote_connection) or trigger_error("SQL", E_USER_ERROR);
    }

Please see and suggest any possible approach to do this.

Improve this question
7
  • Can you show us the error? Also, are you sure the remote connections are defined properly? Commented Sep 16, 2012 at 6:58
  • Fatal error: SQL, thats it. yes connection are proper, no problem there. Commented Sep 16, 2012 at 6:58
  • 1
    Try this for your error handler: ` or die (mysql_error());`, then tell us the result. Commented Sep 16, 2012 at 7:00
  • Change trigger_error("SQL", E_USER_ERROR); to die( mysql_error() ); Commented Sep 16, 2012 at 7:00
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SETLECT * from pc_games' at line 1 Commented Sep 16, 2012 at 7:01

4 Answers 4

Reset to default
1 
"INSERT INTO $tablename SETLECT * from $tablename"

This is an invalid SQL statement. SETLECT have to be SELECT

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

2 Comments

my bad i typed it wrong, it is now showing Duplicate entry '1' for key 'PRIMARY'
You have a unique constraint and you are trying to insert the same record more than once which violates the unique constraint. Either remove the existing row or insert a new one with different value for primary key.
1

Your remote query has a syntax error: "INSERT INTO $tablename SETLECT * from $tablename". You mean SELECT instead of SETLECT.

Improve this answer

Comments

0

Replacement code:

$tablename="pc_games";
$database = 'games';

$local_query = "SELECT * FROM $tablename LIMIT 100 OFFSET $remoterows";
$local_result = mysql_query($local_query, $connection) or die(mysql_error());

while($list=mysql_fetch_array($local_result))
{
$remote_update=mysql_query("INSERT INTO $tablename SELECT * from $tablename");
$remote_update_result = mysql_query($remote_update, $remote_connection) or die (mysql_error());
}

What you did wrong, was you mistyped this line:

$remote_update=mysql_query("INSERT INTO $tablename SETLECT * from $tablename");

Notice that you used SETLECT, instead of what you meant to use (perhaps): SELECT.

I also marvel at how long it takes to detect a typing error.

I changed all your error handlers to the ones I mentioned in the comments. Use them in the future. They're better.

UPDATE: What did you do with your code? I just realised something drastic:

$remote_update=mysql_query("INSERT INTO $tablename SELECT * from $tablename");
$remote_update_result = mysql_query($remote_update, $remote_connection) or die (mysql_error());

First of all, you're running mysql_query on a result. Second, you aren't inserting anything INSERT INTO $tablename. Is it my fault, or are you doing something dreadfully wrong?

Improve this answer

3 Comments

yes you are right, there is no error showing now, and nothing gets inserted in remote database, what should i do.
@TallboY so what did you change your insert code to? May I take a look?
I have asked another question, please see it, stackoverflow.com/questions/12445909/…
0

  1. Why are you looping through the results(with while($list=mysql_fetch_array($local_result)) ) when you absolutely don't need it?

  2. Why have you set the error function everywhere as: trigger_error("SQL", E_USER_ERROR);.

  3. Why are you still using mysql_* method for databases?

  4. If you just need to insert data from another table, just put:

mysql_query("INSERT INTO $tablename SELECT * FROM $tablename LIMIT 100 OFFSET $remoterows") or die(mysql_error());

and be done with it.

Improve this answer

3 Comments

I want to update remote database from local database when new rows are added, sorry i didn't mentioned that.
it shows Duplicate entry '34' for key 'PRIMARY' with this method, i have only 33 rows in remote database and i just inserted a new row in my local database, then why this error
Can't say for sure. Is your $remoterows set to 33?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.