Linkpoint CC Review Problem

[VS4Hire]

I'm getting the following error:

1054 Unknown column 'lp.customer_id' in 'where clause'
in:
[select count(*) as total from customers c, linkpoint_api lp where lp.customer_id = c.customers_id ]

I've checked my zen database and don't see any tables relating to LP or Linkpoint, yet payments are being processed perfectly...

Thanks!
Michelle

[DrByte]

Which version of Zen Cart ?
Which versions have you upgraded from?
How long have you had the Linkpoint module installed? ie: which version of Zen Cart?
If you write down all your linkpoint module settings, then remove the module, and re-install it, and fill in all your settings again ... does that make the error go away ?

(There are some table-field fixes in the "install" process, which may resolve the matter you're reporting).

[VS4Hire]

Which version of Zen Cart?
1.3.7

Which versions have you upgraded from?
I believe it was previously 1.3.5

How long have you had the Linkpoint module installed? ie: which version of Zen Cart?
From the beginning (again, I believe 1.3.5)

If you write down all your linkpoint module settings, then remove the module, and re-install it, and fill in all your settings again ... does that make the error go away?
I will try that now and let you know.

Michelle

[VS4Hire]

Just deleted the module and re-installed it - no changes.

I know I'm missing the lp.XXX tables - just not sure why or how to add them...

Is there a manual install of the linkpoint module that I can grab the SQL file from?

Thanks!
Michelle

[DrByte]

Is there a manual install of the linkpoint module that I can grab the SQL file from?

The SQL portion is built-in to the "Install" process of the module.

[VS4Hire]

I guess what I meant was - is there somewhere else (besides my installation files) where I can go to download a new copy of the module?

Reason I ask this is because the cc_review file in question seems to be the only file that has lp.* in it - also can't find it in a SQL file.

Thanks!
Michelle

[DrByte]

The most current code is in the Zen Cart main zip file.

The SQL to create the table is in the module, not in some SQL file. Hence the recommendation to remove and re-install the module.

I DO NOT recommend running this manually.

    if (!$sniffer->table_exists(TABLE_LINKPOINT_API)) {
      $sql = "CREATE TABLE " . TABLE_LINKPOINT_API . " (
                  id int(11) unsigned NOT NULL auto_increment,
                  customer_id varchar(11) NOT NULL default '',
                  lp_trans_num varchar(64) NOT NULL default '',
                  order_id int(11) NOT NULL default '0',
                  avs_response varchar(4) NOT NULL default '',
                  r_error varchar(250) NOT NULL default '',
                  approval_code varchar(254) NOT NULL default '',
                  transaction_result varchar(25) NOT NULL default '',
                  message text NOT NULL,
                  transaction_response_time varchar(25) NOT NULL default '',
                  transaction_time varchar(50) NOT NULL default '',
                  transaction_reference_number varchar(64) NOT NULL default '',
                  fraud_score int(11) NOT NULL default '0',
                  cc_number varchar(20) NOT NULL default '',
                  cc_expire varchar(12) NOT NULL default '',
                  cust_info text NOT NULL,
                  chargetotal decimal(15,4) NOT NULL default '0.0000',
                  date_added datetime NOT NULL default '0001-01-01 00:00:00',
                  PRIMARY KEY  (id),
                  KEY idx_customer_id_zen (customer_id)     )";
      $db->Execute($sql);
    } else {
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'customer_id')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD customer_id varchar(11) NOT NULL default '' after id");
      if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'lp_order_id'))  $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN lp_order_id lp_trans_num varchar(64) NOT NULL default ''");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'r_error'))     $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD r_error varchar(250) NOT NULL default '' after avs_response");
      if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'approval_code')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN approval_code approval_code varchar(254) NOT NULL default ''");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'transaction_result')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD transaction_result varchar(25) NOT NULL default '' after approval_code");
      if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'message'))      $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN message message text NOT NULL");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'transaction_response_time')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD transaction_response_time varchar(25) NOT NULL default '' after message");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'transaction_time')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD transaction_time varchar(50) NOT NULL default '' after transaction_response_time");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'transaction_reference_number')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD transaction_reference_number varchar(64) NOT NULL default '' after transaction_time");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'fraud_score')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD fraud_score int(11) NOT NULL default '0' after transaction_reference_number");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'cc_number'))   $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD cc_number varchar(20) NOT NULL default '' after fraud_score");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'cc_expire'))   $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD cc_expire varchar(12) NOT NULL default '' after cc_number");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'cust_info'))   $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD cust_info text NOT NULL after cc_expire");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'chargetotal')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD chargetotal decimal(15,4) NOT NULL default '0.0000' after cust_info");
      if (!$sniffer->field_exists(TABLE_LINKPOINT_API, 'date_added'))  $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " ADD date_added datetime NOT NULL default '0001-01-01 00:00:00' after chargetotal");
      if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'zen_order_id'))  $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default '0'");
    }

    if (!$sniffer->field_exists(TABLE_ORDERS, 'lp_avs')) {
      $sql = "ALTER TABLE " . TABLE_ORDERS . " ADD lp_avs VARCHAR( 25 ) NOT NULL DEFAULT ''";
      $db->Execute($sql);
    }
    if (!$sniffer->field_exists(TABLE_ORDERS, 'lp_trans_num')) {
      $sql = "ALTER TABLE " . TABLE_ORDERS . " ADD lp_trans_num varchar(64) NOT NULL DEFAULT ''";
      $db->Execute($sql);
    }
    if ($sniffer->field_type(TABLE_ORDERS, 'lp_trans_num', 'varchar(64)', true) !== true) {
      $sql = "ALTER TABLE " . TABLE_ORDERS . " CHANGE lp_trans_num lp_trans_num varchar(64) NOT NULL DEFAULT ''";
      $db->Execute($sql);
    }

[VS4Hire]

Tried that and got:

[FONT=Courier New]#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (!$sniffer->table_exists(TABLE_LINKPOINT_API)) {
$sql = "CREATE TABLE ' at line 1 [/FONT]

[VS4Hire]

I found the problem...

I didn't do the initial installation of Zen Cart 1.3.5 - but I DID perform the upgrade to 1.3.7 - I followed the instructions and replaced all files - or so I had thought.

Just now I did a compare between the linkpoint_api file we were using vs the file in the original zen distribution - voila - LOTS of changes!

Replaced our old file with the new, removed the module, reinstalled, and presto - works like a charm.

Now let's just hope that credit cards are still processed ;)

Thank you very much for helping me with what to look for - I really appreciate it!!!!

Michelle