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 markw@mohawksoft.com; PHP php-general@lists.php.net
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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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
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!
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!
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!
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!
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
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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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!
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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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 markw@mohawksoft.com; PHP php-general@lists.php.net
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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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!
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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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!
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 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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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²
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, 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 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 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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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;
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 markw@mohawksoft.com; PHP php-general@lists.php.net
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 markw@mohawksoft.com 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 BRBRSTRONGFatal Error:/STRONG Unable to open
databaseBRBR;
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
