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@xxxxxxxxxxxxxx" <markw@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx> 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 "<BR><BR><STRONG>Fatal Error:</STRONG> Unable to open
>>>>> database<BR><BR>";
>>>>> 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: sleepwalker@xxxxxxxxxxxxxxxx
>>>>>
>>>>> ³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
