Re: [PHP] Add New Records Only!

2007-03-26 Thread Rahul Sitaram Johari

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!

2007-03-26 Thread Richard Lynch
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!

2007-03-26 Thread Rahul Sitaram Johari

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!

2007-03-26 Thread Richard Lynch
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!

2007-03-26 Thread Rahul Sitaram Johari
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!

2007-03-23 Thread Richard Lynch
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!

2007-03-23 Thread Mark
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!

2007-03-23 Thread Rahul Sitaram Johari
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!

2007-03-23 Thread Brad Fuller
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!

2007-03-23 Thread Rahul Sitaram Johari

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!

2007-03-23 Thread markw
>
> 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!

2007-03-23 Thread Satyam
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!

2007-03-23 Thread Rahul Sitaram Johari

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!

2007-03-23 Thread Mark
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!

2007-03-23 Thread clive
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