Unable to copy table data from one database to another

[wongle]

Hi All,

I am trying to copy one table entry from table a to table b and the script that I have written keeps failing and I'm not sure why.

E.G. - I have John Smith with contacts table ID 223 and want to copy his details from db1 to db2.

It just keeps telling me that there is no connection to the database. The credentials are working fine as they are working on another script.

PDOException is not picking up any issues and vardump does not show anything on the page.

The databases are on the same server.

Code

try {
    $pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $data = json_decode($_POST['array']);
    // $id = isset($_GET['id']) && !empty($_GET['id']) && $_GET['id'] != 'auto' ? $_GET['id'] : NULL;
    // $name       =   $_GET['name'];
    // $last_name  =   $_GET['last_name'];
    // $status     =   'referral';
    $status             =       'referral';
    $learner_id         =       $data->learner_id;



$name             =       $data->learner_id;
$email      =     $data->email;
$phone      =     $data->phone;
$title      =     $data->title;
$created        =     $data->created;
$status     =     $data->status;
$riskdv     =     $data->riskdv;
$nin        =     $data->nin;
$dob        =     $data->dob;
$nextofkin      =     $data->nextofkin;
$address        =     $data->address;
$refname        =     $data->refname;
$refagency      =     $data->refagency;
$refadd     =     $data->refadd;
$refemail       =     $data->refemail;
$refnum     =     $data->refnum;
$riskvoi        =     $data->riskvoi;
$riskar     =     $data->riskar;
$risksat        =     $data-risksat;
$riskpph        =     $data-riskpph;
$riskpmh        =     $data->riskpmh;
$refpos     =     $data->riskpos;
$mentalhealth       =     $data->mentalheath;
$hisdrug        =     $data->hisdrug;
$exoff      =     $data->exoff;
$learndif       =     $data->learndif;
$hisalc     =     $data->hisalc;
$fled       =     $data->fled;
$rousl      =     $data->rousl;
$trave      =     $data->trave;
$yplc       =     $data->yplc;
$psd        =     $data->psd;
$hivaids        =     $data->hivadids;
$hep        =     $data->hep;
$phyneeds       =     $data->phyneeds;
$mentneeds      =     $data->mentneeds;
$meds       =     $data->meds;
$subdose        =     $data->subdose;
$methmil        =     $data->methmil;
$alcdepend      =     $data->alcdepend;
$ivdrug     =     $data->ivdrug;
$onmeth     =     $data->onmeth;
$compdetox      =     $data->compdetox;
$drugdepend     =     $data->drugdepend;
$dip        =     $data->dip;
$attcdp     =     $data->attcdp;
$heroin     =     $data->heroin;
$canna      =     $data->canna;
$meth       =     $data->meth;
$coc        =     $data->coc;
$crack      =     $data->crack;
$solvents       =     $data->solvents;
$amph       =     $data->amph;
$spice      =     $data->spice;
$premed     =     $data->premed;
$halluc     =     $data->halluc;
$subu       =     $data->subu;
$alclevel       =     $data->alchevel;
$otherinfo      =     $data->otherinfo;
$btype      =     $data->btype;
$bamount        =     $data->bamount;
$bstartdate     =     $data->bstartddate;
$bnextpay       =     $data->bnextpay;
$padd1      =     $data->padd1;
$padd2      =     $data->padd2;
$preoff     =     $data->preoff;
$pendcase       =     $data->pendcase;
$precon     =     $data->precon;
$onprob     =     $data->onprob;
$shop       =     $data->shop;
$cook       =     $data->cook;
$money      =     $data->money;
$cleaning       =     $data->cleaning;
$perh       =     $data->perh;
$housing_benefit_claim_date         =     $data->housing_benefit_claim_date;
$housing_benefit_reference      =     $data->housing_benefit_reference;
$housing_benefit_cancelled      =     $data->housing_benefit_cancelled;
$date_left_left     =     $data->date_left_left;
$number_of_times_at_reflections     =     $data->number_of_times_at_reflections;
$amount_of_service_charge_owed      =     $data->amount_of_service_charge_owed;
$reason_for_leaving     =     $data->reason_for_leaving;
$home_town      =     $data->home_town;
$town_moved_to      =     $data->town_moved_to;
$imageSave      =     $data->imagesave;
$datemovedin        =     $data->datemovedin;
$gp_surgery     =     $data->gpsurgery;
$rflfull        =     $data->rflfull;
$last_date_left     =     $data->last_date_left;
$med_cons       =     $data->med_cons;
$addinfo        =     $data->addinfo;
$compldetox     =     $data->compldetox;
$complprog      =     $data->complprog;
$dietaryreq     =     $data->dietaryreq;


    // Update the record

    $stmt = $pdo->prepare('INSERT INTO contacts VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

       $result = $stmt->execute([null,$name,$email,$phone,$title,$created,$status,$riskdv,$nin,$dob,$nextofkin,$address,$refname,$refagency,$refadd,$refemail,$refnum,$riskvoi,$riskar,$risksat,$riskpph,$riskpmh,$refpos, $mentalhealth,$hisdrug,$exoff,$learndif,$hisalc,$fled,$rousl,$trave,$yplc,$psd,$hivaids,$hep,$phyneeds,$mentneeds,$meds,$subdose,$methmil,$alcdepend,$ivdrug,$onmeth,$compdetox,$drugdepend,$dip,$attcdp,$heroin,$canna,$meth,$coc,$crack,$solvents,$amph,$spice,$premed,$halluc,$subu,$alclevel,$otherinfo,$btype,$bamount,$bstartdate,$bnextpay,$padd1,$padd2,$preoff,$pendcase,$precon,$onprob,$shop,$cook,$money,$cleaning,$perh,$housing_benefit_claim_date,$housing_benefit_reference,$housing_benefit_cancelled, $date_left_left, $number_of_times_at_reflections, $amount_of_service_charge_owed, $reason_for_leaving , $home_town, $town_moved_to, $imageSave,$datemovedin,$gp_surgery,$rflfull,$last_date_left,$med_cons,$addinfo,$compldetox,$complprog,$dietaryreq]);

var_dump($name);



     echo "Contact added at Kenobi";
     
} catch (PDOException $exception) {
    // If there is an error with the connection, stop the script and display the error.
    exit('Failed to connect to database!');
}

?>

Many thanks,

Wongle.

[Barand]

I'd take out the try..catch stuff and let php report any errors with a meaningful message.

I can't see the problem off-hand but it does seem evident that the term "data normalization" is foreign to you. That is a spreadsheet, not a relational db table.

[mac_gyver]

you are still wasting time writing out hundreds of lines of code that are copying variables to other variables for nothing. go back and (re)read the replies i wrote about using a data-driven design, what other settings you should use when you make the connection, and about only catching and handling database exceptions for things the visitor to your site can recover from   - https://forums.phpfreaks.com/topic/315008-information-not-updating-to-database-after-deselecting-a-checkbox/

 

[wongle]

I came on here for some help, not to have the **** taken out of me.

This forum was friendly at first.

[mac_gyver]

perhaps if you make use of the information in the replies you have already gotten, so that we are not repeatedly writing the same answers in thread after thread?

the replies forum members have written, after taking the time to read what you are doing, are to help you make your code - more secure, provide a good user experience, be simple, general-purpose, maintainable, and through validation and error handling, will either work or it will tell you - display/log, the reason why it isn't working.

[Psycho]

1 hour ago, wongle said:

I came on here for some help, not to have the **** taken out of me.

This forum was friendly at first.

Understand that the members here provide help out of good will and receive no compensation. When someone takes the time and energy to read through a person's post and provide a response - then the poster ignores the help that was provided it can certainly feel like the person helping was unappreciated or even disrespected. 

You posted 124 lines of code, of which almost 100 lines were unnecessary and add no value (those lines which simply populate object values into distinct variables). In fact, all those lines of code do nothing other than increase chances of bugs. Far too often I've seen people reassign the wrong value to a variable when doing such things. Instead of parsing the $_POST['array'] into an object, have it parsed into an array (look at the other attributes for json_decode). Then simply prepare your query with named placeholder and pass the  array.

Here is a quick rewrite that would accomplish the same thing with only about 25% of the code which is much more readable and maintainable. I may have made a typo or two, so itg may not work out of the box. There is a LOT more I would do to make this code better, but that's all the time I am willing to invest at this time

try {
    $pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //Read data into an array
    $data = json_decode($_POST['array'], TRUE);

    // Update the record
    //Create placeholders using a colon and the index names from the $data array
    //Ideally I would also list out the field names before the values 
    $query = 'INSERT INTO contacts VALUES (null, :learner_id, :email, :phone, :title, :created, :status, :riskdv, :nin, :dob,
                  :nextofkin, :address, :refname, :refagency, :refadd, :refemail, :refnum, :riskvoi, :riskar, :risksat,
                  :riskpph, :rismpmh, :riskpos, :mentalhealth, :hisdrug, :exoff, :learndif, :hisalc, :fled, :rousl, :trave,
                  :yplc, :psd, :hivadids, :hep, :phyneeds, :mentneeds, :meds, :subdose, :methmil, :alcdepend, :ivdrug,
                  :onmeth, :compdetox, :drugdepend, :dip, :attcdp, :heroin, :canna, :meth, :coc, :crack, :solvents, :amph,
                  :spice, :premed, :halluc, :subu, :alchevel, :otherinfo, :btype, :bamount, :bstartddate, :bnextpay,
                  :padd1, :padd2, :preoff, :pendcase, :precon, :onprob, :ship, :cook, :money, :cleaning, :perh,
                  :housing_benefit_claim_date, :housing_benefit_reference, :>housing_benefit_cancelled, :date_left_left,
                  :number_of_times_at_reflections, :amount_of_service_charge_owed, :reason_for_leaving, :home_town,
                  :town_moved_to, :imagesave, :datemovedin, :gpsurgery, :rflfull, :last_date_left, :med_cons, :addinfo,
                  :compldetox, :complprog, :dietaryreq');
    $stmt = $pdo->prepare($query);
    $result = $stmt->execute($data);

    var_dump($data);

     echo "Contact added at Kenobi";
     
} catch (PDOException $exception) {
    // If there is an error with the connection, stop the script and display the error.
    exit('Failed to connect to database!');
}

 

[Barand]

If the two tables have the same structure

REPLACE INTO db2.tablename SELECT * FROM db1.tablename WHERE id = 223;