mysql import fara duplicate

Buna ziua
As avea nevoie de un pic de ajutor cu o chestie care ma doboara
am asa o tabela si un xml care vreau sa il import dar vreau sa evit duplicatele .
tabela arata asa
CREATE TABLE portare ( datetime timestamp, messagetype varchar(255), idnumber varchar(255), donorid varchar(255), recipientid varchar(255), numberfrom varchar(255), numberto varchar(255), routinginfo varchar(255), caseclass varchar(255) );

si xml-ul asa

<sync:synchronizationinfo xmlns:sync="http://xxxxxxxx">
  <period>
    <startdate>2015-02-01T00:00:00</startdate>
    <enddate>2015-02-28T23:59:59</enddate>
  </period>
  <synchronization>
    <datetime>2015-02-05T12:00:16</datetime>
    <messagetype>npbroadcast</messagetype>
    <idnumber>xxxxxxxxxxx</idnumber>
    <donorid>RTLC</donorid>
    <recipientid>ORNG</recipientid>
    <numberfrom>xxxxxxxx</numberfrom>
    <numberto>xxxxxxxx</numberto>
    <routinginfo>18742</routinginfo>
    <caseclass>fixed</caseclass>
  </synchronization>
  <synchronization>
    <datetime>2015-02-05T12:00:16</datetime>
    <messagetype>npbroadcast</messagetype>
    <idnumber>xxxxxxxxxxxxxxxxxxx</idnumber>
    <donorid>RTLC</donorid>
    <recipientid>ORNG</recipientid>
    <numberfrom>xxxxxxxxxxxxxxxx</numberfrom>
    <numberto>xxxxxxxxxxxxxxxx</numberto>
    <routinginfo>18742</routinginfo>
    <caseclass>fixed</caseclass>
  </synchronization>


as vrea ca la import sa verifice in baza de date daca mai exista <numberto>xxxxxxxxxxxxxxxx</numberto> iar daca exista sa faca update si sa inlocuiasca <routinginfo>18742</routinginfo> cu noile informatii din acel xml

de importat pot importa asa LOAD XML LOCAL INFILE '/home/sync2011-04.xml' INTO TABLE portare ROWS IDENTIFIED BY '<synchronization>';

mai exact vreau sa se faca update ceea ce nu pot cu comanda load xml si diferenta este ca numberto si routinginfo trebuie sa existe numai o singura data in acesta tabela dar in xml-uri exista la un moment dat numberto dar cu un alt routinginfo si acolo trebuie sa fac update

imi cer scuze daca nu m-am explicat prea bine cunoscatorii stiu despre ce vorbesc. un sfat o idee m-ar ajuta mult

pentru mai multe detalii vreau sa implementez portabilitatea pe unul din serverele mele si am si acces la bdc

Eventual poti importa initial intr-o tabela temporara datele ca in XML si le prelucrezi dupa (INSERT ... SELECT DISTINCT, etc). Exista si INSERT ... ON DUPLICATE KEY UPDATE ... si setezi campul ala care nu are voie sa aiba duplicate ca unic.

asa m-am gandit si eu cu o tabela temporara numai ca chestia asta trebuie automatizata

cu php e simplu ce vrei să faci.
încarci feed-ul cu simplexml_load_file ce îl parcurgi apoi cu un foreach
coloana numberto o declari ca UNIQUE KEY și salvezi datele în baza de date cu INSERT ... ON DUPLICATE KEY UPDATE ...

ma ajuti cu un script in php te rog ca habar nu am php

si xml-ul se actualizeaza zilnic http://x.x.x.x/sync/syncfullday.csv

Edited by arsenieciprian, 25 March 2015 - 10:54.

