Re: [PHP] Add New Records Only!
DBF is native dbase databases, used by foxpro etcetera. But anyhow, I think what you've suggested is what I'll have to do. Take records from all my mySQL table(s) and put Unique records (based on your unique identifier exception code) into a temp mySQL table and then simply transfer the records into the dbase dbf. Just for your info, I'm using the dbase() functions in php, using dbase_add_records() to add the records into the dbf. I'll give this a try, use your code, and more then likely it should work. Thanks! On 3/26/07 4:39 PM, "Richard Lynch" <[EMAIL PROTECTED]> wrote: > I have no idea what DBF is, but if the data is already in MySQL with > duplicates, you'll need to take everything I just said, and > "translate" it to DBF. > > If it cannot be translated to DBF because DBF has no unique indeces, > you could, perhaps, create a TEMP table in MySQL, with the unique > index, insert rows one by one, and silently ignore the ones that fail > to insert. > > Then export the temp table to DBF. > > On Mon, March 26, 2007 3:19 pm, Rahul Sitaram Johari wrote: >> >> I'm exporting the data from mySQL table(s) into a dbase DBF table. The >> unique index you're talking about should be in the DBF end, if I'm not >> mistaken - but I'm not sure how to do that, and if that will help >> mySQL to >> get that error and fail the second insert. >> >> Unless I'm not getting this right. >> >> >> On 3/26/07 4:13 PM, "Richard Lynch" <[EMAIL PROTECTED]> wrote: >> >>> On Mon, March 26, 2007 2:28 pm, Rahul Sitaram Johari wrote: > Another option would be to just create a UNIQUE INDEX on the > fields > you think "should" be unique, and then your second insert is gonna > fail, and you can just ignore that. Could you possibly elaborate on this? Things I'm trying are still not working out the way or want to, or efficiently. So still looking for a solution. >>> >>> create unique index no_duplicates on whatever(field1, field2, >>> field3); >>> >>> $query = "insert into whatever (field1, field2, field3) >>> values('$field1_sql', '$field2_sql', '$field3_sql')"; >>> $insert = mysql_query($query, $connection); >>> if (!$insert && mysql_errno($connection) == 1062){ >>> //this is a duplicate insert that failed. do whatever you want >>> here >>> } >>> elseif (!$insert){ >>> //something else went wrong with the insert. >>> //provide usual debugging error handling here >>> } >>> else{ >>> //everything went fine here >>> } >>> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
I have no idea what DBF is, but if the data is already in MySQL with duplicates, you'll need to take everything I just said, and "translate" it to DBF. If it cannot be translated to DBF because DBF has no unique indeces, you could, perhaps, create a TEMP table in MySQL, with the unique index, insert rows one by one, and silently ignore the ones that fail to insert. Then export the temp table to DBF. On Mon, March 26, 2007 3:19 pm, Rahul Sitaram Johari wrote: > > I'm exporting the data from mySQL table(s) into a dbase DBF table. The > unique index you're talking about should be in the DBF end, if I'm not > mistaken - but I'm not sure how to do that, and if that will help > mySQL to > get that error and fail the second insert. > > Unless I'm not getting this right. > > > On 3/26/07 4:13 PM, "Richard Lynch" <[EMAIL PROTECTED]> wrote: > >> On Mon, March 26, 2007 2:28 pm, Rahul Sitaram Johari wrote: Another option would be to just create a UNIQUE INDEX on the fields you think "should" be unique, and then your second insert is gonna fail, and you can just ignore that. >>> >>> Could you possibly elaborate on this? >>> Things I'm trying are still not working out the way or want to, or >>> efficiently. So still looking for a solution. >> >> create unique index no_duplicates on whatever(field1, field2, >> field3); >> >> $query = "insert into whatever (field1, field2, field3) >> values('$field1_sql', '$field2_sql', '$field3_sql')"; >> $insert = mysql_query($query, $connection); >> if (!$insert && mysql_errno($connection) == 1062){ >> //this is a duplicate insert that failed. do whatever you want >> here >> } >> elseif (!$insert){ >> //something else went wrong with the insert. >> //provide usual debugging error handling here >> } >> else{ >> //everything went fine here >> } >> > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
I'm exporting the data from mySQL table(s) into a dbase DBF table. The unique index you're talking about should be in the DBF end, if I'm not mistaken - but I'm not sure how to do that, and if that will help mySQL to get that error and fail the second insert. Unless I'm not getting this right. On 3/26/07 4:13 PM, "Richard Lynch" <[EMAIL PROTECTED]> wrote: > On Mon, March 26, 2007 2:28 pm, Rahul Sitaram Johari wrote: >>> Another option would be to just create a UNIQUE INDEX on the fields >>> you think "should" be unique, and then your second insert is gonna >>> fail, and you can just ignore that. >> >> Could you possibly elaborate on this? >> Things I'm trying are still not working out the way or want to, or >> efficiently. So still looking for a solution. > > create unique index no_duplicates on whatever(field1, field2, field3); > > $query = "insert into whatever (field1, field2, field3) > values('$field1_sql', '$field2_sql', '$field3_sql')"; > $insert = mysql_query($query, $connection); > if (!$insert && mysql_errno($connection) == 1062){ > //this is a duplicate insert that failed. do whatever you want here > } > elseif (!$insert){ > //something else went wrong with the insert. > //provide usual debugging error handling here > } > else{ > //everything went fine here > } > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
On Mon, March 26, 2007 2:28 pm, Rahul Sitaram Johari wrote: >> Another option would be to just create a UNIQUE INDEX on the fields >> you think "should" be unique, and then your second insert is gonna >> fail, and you can just ignore that. > > Could you possibly elaborate on this? > Things I'm trying are still not working out the way or want to, or > efficiently. So still looking for a solution. create unique index no_duplicates on whatever(field1, field2, field3); $query = "insert into whatever (field1, field2, field3) values('$field1_sql', '$field2_sql', '$field3_sql')"; $insert = mysql_query($query, $connection); if (!$insert && mysql_errno($connection) == 1062){ //this is a duplicate insert that failed. do whatever you want here } elseif (!$insert){ //something else went wrong with the insert. //provide usual debugging error handling here } else{ //everything went fine here } -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
Ave, > Another option would be to just create a UNIQUE INDEX on the fields > you think "should" be unique, and then your second insert is gonna > fail, and you can just ignore that. Could you possibly elaborate on this? Things I'm trying are still not working out the way or want to, or efficiently. So still looking for a solution. Thanks. On 3/23/07 8:01 PM, "Richard Lynch" <[EMAIL PROTECTED]> wrote: > If the table is "small", you could maybe do: > > delete from foo where id in (select b.id from foo as a, foo as b where > a.id < b.id and a.field1 = b.field1 and a.field2 = b.field2 and ...) > > Another option would be to just create a UNIQUE INDEX on the fields > you think "should" be unique, and then your second insert is gonna > fail, and you can just ignore that. > > On Fri, March 23, 2007 11:51 am, Satyam wrote: >> Delete from table where id = (select min(id) from table group by >> field1, >> field2 ,... having count(id) > 1) >> >> Id is the unique primary key, fieldn are the fields that can be >> duplicated. >> Each time this runs it will remove one occurence of the all duplicated >> records. You'd have to run it several times until it deletes no more >> records. In MySql, you cannot have the same table in the 'delete' >> and in >> the subquery, thus, you will have to first insert the id's in an >> auxiliary >> table and then delete the records. >> >> Satyam >> >> - Original Message - >> From: "Rahul Sitaram Johari" <[EMAIL PROTECTED]> >> To: "Mark" ; "PHP" >> Sent: Friday, March 23, 2007 5:24 PM >> Subject: Re: [PHP] Add New Records Only! >> >> >>> >>> Ave, >>> >>> "Three: Insert everything and remove duplicates later." >>> >>> Out of the suggested options, this option is sounding the most sane >>> attainable on my end. I don't have a complete grip on how to >>> accomplish >>> this, but certainly sounds feasible. Let me look at ways to achieve >>> this. >>> >>> Thanks! >>> >>> On 3/23/07 11:36 AM, "Mark" wrote: >>> >>>> Rahul Sitaram Johari wrote: >>>> >>>> As far as I can see, there is probably only three ways to do this: >>>> >>>> One: Make sure your dbase system contains unique primary key >>>> capability, >>>> and >>>> use it to avoid duplicates. >>>> Two: query for the row, if it isn't there insert it. (You'll have >>>> to deal >>>> with concurrency with locking or something) >>>> Three: Insert everything and remove duplicates later. >>>> >>>>> Ave, >>>>> >>>>> Let me explain what I¹m trying to do. Unfortunately it¹s not a >>>>> very >>>>> simple >>>>> Add/Update transaction. >>>>> Basically I gather records from multiple mySQL tables and add them >>>>> to a >>>>> DBF >>>>> (dbase) database. I wrote a code which was able to accomplish >>>>> this >>>>> without any problems and the add_records goes very smooth. >>>>> >>>>> However, I have to modify this program now so that only ³New² >>>>> records, >>>>> i.e., records that have not yet been transferred, are added into >>>>> the >>>>> DBF. >>>>> In other words, any records that are being added, which already >>>>> exist in >>>>> the DBF, should not be added. >>>>> >>>>> If it were just adding records from one mySQL to another mySQL, I >>>>> could >>>>> easily use INSERT IGNORE or the Unique Key fundamental however, >>>>> since >>>>> I¹m adding from multiple mySQL tables into DBF, I¹m not sure how >>>>> to go >>>>> about doing this. >>>>> >>>>> Here¹s my code that takes records from multiple mySQL tables and >>>>> adds >>>>> them >>>>> to a DBF. >>>>> >>>>> // define the array with mySQL Table Names & Identifier >>>>> $tChoice = array( >>>>>"lodispo" => "VB", >>>>>"lodispo_osma" => "ATL", >>>>>"lodispo_osmh" => "HOU&
Re: [PHP] Add New Records Only!
If the table is "small", you could maybe do: delete from foo where id in (select b.id from foo as a, foo as b where a.id < b.id and a.field1 = b.field1 and a.field2 = b.field2 and ...) Another option would be to just create a UNIQUE INDEX on the fields you think "should" be unique, and then your second insert is gonna fail, and you can just ignore that. On Fri, March 23, 2007 11:51 am, Satyam wrote: > Delete from table where id = (select min(id) from table group by > field1, > field2 ,... having count(id) > 1) > > Id is the unique primary key, fieldn are the fields that can be > duplicated. > Each time this runs it will remove one occurence of the all duplicated > records. You'd have to run it several times until it deletes no more > records. In MySql, you cannot have the same table in the 'delete' > and in > the subquery, thus, you will have to first insert the id's in an > auxiliary > table and then delete the records. > > Satyam > > - Original Message - > From: "Rahul Sitaram Johari" <[EMAIL PROTECTED]> > To: "Mark" ; "PHP" > Sent: Friday, March 23, 2007 5:24 PM > Subject: Re: [PHP] Add New Records Only! > > >> >> Ave, >> >> "Three: Insert everything and remove duplicates later." >> >> Out of the suggested options, this option is sounding the most sane >> attainable on my end. I don't have a complete grip on how to >> accomplish >> this, but certainly sounds feasible. Let me look at ways to achieve >> this. >> >> Thanks! >> >> On 3/23/07 11:36 AM, "Mark" wrote: >> >>> Rahul Sitaram Johari wrote: >>> >>> As far as I can see, there is probably only three ways to do this: >>> >>> One: Make sure your dbase system contains unique primary key >>> capability, >>> and >>> use it to avoid duplicates. >>> Two: query for the row, if it isn't there insert it. (You'll have >>> to deal >>> with concurrency with locking or something) >>> Three: Insert everything and remove duplicates later. >>> >>>> Ave, >>>> >>>> Let me explain what I¹m trying to do. Unfortunately it¹s not a >>>> very >>>> simple >>>> Add/Update transaction. >>>> Basically I gather records from multiple mySQL tables and add them >>>> to a >>>> DBF >>>> (dbase) database. I wrote a code which was able to accomplish >>>> this >>>> without any problems and the add_records goes very smooth. >>>> >>>> However, I have to modify this program now so that only ³New² >>>> records, >>>> i.e., records that have not yet been transferred, are added into >>>> the >>>> DBF. >>>> In other words, any records that are being added, which already >>>> exist in >>>> the DBF, should not be added. >>>> >>>> If it were just adding records from one mySQL to another mySQL, I >>>> could >>>> easily use INSERT IGNORE or the Unique Key fundamental however, >>>> since >>>> I¹m adding from multiple mySQL tables into DBF, I¹m not sure how >>>> to go >>>> about doing this. >>>> >>>> Here¹s my code that takes records from multiple mySQL tables and >>>> adds >>>> them >>>> to a DBF. >>>> >>>> // define the array with mySQL Table Names & Identifier >>>> $tChoice = array( >>>>"lodispo" => "VB", >>>>"lodispo_osma" => "ATL", >>>>"lodispo_osmh" => "HOU", >>>>"lodispo_osmn" => "NSV", >>>>"lodispo_osmp" => "PAA", >>>>"lodispo_osmj" => "JAX", >>>>"lodispo_osmt" => "TPA", >>>>"lodispo_dmam" => "MET", >>>>"lodispo_osmf" => "FTM", >>>>"lodispo_hfglend" => "GLEND", >>>> "lodispo_hfmesa" => "MESA", >>>> "lodispo_hfphenx" => "PHENX", >>>> ); >>>> >>>> >>>> // open DBF in read-write mode >>>> $db2 = dbase_open($theDBFfile, 2); >>>> if (!$db2) { >>&
Re: [PHP] Add New Records Only!
Rahul Sitaram Johari wrote: > > Ave, > > It's definitely not live data, so that is not a problem at all. But I'm > not sure I understand your method very well. > > I do understand getting data from both the existing DBF and the multiple > mySQL tables into a temporary mySQL table. But if I do go ahead and do > that, I guess I could write a 'delete-duplicates' kind of code that > deletes all rows in that temporary table which are duplicates, and then > add the leftover into the DBF. > > Not sure how this sounds, or how close this is to what you were saying. > And not even sure how to implement this. Actually, I was thinking about this, and if you can control the order of the records coming to you, you can do a simple merge reduction. What you do is issue a number of SQL queries to your databases simultaneously. Something like: SELECT * from mytable order by myrow Then, in the receiving code, have an array of SQL connections. More pseudo code for you: // Seed the array for($i=0; $i < $num_sql_cons; i++) { $sql_record[$i] = sql_get_next($dbs[$i]); } while(1) { $smallest=0; // Find the smallest record for($i=1; $i < $num_sql_cons; $i++) { if($sql_record[$i]->myrow < $sql_record[$smallest]->myrow) $smallest = $i; else if($sql_record[$i]->myrow == $sql_record[$i]->myrow) $sql_record[$i]= sql_get_next($dbs[$i]); } write_record($sql_record[$smallest]); $sql_record[$smallest] = sql_get_next($dbs[$smallest]); } Then make sure that last few are unique and you are done. > > > On 3/23/07 2:27 PM, "markw@mohawksoft.com" wrote: > >>> >>> Ave, >>> >>> "Three: Insert everything and remove duplicates later." >>> >>> Out of the suggested options, this option is sounding the most sane >>> attainable on my end. I don't have a complete grip on how to accomplish >>> this, but certainly sounds feasible. Let me look at ways to achieve >>> this. >>> >>> Thanks! >>> >> >> I'm not sure how live this data is, but it should be easy to do if you >> are merely replicating and don't need this stuff all live all the time. >> >> basically, query all databases, get all the data, and store in a >> temporary database. Then query all the data in the temporary database and >> order by your primary key. Save a copy of your primary key for each next >> row. If the subsequent primary key is the same as the previous one, it is >> a duplicate record and ignore it. >> >> like this: (pseudo code) >> >> $primary_key = ""; >> while(!eof()) >> { >>$record = get_next_record(); >> >> if($record->primary_key != $primary_key) >> { >>store_new_record($record); >>$primary_key = $record->primary_key; >> } >> } >> >> >> } >> >>> On 3/23/07 11:36 AM, "Mark" wrote: >>> Rahul Sitaram Johari wrote: As far as I can see, there is probably only three ways to do this: One: Make sure your dbase system contains unique primary key capability, and use it to avoid duplicates. Two: query for the row, if it isn't there insert it. (You'll have to deal with concurrency with locking or something) Three: Insert everything and remove duplicates later. > Ave, > > Let me explain what I¹m trying to do. Unfortunately it¹s not a very > simple > Add/Update transaction. > Basically I gather records from multiple mySQL tables and add them to > a DBF > (dbase) database. I wrote a code which was able to accomplish this > without any problems and the add_records goes very smooth. > > However, I have to modify this program now so that only ³New² records, > i.e., records that have not yet been transferred, are added into the > DBF. > In other words, any records that are being added, which already exist > in > the DBF, should not be added. > > If it were just adding records from one mySQL to another mySQL, I > could easily use INSERT IGNORE or the Unique Key fundamental > however, since I¹m adding from multiple mySQL tables into DBF, I¹m not > sure how to go about doing this. > > Here¹s my code that takes records from multiple mySQL tables and adds > them > to a DBF. > > // define the array with mySQL Table Names & Identifier > $tChoice = array( >"lodispo" => "VB", >"lodispo_osma" => "ATL", >"lodispo_osmh" => "HOU", >"lodispo_osmn" => "NSV", >"lodispo_osmp" => "PAA", >"lodispo_osmj" => "JAX", >"lodispo_osmt" => "TPA", >"lodispo_dmam" => "MET", >"lodispo_osmf" => "FTM", >"lodispo_hfglend" => "GLEND", > "lodispo_hfmesa" => "MESA", > "lodispo_hfphenx" => "PHENX", > ); > > > // open DBF in read-write mode > $db2 = dbase_open($theDBFf
Re: [PHP] Add New Records Only!
Ave, > A better solution would be to add a column in the MySQL table, maybe call it > "processed" with a default value of 0, and update this value to 1 with each > row inserted. Then you are only querying records where processed=0. > Of course this will not work if you cannot modify the MySQL table. I have to tell you that this is probably the most efficient & feasible method suggested yet! Let me take a look at my structure & application and see how efficiently this can be implemented. I did think about the cron job midnight update initially - and while it looked like a possible method, my only problem was that occasionally a manual update would be required, and I would need a program in place that is able to handle that. If it were solely an automatic midnight update, I would have certainly gone the cron way with a date limitation. Thanks! On 3/23/07 2:17 PM, "Brad Fuller" <[EMAIL PROTECTED]> wrote: > Rahul wrote: >> Ave, >> >> It's definitely not live data, so that is not a problem at all. But I'm >> not >> sure I understand your method very well. >> >> I do understand getting data from both the existing DBF and the multiple >> mySQL tables into a temporary mySQL table. But if I do go ahead and do >> that, >> I guess I could write a 'delete-duplicates' kind of code that deletes all >> rows in that temporary table which are duplicates, and then add the >> leftover >> into the DBF. >> >> Not sure how this sounds, or how close this is to what you were saying. >> And >> not even sure how to implement this. > > > Do you need to update this more than once a day? Is there a date field in > all the tables? > > If you can do it daily, then query for records from the previous day and run > it once daily at midnight via cron job. > > If it has to be done more often than once a day there are other solutions. > > One thing I thought is to store the name of the table it came from along > with the primary key in that table. These 2 fields combined will be your > unique identifier. Store this in your DBF and check for existence before > you insert. > > With that solution in mind however you're still querying for the entire set > of data which is very inefficient. > > A better solution would be to add a column in the MySQL table, maybe call it > "processed" with a default value of 0, and update this value to 1 with each > row inserted. Then you are only querying records where processed=0. > Of course this will not work if you cannot modify the MySQL table. > > Best of luck, > > Brad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Add New Records Only!
Rahul wrote: > Ave, > > It's definitely not live data, so that is not a problem at all. But I'm > not > sure I understand your method very well. > > I do understand getting data from both the existing DBF and the multiple > mySQL tables into a temporary mySQL table. But if I do go ahead and do > that, > I guess I could write a 'delete-duplicates' kind of code that deletes all > rows in that temporary table which are duplicates, and then add the > leftover > into the DBF. > > Not sure how this sounds, or how close this is to what you were saying. > And > not even sure how to implement this. Do you need to update this more than once a day? Is there a date field in all the tables? If you can do it daily, then query for records from the previous day and run it once daily at midnight via cron job. If it has to be done more often than once a day there are other solutions. One thing I thought is to store the name of the table it came from along with the primary key in that table. These 2 fields combined will be your unique identifier. Store this in your DBF and check for existence before you insert. With that solution in mind however you're still querying for the entire set of data which is very inefficient. A better solution would be to add a column in the MySQL table, maybe call it "processed" with a default value of 0, and update this value to 1 with each row inserted. Then you are only querying records where processed=0. Of course this will not work if you cannot modify the MySQL table. Best of luck, Brad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
Ave, It's definitely not live data, so that is not a problem at all. But I'm not sure I understand your method very well. I do understand getting data from both the existing DBF and the multiple mySQL tables into a temporary mySQL table. But if I do go ahead and do that, I guess I could write a 'delete-duplicates' kind of code that deletes all rows in that temporary table which are duplicates, and then add the leftover into the DBF. Not sure how this sounds, or how close this is to what you were saying. And not even sure how to implement this. On 3/23/07 2:27 PM, "markw@mohawksoft.com" wrote: >> >> Ave, >> >> "Three: Insert everything and remove duplicates later." >> >> Out of the suggested options, this option is sounding the most sane >> attainable on my end. I don't have a complete grip on how to accomplish >> this, but certainly sounds feasible. Let me look at ways to achieve this. >> >> Thanks! >> > > I'm not sure how live this data is, but it should be easy to do if you are > merely replicating and don't need this stuff all live all the time. > > basically, query all databases, get all the data, and store in a temporary > database. Then query all the data in the temporary database and order by > your primary key. Save a copy of your primary key for each next row. If > the subsequent primary key is the same as the previous one, it is a > duplicate record and ignore it. > > like this: (pseudo code) > > $primary_key = ""; > while(!eof()) > { >$record = get_next_record(); > > if($record->primary_key != $primary_key) > { >store_new_record($record); >$primary_key = $record->primary_key; > } > } > > > } > >> On 3/23/07 11:36 AM, "Mark" wrote: >> >>> Rahul Sitaram Johari wrote: >>> >>> As far as I can see, there is probably only three ways to do this: >>> >>> One: Make sure your dbase system contains unique primary key capability, >>> and >>> use it to avoid duplicates. >>> Two: query for the row, if it isn't there insert it. (You'll have to >>> deal >>> with concurrency with locking or something) >>> Three: Insert everything and remove duplicates later. >>> Ave, Let me explain what I¹m trying to do. Unfortunately it¹s not a very simple Add/Update transaction. Basically I gather records from multiple mySQL tables and add them to a DBF (dbase) database. I wrote a code which was able to accomplish this without any problems and the add_records goes very smooth. However, I have to modify this program now so that only ³New² records, i.e., records that have not yet been transferred, are added into the DBF. In other words, any records that are being added, which already exist in the DBF, should not be added. If it were just adding records from one mySQL to another mySQL, I could easily use INSERT IGNORE or the Unique Key fundamental however, since I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go about doing this. Here¹s my code that takes records from multiple mySQL tables and adds them to a DBF. // define the array with mySQL Table Names & Identifier $tChoice = array( "lodispo" => "VB", "lodispo_osma" => "ATL", "lodispo_osmh" => "HOU", "lodispo_osmn" => "NSV", "lodispo_osmp" => "PAA", "lodispo_osmj" => "JAX", "lodispo_osmt" => "TPA", "lodispo_dmam" => "MET", "lodispo_osmf" => "FTM", "lodispo_hfglend" => "GLEND", "lodispo_hfmesa" => "MESA", "lodispo_hfphenx" => "PHENX", ); // open DBF in read-write mode $db2 = dbase_open($theDBFfile, 2); if (!$db2) { echo "Fatal Error: Unable to open database"; exit; } else { // Let's Run Array Loops! foreach ($tChoice as $tblQ => $bxQ) { // connect to mySQL tables one by one mysql_select_db($database_imslead_transfer, $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ"; $loDispo = mysql_query($query_loDispo, $imslead_transfer) or die(mysql_error()); $row_loDispo = mysql_fetch_assoc($loDispo); $totalRows_loDispo = mysql_num_rows($loDispo); // write mySql data to Dbf do { dbase_add_record($db2, array( $row_loDispo['phone'], $row_loDispo['comments'], $row_loDispo['starttime'], $row_loDispo['endtime'], $row_loDispo['dispo'], $row_loDispo['loanofficer'], $row_loDispo['verifier'], $bxQ))
Re: [PHP] Add New Records Only!
> > Ave, > > "Three: Insert everything and remove duplicates later." > > Out of the suggested options, this option is sounding the most sane > attainable on my end. I don't have a complete grip on how to accomplish > this, but certainly sounds feasible. Let me look at ways to achieve this. > > Thanks! > I'm not sure how live this data is, but it should be easy to do if you are merely replicating and don't need this stuff all live all the time. basically, query all databases, get all the data, and store in a temporary database. Then query all the data in the temporary database and order by your primary key. Save a copy of your primary key for each next row. If the subsequent primary key is the same as the previous one, it is a duplicate record and ignore it. like this: (pseudo code) $primary_key = ""; while(!eof()) { $record = get_next_record(); if($record->primary_key != $primary_key) { store_new_record($record); $primary_key = $record->primary_key; } } } > On 3/23/07 11:36 AM, "Mark" wrote: > >> Rahul Sitaram Johari wrote: >> >> As far as I can see, there is probably only three ways to do this: >> >> One: Make sure your dbase system contains unique primary key capability, >> and >> use it to avoid duplicates. >> Two: query for the row, if it isn't there insert it. (You'll have to >> deal >> with concurrency with locking or something) >> Three: Insert everything and remove duplicates later. >> >>> Ave, >>> >>> Let me explain what I¹m trying to do. Unfortunately it¹s not a very >>> simple >>> Add/Update transaction. >>> Basically I gather records from multiple mySQL tables and add them to a >>> DBF >>> (dbase) database. I wrote a code which was able to accomplish this >>> without any problems and the add_records goes very smooth. >>> >>> However, I have to modify this program now so that only ³New² records, >>> i.e., records that have not yet been transferred, are added into the >>> DBF. >>> In other words, any records that are being added, which already exist >>> in >>> the DBF, should not be added. >>> >>> If it were just adding records from one mySQL to another mySQL, I could >>> easily use INSERT IGNORE or the Unique Key fundamental however, since >>> I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go >>> about doing this. >>> >>> Here¹s my code that takes records from multiple mySQL tables and adds >>> them >>> to a DBF. >>> >>> // define the array with mySQL Table Names & Identifier >>> $tChoice = array( >>>"lodispo" => "VB", >>>"lodispo_osma" => "ATL", >>>"lodispo_osmh" => "HOU", >>>"lodispo_osmn" => "NSV", >>>"lodispo_osmp" => "PAA", >>>"lodispo_osmj" => "JAX", >>>"lodispo_osmt" => "TPA", >>>"lodispo_dmam" => "MET", >>>"lodispo_osmf" => "FTM", >>>"lodispo_hfglend" => "GLEND", >>> "lodispo_hfmesa" => "MESA", >>> "lodispo_hfphenx" => "PHENX", >>> ); >>> >>> >>> // open DBF in read-write mode >>> $db2 = dbase_open($theDBFfile, 2); >>> if (!$db2) { >>> echo "Fatal Error: Unable to open >>> database"; >>> exit; >>> } >>> >>> else { >>> >>> // Let's Run Array Loops! >>> foreach ($tChoice as $tblQ => $bxQ) { >>> >>> // connect to mySQL tables one by one >>> mysql_select_db($database_imslead_transfer, >>> $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ"; >>> $loDispo = mysql_query($query_loDispo, $imslead_transfer) >>> or >>> die(mysql_error()); >>> $row_loDispo = mysql_fetch_assoc($loDispo); >>> $totalRows_loDispo = mysql_num_rows($loDispo); >>> >>> // write mySql data to Dbf >>> do { >>> dbase_add_record($db2, array( >>> $row_loDispo['phone'], >>> $row_loDispo['comments'], >>> $row_loDispo['starttime'], >>> $row_loDispo['endtime'], >>> $row_loDispo['dispo'], >>> $row_loDispo['loanofficer'], >>> $row_loDispo['verifier'], >>> $bxQ)); >>> } while ($row_loDispo = mysql_fetch_assoc($loDispo)); >>> } >>> >>> dbase_close($db2); >>> } >>> >>> Any suggestions would be highly appreciated. >>> >>> Thanks, >>> >>> ~~~ >>> Rahul Sitaram Johari >>> CEO, Twenty Four Seventy Nine Inc. >>> >>> W: http://www.rahulsjohari.com >>> E: [EMAIL PROTECTED] >>> >>> ³I morti non sono piu soli ... The dead are no longer lonely² > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
Delete from table where id = (select min(id) from table group by field1, field2 ,... having count(id) > 1) Id is the unique primary key, fieldn are the fields that can be duplicated. Each time this runs it will remove one occurence of the all duplicated records. You'd have to run it several times until it deletes no more records. In MySql, you cannot have the same table in the 'delete' and in the subquery, thus, you will have to first insert the id's in an auxiliary table and then delete the records. Satyam - Original Message - From: "Rahul Sitaram Johari" <[EMAIL PROTECTED]> To: "Mark" ; "PHP" Sent: Friday, March 23, 2007 5:24 PM Subject: Re: [PHP] Add New Records Only! Ave, "Three: Insert everything and remove duplicates later." Out of the suggested options, this option is sounding the most sane attainable on my end. I don't have a complete grip on how to accomplish this, but certainly sounds feasible. Let me look at ways to achieve this. Thanks! On 3/23/07 11:36 AM, "Mark" wrote: Rahul Sitaram Johari wrote: As far as I can see, there is probably only three ways to do this: One: Make sure your dbase system contains unique primary key capability, and use it to avoid duplicates. Two: query for the row, if it isn't there insert it. (You'll have to deal with concurrency with locking or something) Three: Insert everything and remove duplicates later. Ave, Let me explain what I¹m trying to do. Unfortunately it¹s not a very simple Add/Update transaction. Basically I gather records from multiple mySQL tables and add them to a DBF (dbase) database. I wrote a code which was able to accomplish this without any problems and the add_records goes very smooth. However, I have to modify this program now so that only ³New² records, i.e., records that have not yet been transferred, are added into the DBF. In other words, any records that are being added, which already exist in the DBF, should not be added. If it were just adding records from one mySQL to another mySQL, I could easily use INSERT IGNORE or the Unique Key fundamental however, since I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go about doing this. Here¹s my code that takes records from multiple mySQL tables and adds them to a DBF. // define the array with mySQL Table Names & Identifier $tChoice = array( "lodispo" => "VB", "lodispo_osma" => "ATL", "lodispo_osmh" => "HOU", "lodispo_osmn" => "NSV", "lodispo_osmp" => "PAA", "lodispo_osmj" => "JAX", "lodispo_osmt" => "TPA", "lodispo_dmam" => "MET", "lodispo_osmf" => "FTM", "lodispo_hfglend" => "GLEND", "lodispo_hfmesa" => "MESA", "lodispo_hfphenx" => "PHENX", ); // open DBF in read-write mode $db2 = dbase_open($theDBFfile, 2); if (!$db2) { echo "Fatal Error: Unable to open database"; exit; } else { // Let's Run Array Loops! foreach ($tChoice as $tblQ => $bxQ) { // connect to mySQL tables one by one mysql_select_db($database_imslead_transfer, $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ"; $loDispo = mysql_query($query_loDispo, $imslead_transfer) or die(mysql_error()); $row_loDispo = mysql_fetch_assoc($loDispo); $totalRows_loDispo = mysql_num_rows($loDispo); // write mySql data to Dbf do { dbase_add_record($db2, array( $row_loDispo['phone'], $row_loDispo['comments'], $row_loDispo['starttime'], $row_loDispo['endtime'], $row_loDispo['dispo'], $row_loDispo['loanofficer'], $row_loDispo['verifier'], $bxQ)); } while ($row_loDispo = mysql_fetch_assoc($loDispo)); } dbase_close($db2); } Any suggestions would be highly appreciated. Thanks, ~~~ Rahul Sitaram Johari CEO, Twenty Four Seventy Nine Inc. W: http://www.rahulsjohari.com E: [EMAIL PROTECTED] ³I morti non sono piu soli ... The dead are no longer lonely² -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 22/03/2007 7:44 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
Ave, "Three: Insert everything and remove duplicates later." Out of the suggested options, this option is sounding the most sane attainable on my end. I don't have a complete grip on how to accomplish this, but certainly sounds feasible. Let me look at ways to achieve this. Thanks! On 3/23/07 11:36 AM, "Mark" wrote: > Rahul Sitaram Johari wrote: > > As far as I can see, there is probably only three ways to do this: > > One: Make sure your dbase system contains unique primary key capability, and > use it to avoid duplicates. > Two: query for the row, if it isn't there insert it. (You'll have to deal > with concurrency with locking or something) > Three: Insert everything and remove duplicates later. > >> Ave, >> >> Let me explain what I¹m trying to do. Unfortunately it¹s not a very simple >> Add/Update transaction. >> Basically I gather records from multiple mySQL tables and add them to a >> DBF >> (dbase) database. I wrote a code which was able to accomplish this >> without any problems and the add_records goes very smooth. >> >> However, I have to modify this program now so that only ³New² records, >> i.e., records that have not yet been transferred, are added into the DBF. >> In other words, any records that are being added, which already exist in >> the DBF, should not be added. >> >> If it were just adding records from one mySQL to another mySQL, I could >> easily use INSERT IGNORE or the Unique Key fundamental however, since >> I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go >> about doing this. >> >> Here¹s my code that takes records from multiple mySQL tables and adds them >> to a DBF. >> >> // define the array with mySQL Table Names & Identifier >> $tChoice = array( >>"lodispo" => "VB", >>"lodispo_osma" => "ATL", >>"lodispo_osmh" => "HOU", >>"lodispo_osmn" => "NSV", >>"lodispo_osmp" => "PAA", >>"lodispo_osmj" => "JAX", >>"lodispo_osmt" => "TPA", >>"lodispo_dmam" => "MET", >>"lodispo_osmf" => "FTM", >>"lodispo_hfglend" => "GLEND", >> "lodispo_hfmesa" => "MESA", >> "lodispo_hfphenx" => "PHENX", >> ); >> >> >> // open DBF in read-write mode >> $db2 = dbase_open($theDBFfile, 2); >> if (!$db2) { >> echo "Fatal Error: Unable to open >> database"; >> exit; >> } >> >> else { >> >> // Let's Run Array Loops! >> foreach ($tChoice as $tblQ => $bxQ) { >> >> // connect to mySQL tables one by one >> mysql_select_db($database_imslead_transfer, >> $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ"; >> $loDispo = mysql_query($query_loDispo, $imslead_transfer) or >> die(mysql_error()); >> $row_loDispo = mysql_fetch_assoc($loDispo); >> $totalRows_loDispo = mysql_num_rows($loDispo); >> >> // write mySql data to Dbf >> do { >> dbase_add_record($db2, array( >> $row_loDispo['phone'], >> $row_loDispo['comments'], >> $row_loDispo['starttime'], >> $row_loDispo['endtime'], >> $row_loDispo['dispo'], >> $row_loDispo['loanofficer'], >> $row_loDispo['verifier'], >> $bxQ)); >> } while ($row_loDispo = mysql_fetch_assoc($loDispo)); >> } >> >> dbase_close($db2); >> } >> >> Any suggestions would be highly appreciated. >> >> Thanks, >> >> ~~~ >> Rahul Sitaram Johari >> CEO, Twenty Four Seventy Nine Inc. >> >> W: http://www.rahulsjohari.com >> E: [EMAIL PROTECTED] >> >> ³I morti non sono piu soli ... The dead are no longer lonely² -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
Rahul Sitaram Johari wrote: As far as I can see, there is probably only three ways to do this: One: Make sure your dbase system contains unique primary key capability, and use it to avoid duplicates. Two: query for the row, if it isn't there insert it. (You'll have to deal with concurrency with locking or something) Three: Insert everything and remove duplicates later. > Ave, > > Let me explain what I¹m trying to do. Unfortunately it¹s not a very simple > Add/Update transaction. > Basically I gather records from multiple mySQL tables and add them to a > DBF > (dbase) database. I wrote a code which was able to accomplish this > without any problems and the add_records goes very smooth. > > However, I have to modify this program now so that only ³New² records, > i.e., records that have not yet been transferred, are added into the DBF. > In other words, any records that are being added, which already exist in > the DBF, should not be added. > > If it were just adding records from one mySQL to another mySQL, I could > easily use INSERT IGNORE or the Unique Key fundamental however, since > I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go > about doing this. > > Here¹s my code that takes records from multiple mySQL tables and adds them > to a DBF. > > // define the array with mySQL Table Names & Identifier > $tChoice = array( >"lodispo" => "VB", >"lodispo_osma" => "ATL", >"lodispo_osmh" => "HOU", >"lodispo_osmn" => "NSV", >"lodispo_osmp" => "PAA", >"lodispo_osmj" => "JAX", >"lodispo_osmt" => "TPA", >"lodispo_dmam" => "MET", >"lodispo_osmf" => "FTM", >"lodispo_hfglend" => "GLEND", > "lodispo_hfmesa" => "MESA", > "lodispo_hfphenx" => "PHENX", > ); > > > // open DBF in read-write mode > $db2 = dbase_open($theDBFfile, 2); > if (!$db2) { > echo "Fatal Error: Unable to open > database"; > exit; > } > > else { > > // Let's Run Array Loops! > foreach ($tChoice as $tblQ => $bxQ) { > > // connect to mySQL tables one by one > mysql_select_db($database_imslead_transfer, > $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ"; > $loDispo = mysql_query($query_loDispo, $imslead_transfer) or > die(mysql_error()); > $row_loDispo = mysql_fetch_assoc($loDispo); > $totalRows_loDispo = mysql_num_rows($loDispo); > > // write mySql data to Dbf > do { > dbase_add_record($db2, array( > $row_loDispo['phone'], > $row_loDispo['comments'], > $row_loDispo['starttime'], > $row_loDispo['endtime'], > $row_loDispo['dispo'], > $row_loDispo['loanofficer'], > $row_loDispo['verifier'], > $bxQ)); > } while ($row_loDispo = mysql_fetch_assoc($loDispo)); > } > > dbase_close($db2); > } > > Any suggestions would be highly appreciated. > > Thanks, > > ~~~ > Rahul Sitaram Johari > CEO, Twenty Four Seventy Nine Inc. > > W: http://www.rahulsjohari.com > E: [EMAIL PROTECTED] > > ³I morti non sono piu soli ... The dead are no longer lonely² -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Add New Records Only!
you could do a serialized md5 of sum of the more unique columns from your db abd use that as a unique identifier for the row. Then you would need to compare this 'key' before doing a insert. Also keeping a log of the date and time of the last operation would help as then you only need to select rows newer than that dates, thats provided of course your source data has a datetime column. -- Regards, Clive. Real Time Travel Connections {No electrons were harmed in the creation, transmission or reading of this email. However, many were excited and some may well have enjoyed the experience.} -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php