[RESOLVED] problem with an updating script

[jacksonpt]

I've got a database with about 5000 records in it. We recently added 3 new fields to the database. New records are populating those new fields as appropriate, but I have to write a script to go back through and populate the fields already in the DB as necessary.

So here's the plain english version of what I'm trying to do...

1. query the db to find out how many of the ~5000 records need to be udpated (based on the value of the DateAttended field), used as a check value

2. retrieve all records from the db that need to be updated (done based on the value of the DateAttended field)

3. for each record returned, update 3 fields: ActivityCode2, ActivityDate2, ActivityComment2

4. autoincrement a variable each time a record is updated, used as a check value

5. query the database to find out how many records actually have updated field values, used as a check value

Here is the code...

    // select * of records to be updated, used as a check
	$data = mysql_query("SELECT count(*) as count FROM temp_backup WHERE DateAttended NOT LIKE 'n/a'",$db); 
    $record = mysql_fetch_array($data);
	$NumRecords = $record['count'];
	echo $NumRecords . " to be updated<br><br>";

// select the actual records to be updated
$data = mysql_query("SELECT RegID, DateAttended, SessionTime, NumPeople FROM temp_backup WHERE DateAttended NOT LIKE 'n/a'",$db); 
if ($record = mysql_fetch_array($data)) {       
	$scanned = 0;
	$ActivityCode2 = "Visit: Campus Tour";
    $ActivityDate2 = date('m/d/y');
	do {    
		$RID = $record['RegID'];
	    $ActivityComment2 = $record['DateAttended'] . ", " . $record['SessionTime'] . " - " . $record['NumPeople'] . " people attended.";
	    $sql = "UPDATE temp_backup SET
						ActivityCode2 = '$ActivityCode2';
						ActivityDate2 = '$ActivityDate2',
						ActivityComment2 = '$ActivityComment2'
	    WHERE RegID = '$RID'";
		$scanned++;  // auto increment each time a record is updated, used as a check
    } // end do
    while ($record = mysql_fetch_array($data));
	echo "<br><br>" . $scanned . " record(s) scanned<br><br>";

	// count records with updated field, used as a final check
	$data = mysql_query("SELECT count(*) as count FROM temp_backup WHERE ActivityCode2 LIKE 'Visit:%'",$db); 
    if ($record = mysql_fetch_array($data)) {
	    echo $record['count'] . " records updated.";
	} else {
	   echo "no records found with updated ActivityCode2 field.";
	}
   } else {
      echo "No records found";
   }

and here is the output...

2366 to be updated

2366 record(s) scanned

0 records updated.

So the first query is showing that 2366 records need to be updated, based on the value of the DateAttended field in each record. The incrementing value of $scanned in the do/while loop is showing that the loop is executed 2366 times, which is right. But for some reason, no records are being updated (I checked the database and that is correct).

I can't for the life of me figure out what's going on. Any help would be appreciated. TIA.

[leatherback]

Although you do create a querystring ($sql) you never perform the query:

result = mysql_query($sql) or echo "failed update";

[jacksonpt]

I changed it to the following

$sql = "UPDATE temp_backup SET
							ActivityCode2 = '$ActivityCode2';
							ActivityDate2 = '$ActivityDate2',
							ActivityComment2 = '$ActivityComment2'
		    WHERE RegID = '$RID'";
			$result = mysql_query($sql);

but there was no change in output...

[jacksonpt]

Damnit... I got it - semicolon instead of a comma after the '$ActivityCode2' update...

sigh