Error message copying data from one database to another on the same server

[EarthDay]

Hi there,

I have wrote a script to copy client data over from our old system to the new system and it's giving me the above error message.

I know the script is not very secure but it's located on the internal network and is not open to the internet via firewall rules. I am monitoring use of the script very closely (When I get it working lol)

Full error message

Quote

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from cic_remus.contacts where (id='127')' at line 1 in sender.php:16 Stack trace: #0 sender.php(16): mysqli_query() #1 {main} thrown in sender.php on line 16

I have gone through the code and everything appears to be correct and can't seem to see what the problem is.

Both tables are the same and using the same version of PHP 8.2 and are located on the same server.

I know that I can just export and import but we are manually reviewing what information goes over and this is the easiest way for us to do it.

Code;

<?php

$databaseHost = 'localhost'; 
$databaseName = ''; 
$databaseUsername = ''; 
$databasePassword = '';

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 
 
$id = $_GET['id'];



$sql="select from cic_remus.contacts where (id='$id');"; 

      $res=mysqli_query($mysqli,$sql);

      if (mysqli_num_rows($res) > 0) {
        // output data of each row
        $row = mysqli_fetch_assoc($res);
        if($id==$row['id'])
        {
echo "Already copied"; 
                
        }

       } else{

 
   
$query=mysqli_query($mysqli,"INSERT INTO cic_kenobi.contacts (status, image, datemovedin, learner_id, title, name, last_name, sex, dob, house_number, address_line_one, address_line_two, city_town, country, postcode, postcode_enrolment, phone, mobile_number, email_address, ethnic_origin, ethnicitys, health_problem, health_disability_problem_if_yes, lldd, education_health_care_plan, health_problem_start_date, education_entry, emergency_contact_details, employment_paid_status, employment_date, unemployed_month, education_training_prior, education_claiming, claiming_if_yes, household_situation, mentor, intext)

SELECT status, image, datemovedin, learner_id, title, name, last_name, sex, dob, house_number, address_line_one, address_line_two, city_town, country, postcode, postcode_enrolment, phone, mobile_number, email_address, ethnic_origin, ethnicitys, health_problem, health_disability_problem_if_yes, lldd, education_health_care_plan, health_problem_start_date, education_entry, emergency_contact_details, employment_paid_status, employment_date, unemployed_month, education_training_prior, education_claiming, claiming_if_yes, household_situation, mentor, intext

FROM cic_remus.contacts WHERE id =$id");

echo "Successfully copied"; 


       }
       
?>

If I change the below code to

$sql="select from cic_remus.contacts where (id='$id');"; 

to

$sql="select `id` from cic_remus.contacts where (id='$id');"; 

It gives me the error message that the contact already exists with the same ID and does not create a new ID.

Cheers, ED.

Edited March 10, 2023 by EarthDay

[kicken]

4 minutes ago, EarthDay said:

$sql="select from cic_remus.contacts where (id='$id');";

You did not specify any columns to select.

[EarthDay]

Just now, kicken said:

You did not specify any columns to select.

Hi Kicken,

Thank you for your reply, I have just edited my post

Cheers, ED.

[kicken]

If you're inserting the data into cic_kenobi.contacts, why are you checking if it already exists by selecting from cic_remus.contacts?

 

 

[EarthDay]

4 minutes ago, kicken said:

If you're inserting the data into cic_kenobi.contacts, why are you checking if it already exists by selecting from cic_remus.contacts?

 

 

So if the ID exists in the new database, it does not get overwritten.

[kicken]

Doesn't make any sense to check the source table though.

Your copying from cic_remus.contacts into cic_kenobi.contacts.  So of course the data already exists in cic_remus.contacts, if it didn't there wouldn't be anything to copy in the first place.

[EarthDay]

49 minutes ago, kicken said:

Doesn't make any sense to check the source table though.

Your copying from cic_remus.contacts into cic_kenobi.contacts.  So of course the data already exists in cic_remus.contacts, if it didn't there wouldn't be anything to copy in the first place.

It checks to see if the ID exists in in cic_kenobi.

Edited March 10, 2023 by EarthDay

[kicken]

14 minutes ago, EarthDay said:

It checks to see if the ID exists in in cic_remus.

I know that, but your logic doesn't make sense.  This is the logic you've implemented:

$id = $_GET['id'];
if ($id exists in cic_remus.contacts) {
    echo "Already copied"; 
}
else if ($id does not exist in cic_remus.contacts){
    copy data for $id from cic_remus.contacts into cic_kenobi.contacts
}

You're trying to copy data that doesn't exist into another table.  Copying nothing will give you nothing.

[gizmola]

You are clearly new to doing queries with the php mysqli extension.

First of all, why are you including extraneous parens and punctuation in your query?

 

What you are doing:

$sql= "select * from cic_remus.contacts where (id='$id');"; 

What it should be:

$sql = "select * from cic_remus.contacts where id=$id"; 

Your problem is likely a logic issue as Kicken has pointed out, but you should also address the underlying issue for debugging purposes:

Quote

Fatal error: Uncaught mysqli_sql_exception:

This is telling you that you have an uncaught exception, so try surround the code with a try..catch block for that and then display the actual exception message and query next time you do this.

try{
   // sql query code
}
catch(Exception $e)
{
  echo "Error: " . $e->getMessage();
}

 

I'm not sure why you are doing what you are doing, when instead you can just do a query:

INSERT INTO cic_kenobi.contacts AS select * from cic_remus.contacts ON DUPLICATE KEY IGNORE

If the tables don't exactly match (you can craft the individual values statement in the same way you already have been).  You can run this from PHP but unless you are doing this frequently, having it scripted within php doesn't have a lot of value to it.