Records not added to MS Access db - why?

[ywickham]

Hi,

I have a PHP script that pulls records from an MySQL database for users who have become a member the day before and then adds those records to an MS Access database. It runs once a night.

When this script runs, if a marker has been reset on all the records in the MS Access database, the script pulls the records for ALL the members from the MySQL database and adds them as new records to MS Access database.

This seems to have been working fine, but recently, I have two records that were not added the MS Access database, even though they were present in the query that selects the records to copy from the MySQL database to the MS Access database.

I don't believe this to be a problem with the script, but a problem with how MS Access processes queries. I was wondering if anyone had any insight or a reason as to why this happened? I would appreciate any help I can get on this.

Thanks!

[ywickham]

Bump...

Anyone? This is still an outstanding issue for me.

Thanks!

[LordShryku]

Maybe you'd get better results if you showed some code or the queries that have bombed.

[ywickham]

Fair enough....here's the complete script. Mind you, it is processing all 700+ records in the database with the exception of 2 records...

//Prints error message if connection to Access database could not be made
function Error_Handler( $msg, $cnx ) {
    echo "$msg \n";
    odbc_close( $cnx);
    exit();
}

chdir("c:/phpdev/www/html");
include("email_marketing/includes/sysenv.php");

//Connect to nuke database server
$dbcnx = @mysql_connect($db_server, $db_server_user, $db_server_user_password);
if (!$dbcnx) {
	die( '<p>Unable to connect to the ' .
		 'database server at this time.</p>' );
}

//Select the database
if (! @mysql_select_db($db_database) ) {
	die( '<p>Unable to locate the nuke ' .
		 'database at this time.</p>' );
}


//Connect to Access database
$cnx = odbc_connect($mdb_server,$mdb_server_user,$mdb_server_user_password);
if (!$cnx) {
	Error_handler( "Error in odbc_connect" , $cnx );
}

//Send access query to check if newsletters have not been sent
$cur= odbc_exec( $cnx, "SELECT count(SentFlag) FROM Newsletters WHERE SentFlag = 0" );
if (!$cur) {
	Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}


// fetch the succesive result rows
while( odbc_fetch_row( $cur ) ) {
	$count= odbc_result( $cur, 1 ); // get the field "Count"
}


//Add only todays new members to db if newsletter has not been sent out
if ($count != 0){

//Get filter dates in timestamp format
$today_date = mktime (0,0,0,date("m"),date("d"),date("Y")); //Todays date
$yesterday_date = mktime (0,1,0,date("m"),date("d")-1,date("Y")); //Todays date minus 1

//Connect to nuke MySQL database and get all records with a join date of current day
$query = "SELECT userid, email
		  FROM user
		  WHERE joindate <= $today_date
		  AND joindate >= $yesterday_date
		  AND usergroupid = 11;";

$result = mysql_query($query) or die(mysql_error());

//Add all members to db if newsletter has not been sent out
} else {

//Connect to nuke MySQL database and get all member records
$query = "SELECT userid, email
		  FROM user
		  WHERE usergroupid = 11;";
$result = mysql_query($query) or die(mysql_error());
}

//Get number of rows returned from query
$num_rows = mysql_num_rows($result);


if ($num_rows != 0) {

//Update records in Access database
while ($row = mysql_fetch_array($result)) {

	//Setup variables added to db
	$member_id = $row['userid'];
	$member_email = $row['email'];

	//Run query to pull first name of member from nuke db
	$name_query = "SELECT field6
				   FROM userfield
				   WHERE userid = '$member_id';";
	$name_result = mysql_query($name_query) or die(mysql_error());

	//Get name from returned record
	$name_rec = mysql_fetch_array($name_result);
	$member_name = $name_rec['field6'];

	//Add new record to Access database
	$update_query = "INSERT INTO Newsletters (EMAIL, FirstName)
					  VALUES ('$member_email','$member_name');";

	$update_result = odbc_exec($cnx, $update_query);
}
}

odbc_free_result($check_result);
odbc_close($cnx);
?>

Thanks!

[LordShryku]

Which two queries are bombing? Can you find the exact two that don't work?

[ywickham]

After processing this query:

//Connect to nuke MySQL database and get all member records 
$query = "SELECT userid, email
FROM user
WHERE usergroupid = 11;"; 

...this is the query that is omitting adding the two records to the access database:

//Add new record to Access database
$update_query = "INSERT INTO Newsletters (EMAIL, FirstName)
 VALUES ('$member_email','$member_name');";

The records that are being omitted get pulled by the first query. (I've been able to verify that.) Yet, something is happening that the second query doesn't add those two records to the Access database.

[ywickham]

Hi,

I still haven't found an answer to this problem.

Anyone have any ideas?

Thanks!