<?php
$xmlDoc = new DOMDocument();
$xmlDoc->load("/home/sync2011-04.xml");
$mysql_hostname = "localhost";
$mysql_user = "test";
$mysql_password = "test";
$mysql_database = "test";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Oops some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Oops some thing went wrong");
$xmlObject = $xmlDoc->getElementsByTagName('synchronization');
$itemCount = $xmlObject->length;
for ($i=0; $i <= $itemCount; $i++){
  $datetime = $xmlObject->synchronization($i)->getElementsByTagName('datetime')->item(0)->childNodes->item(0)->nodeValue;
  $messagetype  = $xmlObject->synchronization($i)->getElementsByTagName('messagetype')->item(0)->childNodes->item(0)->nodeValue;
  $idnumber = $xmlObject->synchronization($i)->getElementsByTagName('idnumber')->item(0)->childNodes->item(0)->nodeValue;
  $donorid  = $xmlObject->synchronization($i)->getElementsByTagName('donorid')->item(0)->childNodes->item(0)->nodeValue;
  $recipientid = $xmlObject->synchronization($i)->getElementsByTagName('recipientid')->item(0)->childNodes->item(0)->nodeValue;
  $numberfrom  = $xmlObject->synchronization($i)->getElementsByTagName('numberfrom')->item(0)->childNodes->item(0)->nodeValue;
  $numberto = $xmlObject->synchronization($i)->getElementsByTagName('numberto')->item(0)->childNodes->item(0)->nodeValue;
  $routinginfo  = $xmlObject->synchronization($i)->getElementsByTagName('routinginfo')->item(0)->childNodes->item(0)->nodeValue;
  $caseclass = $xmlObject->synchronization($i)->getElementsByTagName('caseclass')->item(0)->childNodes->item(0)->nodeValue;

  $sql   = "INSERT INTO portare (datetime, messagetype, idnumber, donorid, recipientid, numberfrom, numberto, routinginfo, caseclass) VALUES ('$datetime', '$messagetype', '$idnumber', '$donorid', '$recipientid', '$numberfrom', '$numberto', '$routinginfo',  '$caseclass')";
  mysql_query($sql);
  print "Finished Item $title n<br/>";
}
?>

dar am o eroare
PHP Fatal error:  Call to undefined method DOMNodeList::synchronization() in /test1.php on line 17

<?php
$xmlFeed='http://domain.ro/path/to/thefeed.xml';
$phoneList = simplexml_load_file($xmlFeed);

$dbhost='test';
$dbuser='test';
$dbpass='test';
try
{
$db = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8",$dbuser,$dbpass,array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
catch (PDOException $err)
{
echo "There was an error.  <br>\n" . $err;
die;
}

$sql_str  ="INSERT INTO portare (datetime, messagetype, idnumber, donorid, recipientid, numberfrom, numberto, routinginfo, caseclass) ";
$sql_str .="VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
$sql_str .="ON DUPLICATE KEY UPDATE routinginfo=?";

$stmt = $db->prepare($sql_str);

foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->routinginfo));
}

uite, pentru că sunt un băiat de treabă, ți-am făcut eu de la 0 scriptul care îți populează baza de date. trebuie doar să modifici url-ul pt feed și datele de conectare la baza de date. nu uita că, coloana numberto trebuie să fie UNIQUE KEY.
pui un cronjob să-ți ruleze scriptul ăsta zilnic.

dilema mea e, cu ce te ajută o bază de date? mă gândesc că dacă nu ai știut cum să o populezi, nu vei știi nici să tragi valori din ea.

ma ajuta la portabilitatea numerelor. am in asterisk un agi care face all call queey si daca in baza de date exista numarul de telefon care este portat ii pune in fata routing number (routinginfo) mai exact 18720 0755XXXXXXX si de aici eu stiu ca numerul este portat in reteaua vodafone si il trimit catre reteaua vodafone nu il trimit catre orange cum ar fi normal dupa prefix .orcum am reusit si eu intr-un final sa fac o colectie de php-uri de pe net puse cap la cap dar merge al naibi de greu. imagineaza-ti 2000 numere portate zilnic
.
o sa postez ce am facut eu si ma apuc acuma sa testez pe al tau sa vedem care e mai rapid.
Iti multumesc foarte mult pentru timpul acordat si sper ca vreodata sa pot sa te cinstesc cu vreo bere 2 3 4 5 6 7


