This topic is locked

Update all records

11/24/2008 2:05:12 PM
PHPRunner General questions
author

I am trying to create an event page that is not related to adding or editing a subject record but that I can use periodically to up date all records. I have the following code in an event before adding a new record:
$str = "select ID from pedigrees where RegName='".mysql_escape_string($values["Sire"])."'";
$rs = db_query($str,$conn);
if ($data = db_fetch_array($rs))

$values["SireID"] = $data["ID"];
I have about 30,000 records where I have a blank SireID only this time instead of having the Sire name to use to compare to the RegName, I have the sire_num to compare to the RegNumber. And I have a way now of adding 10 or 20 thousand records at a time that will have this information. And instead of just dealing with the recording being added, I want it to find all the records that have the sire_num and update the SireID.
So, I want to create a page to use periodically that only I can access to use code similar to the below that to update each record that has a sire_num to update its SireID?
$str = "select ID from pedigrees where RegNumber ='".mysql_escape_string($values["sire_num"])."'";
$rs = db_query($str,$conn);
if ($data = db_fetch_array($rs))

$values["SireID"] = $data["ID"];
Unfortunately I have over 50,000 records now so I cannot upload my DB to your server.
Thanks!!

J
Jane 11/25/2008

Mary,
You need to use external scheduler like CRON for this purpose.
Write a script that checks the values and update records in the database. Use cron to run this script on timely fashion i.e. once a day.
Here is just a sample:

global $conn;

$strUpdate = "update pedigrees set SireID=ID where isnull(SireID)";

db_exec($strUpdate,$conn);

501335 11/25/2008

Mary,

You need to use external scheduler like CRON for this purpose.
Write a script that checks the values and update records in the database. Use cron to run this script on timely fashion i.e. once a day.
Here is just a sample:


Thanks for pointing me in the right direction. I did not know what a CRON scheduler was. It seems to me it is necessary for me to do a join of the table to itself. Would something like this work [I'm afraid to try it without being somewhat comfortable that I am on the right track]?
$strUpdate =

"INNER JOIN pedigrees AS pedigrees1 ON pedigrees.sire_num = pedigrees1.RegNumber

update pedigrees set pedigrees.SireID=pedigrees1.ID

where pedigrees.sire_num <> ""

db_exec($strUpdate,$conn);
I understand that I can run this manually simply by opening the CRON.php file when I want it to do the update.
Thanks@

J
Jane 11/26/2008

Hi,
I'm not sure that I understand your code.

You don't need to join tables.
Cron is the name of program that enables unix users to execute commands or scripts automatically at a specified time/date. If you want to update database manually just open required page with your script when you want to update database.

501336 11/26/2008

Hi,

I'm not sure that I understand your code.

You don't need to join tables.
Cron is the name of program that enables unix users to execute commands or scripts automatically at a specified time/date. If you want to update database manually just open required page with your script when you want to update database.


Hi Jane: If I don't do a join, and I put
WHERE RegNumber = sire_num
won't it just look for records where the RegNumber and sire_num are the same? Which it will never find since no dog can be it's own sire.
I want it to take the sire_number from one record then find a different record where the RegNumber is the same. Thanks!

J
Jane 11/27/2008

Hi,
I see what you're saying.

You need to select all RegBumber from database, write loop in PHP and update records for each record in loop.