Jump to content

Unable to copy table data from one database to another


wongle

Recommended Posts

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.

Link to comment
Share on other sites

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/

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!');
}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.