<?php
$xmlDoc = new DOMDocument();
$xmlDoc->load("http://1xxxxxxx/sync...ync2011-04.xml");
$mysql_hostname = "localhost"; // Example : localhost
$mysql_user = "ccccc";
$mysql_password = "ccc";
$mysql_database = "mnp";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Oops some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Oops some thing went wrong");
$xmlObject = $xmlDoc->getElementsByTagName('synchronization');
$itemCount = $xmlObject->length;
for ($i=0; $i <= $itemCount; $i++){
  $datetime = $xmlObject->item($i)->getElementsByTagName('datetime')->item(0)->childNodes->item(0)->nodeValue;
  $messagetype  = $xmlObject->item($i)->getElementsByTagName('messagetype')->item(0)->childNodes->item(0)->nodeValue;
  $idnumber = $xmlObject->item($i)->getElementsByTagName('idnumber')->item(0)->childNodes->item(0)->nodeValue;
  $donorid  = $xmlObject->item($i)->getElementsByTagName('donorid')->item(0)->childNodes->item(0)->nodeValue;
  $recipientid = $xmlObject->item($i)->getElementsByTagName('recipientid')->item(0)->childNodes->item(0)->nodeValue;
  $numberfrom  = $xmlObject->item($i)->getElementsByTagName('numberfrom')->item(0)->childNodes->item(0)->nodeValue;
  $numberto = $xmlObject->item($i)->getElementsByTagName('numberto')->item(0)->childNodes->item(0)->nodeValue;
  $routinginfo  = $xmlObject->item($i)->getElementsByTagName('routinginfo')->item(0)->childNodes->item(0)->nodeValue;
  $caseclass = $xmlObject->item($i)->getElementsByTagName('caseclass')->item(0)->childNodes->item(0)->nodeValue;
$sql_n = "Select * from portare where numberto = '$numberto'";
$result = mysql_query($sql_n);
$count = mysql_num_rows($result);
if (!$count) {
  $sql   = "INSERT INTO portare (datetime, messagetype, idnumber, donorid, recipientid, numberfrom, numberto, routinginfo, caseclass) VALUES ('$datetime', '$messagetype', '$idnumber', '$donorid', '$recipientid', '$numberfrom', '$numberto', '$routinginfo',  '$caseclass')";
  } else {
$sql = "Update portare SET datetime='$datetime', messagetype='$messagetype', idnumber='$idnumber', donorid='$donorid', recipientid='$recipientid', numberfrom='$numberfrom', numberto='$numberto', routinginfo='$routinginfo', caseclass='$caseclass'  Where  numberto='$numberto'";
}
  mysql_query($sql);
}
print "Finished";
?>

//  <synchronization>
// <datetime>2011-04-01T09:02:27</datetime>
// <messagetype>npbroadcast</messagetype>
// <idnumber>xxxxxxx</idnumber>
// <donorid>RTLC</donorid>
// <recipientid>RCSF</recipientid>
// <numberfrom>xxxx</numberfrom>
// <numberto>xxxxxxx</numberto>
// <routinginfo>18771</routinginfo>
// <caseclass>fixed</caseclass>
//  </synchronization>

acum am testat scriptul tau si sa stii c a imi dubleaza baza de date. poti sa te mai uiti un pic ca as vrea sa utilizez ce ai facut tu pentru ca merge muuult mai rapid ce ai facut tu. Uitate te rog la ce am facut eu pentru ca ala merge dar e ultra incet

am adaugat eu $dbname='mnp';

si sa explic cam ce am facut eu in scriptul meu
1 parsez xml-ul
2 verific in baza de date daca nu cumva mai exista numarul de telefon portat in baza la numberto
3 daca nu exista adaug
4 daca exista fac update la toate campurile deoarece se schimba multe date (cam toate) si am zis ca fac update la tot randul respectiv.

apropo mai stiu si eu una alta. singura chestie e ca imi ia mult timp pana ma documentez si citesc ca sa rezolv o problema. si e prima data cand apelez la forumuri pentru ca sunt presat de timp si de $$. de obicei platesc sa mi se faca scripturi si alte cele

Edited by arsenieciprian, 25 March 2015 - 16:10.

mă gândeam că vrei să faci un site gen portare.ro și aveam dubii că vei reușii

când ai creat tabelul portare, ai declarat coloana numberto ca fiind unique? nu știu exact ce fel de bază de date folosești, că nu ai precizat, presupun că MySQL, dar codul de mai jos ar trebuii să meargă și astfel nu îți va mai putea creea dubluri

CREATE TABLE portare ( datetime timestamp, messagetype varchar(255), idnumber varchar(255), donorid varchar(255), recipientid varchar(255), numberfrom varchar(255), numberto varchar(255), routinginfo varchar(255), caseclass varchar(255) ) UNIQUE KEY `numberto` (`numberto`);

