List:General Discussion« Previous MessageNext Message »
From:Gary Date:April 15 2011 3:59pm
Subject:Re: Out of sync tables
View as plain text  
Michael, thank you for your reply

""Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second.  Then there
is only one sequence to worry about with nothing to sync against
""

There is only one AI into the main page.  This is the insert code, I have 
probably left more in than you need to see.

What I also did was to add some duplicate columns in the two tables (email, 
ip, timestamp) so in the event I need to manually to in I would be able to 
decifer who goes where.

On second look, it would appear I am NOT using a join, but two inserts.... I 
don't recall why I did it that way

******

if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
$ipi_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
  $em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] : 
addslashes($_SERVER['REMOTE_ADDR']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
  $insertSQL = sprintf("INSERT INTO attorney (email, firm_name, ip) VALUES 
(%s, %s, %s)",

                       GetSQLValueString($_POST['email'], "text"),
        GetSQLValueString($_POST['firm_name'], "text"),
        GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));

  mysql_select_db($database_assess, $assess_remote);
  $Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
}
// checks if bot

   if ($_POST['address'] != '' ){


die("Changed field");

    }
$firmname_var_Recordset1 = "-1";
if (isset($_POST['firm_name'])) {
  $firmname_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['firm_name'] 
: addslashes($_POST['firm_name']);
}
$username_var_Recordset1 = "-1";
if (isset($_post['user_name'])) {
  $username_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['user_name'] 
: addslashes($_POST['user_name']);
}
$pw_var_Recordset1 = "-1";
if (isset($_POST['password'])) {
  $pw_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['password'] : 
addslashes($_POST['password']);
}
$em_var_Recordset1 = "-1";
if (isset($_POST['email_2'])) {
  $em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['email_2'] : 
addslashes($_POST['email_2']);
}
$ip_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
  $em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] : 
addslashes($_SERVER['REMOTE_ADDR']);
}
mysql_select_db($database_assess, $assess_remote);
$query_Recordset1 = sprintf("SELECT law_firm.firm_id FROM law_firm WHERE 
law_firm.firm_name = %s AND law_firm.user_name = %s AND law_firm.password=%s 
AND law_firm.ip=%s" , GetSQLValueString($firmname_var_Recordset1, 
"text"),GetSQLValueString($username_var_Recordset1, 
"text"),GetSQLValueString($pw_var_Recordset1, 
"text"),GetSQLValueString($em_var_Recordset1, 
"text"),GetSQLValueString($ip_var_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $assess_remote) or 
die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$row = mysql_fetch_assoc($Recordset1);
$_SESSION['firm_id'] = $row['firm_id'];
echo $row['firm_id'];

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
  $insertSQL = sprintf("INSERT INTO law_firm (firm_name, user_name, 
password, slsmn, email_2, ip) VALUES (%s, %s, %s, %s, %s, %s)" ,
                       GetSQLValueString($_POST['firm_name'], "text"),
                       GetSQLValueString($_POST['user_name'], "text"),
        GetSQLValueString($_POST['password'], "text"),

        GetSQLValueString($_POST['slsmn'], "text"),
        GetSQLValueString($_POST['email_2'], "text"),
        GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));

  mysql_select_db($database_assess, $assess_remote);
  $Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());



"Michael Dykman" <mdykman@stripped> wrote in message 
news:BANLkTi=+fpW=-qbMb6TCfFFHR1gb32Wb8Q@ style="color:#666">stripped...
I presume you are inserting to both tables always at the same time
inside a transaction?  We would need to see the code to see how you
are inserting them..

Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second.  Then there
is only one sequence to worry about with nothing to sync against

 - michael dykman

On Fri, Apr 15, 2011 at 10:33 AM, Gary <gpaul@stripped> wrote:
> I have a innodb with a join on two tables. The foreign key is the id
> column which is set to AI. I have been having an issue of the tables being
> out of sync in that the id is not the same on the two tables. I have
> corrected this a couple of times in phpmyadmin by resetting the
> auto_increment to the same number in both tables, but it is re-occurring.
>
> I have tried to duplicate how this occurs, and I believe it is occurring 
> on
> a page refresh, but I am not positive.
>
> I don't know what information / code to post that someone would be able to
> make a suggestion to a solution, so if someone could direct me to what to
> post I would be appreciative.
>
>
> --
> Gary
>
>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 6044 (20110415) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>



-- 
- michael dykman
- mdykman@stripped

May the Source be with you.

__________ Information from ESET Smart Security, version of virus signature 
database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 6044
(20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com




Thread
Out of sync tablesGary15 Apr
  • Re: Out of sync tablesMichael Dykman15 Apr
  • Re: Out of sync tablesGary15 Apr
    • Re: Out of sync tablesReindl Harald15 Apr
      • Re: Out of sync tablesMichael Dykman15 Apr
  • Re: Out of sync tablesGary15 Apr
    • Re: Out of sync tablesReindl Harald15 Apr
    • Re: Out of sync tablesJohan De Meersman15 Apr
  • Re: Out of sync tablesGary15 Apr
    • Re: Out of sync tablesReindl Harald15 Apr
  • Re: Out of sync tablesGary15 Apr