note 81028 modified in function.mysql-connect by danbrown

[EDIT by danbrown AT php DOT net: The issue this user is illustrating is a common problem when dealing with multiple databases from PHP. Note his comments at the end of the code for an explanation.] Looks like I learned this the hard way: <?php //establish connection to master db server mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); mysql_select_db (DB_NAME); //establish connection to read-only slave cluster $objMySQL_Read = mysql_connect (SLAVE_DB_HOST, SLAVE_DB_USER, SLAVE_DB_PASSWORD); mysql_select_db (DB_NAME, $objMySQL_Read); $strSQL = "SELECT col1,col2 FROM " . DB_NAME . "." . "tbl1 WHERE 1=1"; $objRS = mysql_query ($strSQL, $objMySQL_Read); //returns data from slaves $strSQL = "INSERT INTO " . DB_NAME . "." . "tbl1 (col1,col2) VALUES (val1,val2)"; mysql_query ($strSQL); //expected behavior, to insert the last statement into the master db, since it doesn't reference the read-only resource explicitly. instead, it inserts the record into the last connection, even though it shouldn't, since the last connection is not a global/anonymous connection like the first one, it's $objMySQL_Read. //you'll get out of sync db's across your cluster unless you explicitly define all connection resources ?> --was-- Looks like I learned this the hard way: <? //establish connection to master db server mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); mysql_select_db (DB_NAME); //establish connection to read-only slave cluster $objMySQL_Read = mysql_connect (SLAVE_DB_HOST, SLAVE_DB_USER, SLAVE_DB_PASSWORD); mysql_select_db (DB_NAME, $objMySQL_Read); $strSQL = "SELECT col1,col2 FROM " . DB_NAME . "." . "tbl1 WHERE 1=1"; $objRS = mysql_query ($strSQL, $objMySQL_Read); //returns data from slaves $strSQL = "INSERT INTO " . DB_NAME . "." . "tbl1 (col1,col2) VALUES (val1,val2)"; mysql_query ($strSQL); //expected behavior, to insert the last statement into the master db, since it doesn't reference the read-only resource explicitly. instead, it inserts the record into the last connection, even though it shouldn't, since the last connection is not a global/anonymous connection like the first one, it's $objMySQL_Read. //you'll get out of sync db's across your cluster unless you explicitly define all connection resources ?> http://php.net/manual/en/function.mysql-connect.php