dacă vrei ca în cazul numerelor ce există deja în baza de date să modifici mai multe elemente, pur și simplu le adaugi în cod în locurile prezentate în poză
[ https://i.imgur.com/dlThbVk.png - Pentru incarcare in pagina (embed) Click aici ]

Edited by mberila, 25 March 2015 - 17:08.

NU ESTE PUS UNIQUE BAZA AM CREATO EXACT CUM AI DESCRIS MAI SUS . POT SA MODIFIC SI SA VAD CE IASA. SI MYSQL FOLOSESC

am facut asa


ALTER TABLE `portare` ADD UNIQUE(`numberto`);

si aici cum trebuie facut  ?

ON DUPLICATE KEY UPDATE routinginfo=?

Edited by arsenieciprian, 25 March 2015 - 17:36.

și îți face în continuare dubluri?

nu nu acuma nu dubleaza de cand am dat alter table

ultima ajustare e aia sa faca update la toaata coloana

ma mai ajuti cu ulima ajustare ca nu stiu cum sa fac

arsenieciprian, on 25 martie 2015 - 17:47, said:

adaugi aici toate câmpurile care vrei să le modifice, separate de virgulă. ex

$sql_str .="ON DUPLICATE KEY UPDATE routinginfo=?, messagetype=?, idnumber?";

și adaugi aceleași elemente în aceeași ordine la finalul array-ului din foreach, la fel, separate cu virgulă. ex

foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->routinginfo,
  $phoneItem->messagetype,
  $phoneItem->idnumber));
}

am inteles acuma . eram in dubii de ce trebuie dublate in array. bine ca nici nu acuma nu inteleg . dar din moment ce merge e ok

$sql_str .="ON DUPLICATE KEY UPDATE datetime=?, messagetype=?, idnumber=?, donorid=?, recipientid=?, numberfrom=?, numberto=?, routinginfo=?, caseclass=?";
$stmt = $db->prepare($sql_str);
foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass));
}

prima dată alocă valori pt ?-urile de la INSERT și a doua oară alocă valori pt ?-urile de la UPDATE

P.S. poți pune cronjob-ul să meargă mai des de o dată pe zi dacă vrei, îți garantez că resursele consumate de codul ăsta sunt infime

de aia am ales varianta ta pentru ca consuma resurse ffffff mici
multumesc mult mult de tot.
am sa postez varianta finala pentru cei care vreodata vor implemeta portabilitatea pe serverele lor (operatorii in principiu)

-- phpMyAdmin SQL Dump
-- version 4.2.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 25, 2015 at 04:23 PM
-- Server version: 5.5.42
-- PHP Version: 5.4.38
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `mor_mnp`
--
-- --------------------------------------------------------
--
-- Table structure for table `portare`
--
CREATE TABLE IF NOT EXISTS `portare` (
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `messagetype` varchar(255) DEFAULT NULL,
  `idnumber` varchar(255) DEFAULT NULL,
  `donorid` varchar(255) DEFAULT NULL,
  `recipientid` varchar(255) DEFAULT NULL,
  `numberfrom` varchar(255) DEFAULT NULL,
  `numberto` varchar(255) DEFAULT NULL,
  `routinginfo` varchar(255) DEFAULT NULL,
  `caseclass` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `portare`
--
ALTER TABLE `portare`
ADD UNIQUE KEY `numberto` (`numberto`), ADD UNIQUE KEY `numberfrom` (`numberfrom`);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

si acuma scriptul functional

<?php
$xmlFeed='http://x.x.x.x/sync/...llday.xml&#39;;
$phoneList = simplexml_load_file($xmlFeed);
$dbhost='localhost';
$dbuser='xxx';
$dbpass='xxxx';
$dbname='xxxxx';
try
{
$db = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8",$dbuser,$dbpass,array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
catch (PDOException $err)
{
echo "There was an error.  <br>\n" . $err;
die;
}
$sql_str  ="INSERT INTO portare (datetime, messagetype, idnumber, donorid, recipientid, numberfrom, numberto, routinginfo, caseclass) ";
$sql_str .="VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
$sql_str .="ON DUPLICATE KEY UPDATE datetime=?, messagetype=?, idnumber=?, donorid=?, recipientid=?, numberfrom=?, numberto=?, routinginfo=?, caseclass=?";
$stmt = $db->prepare($sql_str);
foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass));
}

iti multumesc inca o data pentru ajutorul acordat

acuma surpriza surprizelor trebuie sa fac aceiasi chestie pe postgresql